Error with MDX formula in Active Form

Post Reply
jamesbennett
Posts: 2
Joined: Wed Aug 08, 2018 10:01 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Error with MDX formula in Active Form

Post by jamesbennett » Wed Aug 08, 2018 10:22 am

Hi there, I'm new to this forum. I'm also new to Active Forms and MDX formulae and have run into difficulties.

I have an Active Form that when created gives me a list of all departments at a college. I want to be able to select each department individually using a drop-down menu tool, and have it show me the leaf-level student names immediately below.

I have this formula:

Code: Select all

=TM1RPTROW($B$78,"student_dimension","all_departments_view",B65,"student_alias",0,B82)
where cell B78 contains a TM1RPTVIEW formula auto-generated by the Active Form, cell B65 refers to the department name (selected via a drop-down menu tool) and cell B82 contains an MDX formula:

Code: Select all

{DRILLDOWNLEVEL({[student dimension].["&$B$65&"]}, ALL, RECURSIVE)}
I managed to get the TM1RPTROW formula working in conjunction with a different MDX expression so it clearly is possible. Is there an obvious syntax error that I'm making with the DRILLDOWNLEVEL formula?

----------
Edit: I should add that when I 'rebuild current sheet' using the above formulae my active form just turns into a blank list

----------
Edit edit: I realised my DRILLDOWNMEMBER formula had an error and should read this:

Code: Select all

{DRILLDOWNLEVEL({[student dimension].["&$B$65&"]})}
Also I discovered that the issue is with the cell reference to ["&$B$65&"]. When I replace it with one of the values that might appear in that cell, the sheet updates correctly. However, when it remains as the cell reference I get a blank sheet. The contents of cell B65 matches exactly with the department name as used in TM1.

Mark RMBC
Regular Participant
Posts: 154
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Error with MDX formula in Active Form

Post by Mark RMBC » Wed Aug 08, 2018 11:28 am

Hi,

I presume the actual formula in cell B82 is not

{DRILLDOWNLEVEL({[student dimension].["&$B$65&"]}, ALL, RECURSIVE)}

But is actually

=“{DRILLDOWNLEVEL({[student dimension].["&$B$65&"]}, ALL, RECURSIVE)}”

cheers, Mark

jamesbennett
Posts: 2
Joined: Wed Aug 08, 2018 10:01 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: Error with MDX formula in Active Form

Post by jamesbennett » Wed Aug 08, 2018 11:33 am

That's amazing, I was missing the = and quotation marks. Sticking those in makes in work like a dream now. Thank you so much!

Mark RMBC
Regular Participant
Posts: 154
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Error with MDX formula in Active Form

Post by Mark RMBC » Wed Aug 08, 2018 11:40 am

Hi,

glad it worked, though when you think about it it isn't that amazing because without the = sign excel would not treat it as a formula and instead treat it as a string value.

cheers, Mark

Post Reply