Page 1 of 1
TM1RPTRow
Posted: Wed Feb 26, 2014 11:26 am
by Abinaya
Hi,
I am using the following function in my Active Form. Is there is a way to get only leaf level n elements of the Subset displayed in the Active Form?. The subset I am using has a consolidated element.
TM1RptRow(ReportView, Dimension, Subset, SubsetElements,
Alias, ExpandAbove,MDXStatement, Indentations, ConsolidationDrilling)
regards,
Abi
Re: TM1RPTRow
Posted: Wed Feb 26, 2014 11:45 am
by declanr
Why can't you use a different subset without the consolidated elements OR some mdx code that doesn't include the consolidations?
Re: TM1RPTRow
Posted: Wed Feb 26, 2014 11:58 am
by lotsaram
Just use the MDX argument rather than the Subset argument in the TM1RptRow formula. You could even use the subset as the base of the MDX expression to filter by level for 0 level elements (this will work fine so long as the subset doesn't share a name with an actual element in the dimension).
Re: TM1RPTRow
Posted: Wed Feb 26, 2014 12:53 pm
by rmackenzie
Abinaya wrote:Is there is a way to get only leaf level n elements of the Subset displayed in the Active Form?. The subset I am using has a consolidated element.
If your dimension is called e.g. Business_Unit and the consolidation is e.g. All Business Units then the TM1RPTROW formula should read:
Code: Select all
=TM1RptRow(ReportView, Dimension, "", "", "", 0, "{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Business_Unit].[All Business Units]}, ALL, RECURSIVE )}, 0)}")
Assuming that ReportView and Dimension are named ranges - if not, replace them with the correct cell references.
Re: TM1RPTRow
Posted: Wed Feb 26, 2014 11:16 pm
by Abinaya
rmackenzie wrote:Abinaya wrote:Is there is a way to get only leaf level n elements of the Subset displayed in the Active Form?. The subset I am using has a consolidated element.
If your dimension is called e.g. Business_Unit and the consolidation is e.g. All Business Units then the TM1RPTROW formula should read:
Code: Select all
=TM1RptRow(ReportView, Dimension, "", "", "", 0, "{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Business_Unit].[All Business Units]}, ALL, RECURSIVE )}, 0)}")
Assuming that ReportView and Dimension are named ranges - if not, replace them with the correct cell references.
Thanks. This really worked well.