MDX Fun

[John Hobson]

I am trying to create a parameterised MDX query using the methodology suggested by BI Hints excellent MDX tutorial

http://www.bihints.com/using_parameters_in_queries

If I select the products using a parameter based on the parameter cube (which holds a text selection and a calculated index called "Dimix Product" for use in the TM1 Member function) as follows:

{TM1FILTERBYLEVEL( {DESCENDANTS(TM1Member(TM1SUBSETALL( [Product MS] ).Item([Exception].(StrToMember("[}Clients].["+USERNAME+"]"), [Exception Parameters].[Dimix Product])-1),0))}, 0)}

I get a list of all those zero level elements within the selected parameter Dept 400

This is good! (I am not sure why the -1 is necessary but if I don't use it I get the children of the next Department along the dimension)

If I use

{ TOPCOUNT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product MS] )}, 0)}, 10.000000, [Flow Line].([Time].[0910 W01],[Version].[Actual TY],[Flow Data].[Sales Units])) }

I get a list of the top 10 products from All Products for sales for 0910 W01 in the Actual TY dimension.

So far so good – we can see that the Top 10 works in principle.

If I try to restrict the product selection above by inserting the code that works to select based on a parameter like so

{TOPCOUNT( {TM1FILTERBYLEVEL({DESCENDANTS(TM1Member(
TM1SUBSETALL( [Product MS] ).Item([Exception].(StrToMember("[}Clients].["+USERNAME+"]"), [Exception Parameters].[Dimix Product])-1),0))}, 0)}, 10.000000, [Flow Line].([Time].[0910 W01],[Version].[Actual TY],[Flow Data].[Sales Units]))}

I get an index out of range error

If I remove the -1 at the end of the Dimix Product

{TOPCOUNT( {TM1FILTERBYLEVEL({DESCENDANTS(TM1Member(
TM1SUBSETALL( [Product MS] ).Item([Exception].(StrToMember("[}Clients].["+USERNAME+"]"), [Exception Parameters].[Dimix Product])),0))}, 0)}, 10.000000, [Flow Line].([Time].[0910 W01],[Version].[Actual TY],[Flow Data].[Sales Units]))}

I get that a list of the top 10 products from All Products for sales for 0910 W01 in the Actual TY dimension. (i.e. It is NOT filtered by the Department Selection)

This is, as they say, doing my head in

Does anyone have any ideas as to what I am doing wrong?

[John Hobson]

Nobody have any idea ?

[lotsaram]

TM1's support for MDX isn't complete, it works brilliantly for returning a view array but sometimes complex MDX to return filtered subsets based on cube values can return "quirky" results.

What's the interface that you need to fetch the resultant subset in? If you are using active forms then you can just parameterise from cells retrieving your string parameter values. Also if a static subset rebuilt on demand at the click of an action button will suffice then you could take a similar approach to strip out the additional complexity of retrieving a string value and encasing in StrToMember by parameterising and using SubsetCreatebyMDX in TI.

[John Hobson]

Thansk for that - I will take a look at those ideas.