CELL STATUS

Post Reply
mnasra
Posts: 136
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

CELL STATUS

Post by mnasra »

Hi expert,

I cant believe I am asking this question:
but how do I find the cell status in a rule.
I know how to get it on a view : RIGHT click (on cell) - EDIT status.
it will answer : editable or not.

I know how to do it in a TI process with cellisupdateable.
but, I need it in an active form. I need to GREY the cells that are not updatable.
Thanks
Micheline
Thanks
Micheline
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: CELL STATUS

Post by paulsimon »

Hi Micheline

To the best of my knowledge, there is no way to do that in an Active Form, unless you resort to VBA, which has never been an option for me as all sheets are deployed as Web Sheets in TM1 Web or PAW.

However, if you understand the rules and security on the cube, you can normally predict which cells are not going to be editable. Typically if you are displaying a forecast then the earlier months will have been actualised, ie there is a rule in the cube that copies the Actuals to the Forecast. You will typically create an Information cube that tells you what the current period is. You can then arrange to apply the appropriate formatting to the data cells via the Active Form's Format Range. Eg you have format Id F1 that greys out Period 1 and the rest white, F2 greys out periods 1 & 2 and the rest white. You can then apply the appropriate format Id to each of the data rows according to the current period in the information cube. You can use a formula to override the basic period based format. For example, if you know by looking at the security cube that the user does not have write access to this cost centre you can apply a format that greys out all enterable cells. If you know that one of the elements on the rows is a consolidation which you can get with DTYPE, then again you can apply a format that greys out all cells. If one of the columns is a total consolidation or ratio calculation then you can grey that out in all cases in the format range. If a column shows a variance then you will typically apply a conditional format eg Red or Green background. If applying a Red background I recommend you set the text to White as Black does not show so well against Red.

It would be nice if there was a simple way to do this. I know that the information is available via the Rest API, but at present there is no formula that will do this.

There would potentially be a performance cost to this. Given the nature of Excel it would probably mean having a copy of the columns, alongside those where you retrieved the figures, with a new formula to be developed by IBM to indicate whether the cell was editable and then you could reference this in the cell holding the figure via a conditional format. That would mean retrieving twice as many cells which would slow performance down.

An alternative might be that it automatically greyed out cells that were not editable. However, there are cases where you would not always want that, eg the variance calculation above. A possible approach that IBM could follow might be to put a value into the Format Range to indicate that you wanted the cell to be greyed out if it was not editable. Of course, there would still be some impact on performance, since retrieving the extra information to indicate whether a cell is editable is not free. I have done it in the Rest API, but only with an MDX query. It would probably be more cumbersome with the DBRW cell based approach used in an Active Form, or its PAX equivalent, the Dynamic Report.

There are also complexities are what is considered editable. For example a text comment on the data possibly should still be editable even though it is a against a consolidation.

If the users want to use Spreading, then there is a how series of additional complications about where data can be entered.

Sorry that there is not a simple answer

Regards

Paul Simon
mnasra
Posts: 136
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: CELL STATUS

Post by mnasra »

Oh wow. Thank you very much Paul for taking the time to answer.

I realized yesterday when I saw the number of people who read the message with no answer, it means the solution is not simple.
I did solve my problem by creating an alternate hierarchy in my dimension (used in the rows of the report) called: Rule-calculated.
In the Report, I check if 'rule-calculated' is a parent, and I change the formatting accordingly.

It solves half of the problem, but it is good enough.
I wanted to change the colors per cell but it is too complicated.

Thanks again
Thanks
Micheline
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: CELL STATUS

Post by orlando »

Hi,

I have the following idea.
An example, a cube with three dimensions
Organisation, version (planned, actual) and value type (eg. EUR....).

In the dimension Value Type you create an element "EURTest" and make sure that the same calculation rules apply there as for the element EUR.

In the active form you now have e.g. the organisation in the lines
and the version and value type in the columns.
In column C, for example, the plan is displayed in EUR. In column D you make a DBS formula that tries to write a "1" to "EURTest". If this is successful, a 1 appears in the cell. You can now format column C using conditional formatting - if D<>1 then grey (or whatever). To make the report look nicer, you can then hide the column.

Depending on how your model is constructed, you have to do this for each column or each element of your version.
You can also pack the check columns at the end of the report and then hide them collectively.


Cheers to:
Translated with www.DeepL.com/Translator (free version)

Best regards,

olando
mnasra
Posts: 136
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: CELL STATUS

Post by mnasra »

That makes sense too.
Thanks Olando
Thanks
Micheline
Post Reply