TM1 Function Like TM1RPTROW to create Dynamic Columns

Post Reply
pikolikoli
Posts: 19
Joined: Sun May 17, 2020 2:37 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

TM1 Function Like TM1RPTROW to create Dynamic Columns

Post by pikolikoli » Wed Jan 13, 2021 11:22 am

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

User avatar
gtonkin
MVP
Posts: 866
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 Function Like TM1RPTROW to create Dynamic Columns

Post by gtonkin » Wed Jan 13, 2021 11:52 am

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
Posts: 19
Joined: Sun May 17, 2020 2:37 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

Re: TM1 Function Like TM1RPTROW to create Dynamic Columns

Post by pikolikoli » Wed Jan 13, 2021 12:04 pm

gtonkin wrote:
Wed Jan 13, 2021 11:52 am
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
Posts: 117
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Function Like TM1RPTROW to create Dynamic Columns

Post by Bakkone » Wed Jan 13, 2021 1:25 pm

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
MVP
Posts: 1684
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TM1 Function Like TM1RPTROW to create Dynamic Columns

Post by declanr » Wed Jan 13, 2021 2:10 pm

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.
Declan Rodger

tomok
MVP
Posts: 2783
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 Function Like TM1RPTROW to create Dynamic Columns

Post by tomok » Wed Jan 13, 2021 2:24 pm

Bakkone wrote:
Wed Jan 13, 2021 1:25 pm
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
gtonkin
MVP
Posts: 866
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 Function Like TM1RPTROW to create Dynamic Columns

Post by gtonkin » Wed Jan 13, 2021 3:16 pm

tomok wrote:
Wed Jan 13, 2021 2:24 pm
...
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
MVP
Posts: 2783
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 Function Like TM1RPTROW to create Dynamic Columns

Post by tomok » Wed Jan 13, 2021 4:06 pm

gtonkin wrote:
Wed Jan 13, 2021 3:16 pm
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Post Reply