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.