TM1 Function Like TM1RPTROW to create Dynamic Columns

[pikolikoli]

Hi Everyone ,

I would love to create dynamic Column on a report. Is there any function Like TM1RPTROW but for columns ? And how does it work ?

If there is None , Is there any tricks I can do to make the Columns Dynamic.

Thanks

[gtonkin]

There has been talk of this for a while for upcoming versions but cannot say when. There are obviously many challenges besides inserting columns - think of how formatting would need to be applied, especially if the columns are multi-level, not just leaf.

For now you would probably need to do what the rest of us do and use macro code to mimic dynamic columns or have your report reference another sheet to reference from etc.

[pikolikoli]

There has been talk of this for a while for upcoming versions but cannot say when. There are obviously many challenges besides inserting columns - think of how formatting would need to be applied, especially if the columns are multi-level, not just leaf.

For now you would probably need to do what the rest of us do and use macro code to mimic dynamic columns or have your report reference another sheet to reference from etc.

Thank you So Much gtonkin for your help and explanation .

I guess Ill use the macros and hopefully this feature will be added in the future .

Thanks Again

[Bakkone]

While not a perfect match, and not as dynamic. You could use the INDEX() formula combined with ELIST in excel to create the appearance of dynamic columns. But you would have to prepare your columns. So you would have 20 columns ready but if the subset is short only 10 would show data, the rest would be blank. This of course drains performance and might give you an annoying uneccessary scroll bar.

[declanr]

Or you can go back to the original requirements and ask whether it needs to be a custom report at all or would a dynamic Exploration View in PaFE meet the requirement.

[tomok]

While not a perfect match, and not as dynamic. You could use the INDEX() formula combined with ELIST in excel to create the appearance of dynamic columns. But you would have to prepare your columns. So you would have 20 columns ready but if the subset is short only 10 would show data, the rest would be blank. This of course drains performance and might give you an annoying uneccessary scroll bar.

What I do is create an active form range at the bottom of the report and for that active form I have a zero-suppressed cube query that shows the column dimension from the main report be in the rows for this. In the main report I then assign a sequential index number to each column and then use the OFFSET function to grab the element name from the appropriate row below. I then format the extra active form to be all the background color so it doesn't show up.

Of course, this requires adding plenty of extra columns to the report so it can accommodate the changing number of columns. You can do dynamic formatting to hide the content in a column if it doesn't have a corresponding row from the lookup active form. This works well in Perspectives and TM1Web. I haven't really done much with it in PAFE.

[gtonkin]

...
What I do is create an active form range at the bottom of the report and for that active form I have a zero-suppressed cube query that shows the column dimension from the main report be in the rows for this. In the main report I then assign a sequential index number to each column and then use the OFFSET function to grab the element name from the appropriate row below. I then format the extra active form to be all the background color so it doesn't show up.
...

Be careful with volatile functions like OFFSET in PAfE - they, as the name suggests, recalculate whenever a recalc happens.
INDEX may be better but OFFSET killed my reports completely,

[tomok]

Be careful with volatile functions like OFFSET in PAfE - they, as the name suggests, recalculate whenever a recalc happens.
INDEX may be better but OFFSET killed my reports completely,

Interesting. The OFFSET index# shouldn't change unless the active form is rebuilt so a simple recalc should not make that change. Thanks for the heads up.