TM1RPTROW() Parameters Question

[ExApplix]

I have a question realted to the Actove Form/TM1RPTROW() function.

In Active Forms, I have noticed that at certain times TM1RPTROW() function looks like following:

Code: Select all

TM1RPTROW($B$9, "server:dimnesion", "default")

while sometimes it has the following:

Code: Select all

TM1RPTROW($B$9, "server:dimnesion", "", '{AR}01'!$B$14:$B$50, "AliasName",0)

I think {AR}01 is some sort of vritual Sheet which TM1 generates. I tried to look in the Manual/Forum but could not find anyhting which explain the logic of {AR}01 and also tells us that why TM1RPTROW() is generated differently.

Any guidence would be appreciated. Thanks

[qml]

{AR}01 is not a 'virtual sheet', it's a normal sheet, albeit hidden. In it TM1 creates a static list of elements when you export cube views to Excel as Active Forms. You can unhide it and check for yourself that there is really nothing special about it.
When creating/editing your own Active Forms you are better off using the Subset or MDXStatement parameters/capabilities of the TM1RPTROW() function.

[lotsaram]

I think {AR}01 is some sort of vritual Sheet which TM1 generates. I tried to look in the Manual/Forum but could not find anyhting which explain the logic of {AR}01 and also tells us that why TM1RPTROW() is generated differently.

Any guidence would be appreciated. Thanks

The end user guide chapter on active forms is a good place to start... It's pretty simple really, if the active form is using a named subset (either public or private) then the subset is just referred to by name using the 3rd argument. If however the active form is sliced out of the cube viewer with an "unregistered subset" on rows then TM1 saves the staic definition of the rowset in a hidden sheet called {AR}xx and uses the 4th argument. (Warning the moment you expand or contract any consolidations in a subset you are no longer looking at the named subset but a new "unregistered subset".)

It is best practice to used named public subsets or MDX to generate active form rowsets since the hidden sheet adds overhead (especially if it is a long list) and also adds a static element to the report since the list in the hidden sheet represents the elements when the view was sliced out and it doesn't update like a subset might or MDX would.

You can edit the list simply by unhiding the hidden sheet and changing element names, inserting/deleting rows, etc. Or you can use the active form ribbon section to edit members via the subset editor (both achieve th same result.)

If the TM1RpTRow formula is changed to use a named subset or MDX then you can simply delete the hiddne sheet with no ill effect.

[ExApplix]

Thanks guys...it helped, but I have another question at this stage.

How do I use/generate the MDX within the TM1RPTROW function. Initially I want to create MDX which will include ALL the members of the dimension. I thought of using the SubsetCreateByMDX function, but that function is only for the TI.

Please guide me in this matter.

[tomok]

How do I use/generate the MDX within the TM1RPTROW function.

You can't. You have to create the MDX code yourself and paste it into the formula or into a cell the formula references. You can use the subset editor to help you create the MDX if you want, that's what I usually do.

[lotsaram]

[ou can use the subset editor to help you create the MDX if you want, that's what I usually do.

Bear in mind with Tomok's advice that {TM1SubsetBasis()} has no context outside of the subset editor (or more precisly without the list saved in the .sub file) so would need to be substituted for a proper set reference.