Active Forms with Filters

Post Reply
Paul-TM1
Posts: 66
Joined: Tue Jun 13, 2017 3:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Active Forms with Filters

Post by Paul-TM1 » Tue Sep 10, 2019 9:25 pm

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 183 times
Can someone please help me?

Thanks,
Paul.

Wim Gielis
MVP
Posts: 2152
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Active Forms with Filters

Post by Wim Gielis » Tue Sep 10, 2019 9:32 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

tomok
MVP
Posts: 2599
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Active Forms with Filters

Post by tomok » Tue Sep 10, 2019 10:39 pm

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 2152
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Active Forms with Filters

Post by Wim Gielis » Wed Sep 11, 2019 12:04 am

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)"
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Paul-TM1
Posts: 66
Joined: Tue Jun 13, 2017 3:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Active Forms with Filters

Post by Paul-TM1 » Wed Sep 11, 2019 6:07 pm

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
MVP
Posts: 2599
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Active Forms with Filters

Post by tomok » Wed Sep 11, 2019 6:21 pm

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Post Reply