Hiding Excel Sheets in TM1 Application 10.2.2.0

[Chiara Masiero]

Hello guys,

our financial planning application consists of Excel sheets published in TM1 Applications (version 10.2.2.0)

There are two different kinds of scenario: FCST II and FCST III. The former considers 8 months (from May XXXX to December XXXX), while the latter takes into account 18 months, from August XXXX to December XXXX+1. The application shows an Excel form that has months of currrent scenario in columns. Thus, we developed one form for FCST II and another for FCST III.
There is just one scenario available at a time. Based on current scenario type (we already have a look up cube for this information), we would like to show only the corresponding form in TM1 Applications.
Maybe we can modify the rule associated to the cube that defines forms security assignment, but we do not know whether this is feasible and how we can make it.

Any ideas would be greatly appreciated.

Thank you.

Chiara

[jim wood]

You create a subset for each. Name them the same as the scenario. If you combine this with SubNm it will only show the member contained in the subset. It's not ideal as the formulas will still be there in the sheet but in web they will appear blank. You could also use and active form and MDX. I hope this helps,

Jim.

[tomok]

You create a subset for each. Name them the same as the scenario. If you combine this with SubNm it will only show the member contained in the subset. It's not ideal as the formulas will still be there in the sheet but in web they will appear blank. You could also use and active form and MDX. I hope this helps,

Jim.

So you're going to either hide or show a particular form in an Application folder by creating a subset? How is that going to work?

[Chiara Masiero]

Hi Jim,

if I got it, you suggest to put all months in the sheet and then use SUBNM to filter them, right? But how can we use SUBNM for columns?

Now we have two different Excel files, one for FCST II and one for FCST III, respectively. Maybe we could also put them in different sheets in the same Excel workbook. In that case, is it possible to show just one of the sheets in the form in TM1 Applications, according to current scenario type?

Thank you very much for your support,

Chiara

[tomok]

In that case, is it possible to show just one of the sheets in the form in TM1 Applications, according to current scenario type?

You can't selectively hide tabs in an Excel worksheet to the best of my knowledge. You can selectively hide rows and columns via conditional formatting by making the text and background the same color.

[jim wood]

Hi Jim,

if I got it, you suggest to put all months in the sheet and then use SUBNM to filter them, right? But how can we use SUBNM for columns?

Now we have two different Excel files, one for FCST II and one for FCST III, respectively. Maybe we could also put them in different sheets in the same Excel workbook. In that case, is it possible to show just one of the sheets in the form in TM1 Applications, according to current scenario type?

Thank you very much for your support,

Chiara

If you have a subset called FCST II and one called FCAST III When they select the version the subset would change. So say they select the version in cell C1 you would have SubNm formulas in the columns like SubNm(Dim,$c$1,1) for the first column adding each time. This way the number of column header would flex depending on the size of the subset. You just need to make sure that the number of SubNm is either the same or exceeds the maximum number of each subset for each version.

Then you need to ensure that you complete a check for each DBRW formula to check that the column header isn't empty.

I have only got this working in standard slices not active forms. I don't know if anybody else as done so?

Jim.

[jim wood]

You create a subset for each. Name them the same as the scenario. If you combine this with SubNm it will only show the member contained in the subset. It's not ideal as the formulas will still be there in the sheet but in web they will appear blank. You could also use and active form and MDX. I hope this helps,

Jim.

So you're going to either hide or show a particular form in an Application folder by creating a subset? How is that going to work?

I was trying to offer an alternative. I apologise if this didn't fit well with you. I also apologise if I wasn't clear that I was offering something different. Thank you for pointing it out in such a kind a well mannered way. My day took a brighter turn for reading your most excellent and helpful post. It's always a joy to read them.

[tomok]

[Thank you for pointing it out in such a kind a well mannered way. My day took a brighter turn for reading your most excellent and helpful post. It's always a joy to read them.

My post was neither kind nor unkind, it was just a simple question. I was curious to learn how to filter an Application list via a subset. That's all.