Active Forms with Filters

[Paul-TM1]

Hi All,
I am looking for an idea on how to filter in an Active form.
As shown in the picture, I want to allow users to pick the highest level of the project from a drop down and only the leaf level elements of the chosen project must be available.

ActiveForm.PNG (39.62 KiB) Viewed 3980 times

Can someone please help me?

Thanks,
Paul.

[Wim Gielis]

Use an MDX statement to return these children, based on the value of a cell (like a SUBNM).
You can fully customize this. In the MDX argument of the TM1RPTROW function, refer to the (hidden) cell that contains the MDX expression.
That MDX would be a string where a cell reference to the project choice is made and hence, used in the expression.

[tomok]

This MDX statement:

Code: Select all

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[YourDimension].["&A1&"]}, ALL, RECURSIVE )},0)}

placed in your TM1RPTROW formula will filter to all the leaf elements of the parent contained in cell A1. In A1 you could put a SUBNM formula or a data validation Excel drop down formula.

[Wim Gielis]

As there is TM1DRILLDOWNMEMBER, DRILLDOWNLEVEL, ... and also to for the arguments ALL and RECURSIVE,
I tend to use Descendants:

Code: Select all

="TM1FILTERBYLEVEL( DESCENDANTS( {[YourDimension].[" & A1 & "]}), 0)"

[Paul-TM1]

Thanks Wim and Tomok.
I need some more help.

This is the code I see and where should I add?

Code: Select all

=TM1RPTROW($B$9,"ABC:Projects","",'{AR}01'!$B$16:$B$17,"Number And Name",0)

I tried to add it towards the end, but it's erroring out. From the syntax, I placed it in the MDXStatement and in SubsetElements and failed in both attempts.

Code below is what I replaced with.
"E13" is the drop down list that has the Projects.

Code: Select all

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[YourDimension].["&E13&"]}, ALL, RECURSIVE )},0)}

Syntax:

Code: Select all

TM1RptRow(ReportView, Dimension, Subset, SubsetElements, Alias, ExpandAbove,MDXStatement, Indentations, ConsolidationDrilling)

Thanks,
Paul.

[tomok]

For the TM1RPTROW formula you provide either a subset OR an MDX statement parameter. This is an MDX parameter. Use the function editor in Excel to help you get the syntax correct.