Dynamic TM1RPTVIEW Parameters

Post Reply
holger_b
Posts: 126
Joined: Tue May 17, 2011 10:04 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Freiburg, Germany

Dynamic TM1RPTVIEW Parameters

Post by holger_b »

Hi all,

I have seven Active Forms on seven cubes all with the same simple dimensionality: }Clients, <Dimension n>, Measure. In all of these Active Forms, TM1RPTROW handles this one dimension which makes the difference between these cubes.

I was thinking to have everything in just one sheet instead of seven, but I can not make it work. I tried to concatenate the proper TM1RPTVIEW syntax, depending on a drop down for the selection of the cube. When I switch the cube selection rebuild the report, no rows show up, only when I use the version with which the report was initially created.

This one works:
=TM1RPTVIEW(_server&":}Product:10", 0, TM1RPTTITLE(_server&":}Clients",$E$20),TM1RPTFMTRNG,TM1RPTFMTIDCOL)

This one does not:
=TM1RPTVIEW(_server&":}Row:15", 0, TM1RPTTITLE(_server&":}Clients",$E$20), TM1RPTFMTRNG,TM1RPTFMTIDCOL)

Any hints? Some hidden magic which I do not see yet?

Thank you, Holger.
User avatar
gtonkin
MVP
Posts: 1039
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.10; PAW 2.0.71
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Dynamic TM1RPTVIEW Parameters

Post by gtonkin »

Have you tried deleting the named ranges for the rows i.e. TM1RPTDATARNGxx then rebuilding?
holger_b
Posts: 126
Joined: Tue May 17, 2011 10:04 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Freiburg, Germany

Re: Dynamic TM1RPTVIEW Parameters

Post by holger_b »

Tried that, thank you, but it did not help:

- On rebuild I got back the named range TM1RPTDATARNG10
- However, TM1RPTDATARNG15 was not created when using the other option
- Then I created TM1RPTDATARNG15 manually and started the rebuild, and Perspectives got stuck (looped)
holger_b
Posts: 126
Joined: Tue May 17, 2011 10:04 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Freiburg, Germany

Re: Dynamic TM1RPTVIEW Parameters

Post by holger_b »

Ah, I tried a few more things and now it works: The trick is, you must not have different number strings after the cube name, that one must be the same all over, then it works because you can apparently only have one TM1RPTDATARNG?? range, not more.

Awesome, you definitely made my day, thanks a lot!
User avatar
gtonkin
MVP
Posts: 1039
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.10; PAW 2.0.71
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Dynamic TM1RPTVIEW Parameters

Post by gtonkin »

Glad you got it sorted.

In theory the format should be server:cube:reportID. I often replace the number with something Like Input, Review, ReviewByXXX etc. to easily look at the named ranges as see what they relate to. Trick is to obviously ensure that you update consistently then delete the range for the data i.e. TM1RPTDATARNG??, then rebuild.
Post Reply