Page 1 of 1
MDX query not working
Posted: Mon Nov 23, 2015 4:38 pm
by wigglyrat
Hi:
I am trying to write an MDX query that returns the lowest level elements of a dimension "zz Customers" with the syntax as follows:
Code: Select all
=TM1RPTROW($B$9,"DecisionSupportUAT:zz BDE","{TM1FILTERBYLEVEL({TM1SUBSETALL([zz Customers])},0)}
Is there a syntax issue here/can anyone please explain why this would not work?
Many thanks in advance.
Re: MDX query not working
Posted: Mon Nov 23, 2015 5:11 pm
by Edward Stuart
TM1RPTROW from the Reference Guide link:
http://www-01.ibm.com/support/knowledge ... _tm1rptrow
What you can see is that you only need to name a Subset and therefore do not need the MDX code in the TM1RPTROW formula
This could then be broken out to a Data Validation list for your Top 10, Top 20 etc.. MDX subsets
Re: MDX query not working
Posted: Mon Nov 23, 2015 5:23 pm
by tomok
wigglyrat wrote:Hi:
I am trying to write an MDX query that returns the lowest level elements of a dimension "zz Customers" with the syntax as follows:
Code: Select all
=TM1RPTROW($B$9,"DecisionSupportUAT:zz BDE","{TM1FILTERBYLEVEL({TM1SUBSETALL([zz Customers])},0)}
Is there a syntax issue here/can anyone please explain why this would not work?
Many thanks in advance.
You're missing an ending quotation mark on the MDX string.
Re: MDX query not working
Posted: Mon Nov 23, 2015 5:49 pm
by gtonkin
tomok wrote:You're missing an ending quotation mark on the MDX string.
-as well as the round bracket to terminate the function. Guessing this was a copy and paste issue as Excel would have given an error when you hit enter.
As Edward pointed out, you have MDX where a Subset should be specified. Use the FX button on the formula bar to see the possible parameters and their order.