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
CELL STATUS
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
CELL STATUS
Thanks
Micheline
Micheline
- 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
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
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
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Re: CELL STATUS
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
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
Micheline
- 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
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
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