Page 1 of 1

Active Forms with Filters

Posted: Tue Sep 10, 2019 9:25 pm
by 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
ActiveForm.PNG (39.62 KiB) Viewed 186 times
Can someone please help me?

Thanks,
Paul.

Re: Active Forms with Filters

Posted: Tue Sep 10, 2019 9:32 pm
by 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.

Re: Active Forms with Filters

Posted: Tue Sep 10, 2019 10:39 pm
by 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.

Re: Active Forms with Filters

Posted: Wed Sep 11, 2019 12:04 am
by 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)"

Re: Active Forms with Filters

Posted: Wed Sep 11, 2019 6:07 pm
by 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.

Re: Active Forms with Filters

Posted: Wed Sep 11, 2019 6:21 pm
by 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.