MDX - StrToMember

[ccierpik]

I'm trying to create an MDX subset which sorts the members of dimension based on their values for a particular month the value of which is contained in a separate cube. The following is the MDX expression I've developed, which is accepted as a valid expression however the result is not sorted based on Inventory Dollars (EOM), i.e. it doesn't appear to be returning the value, which in this case is MAY-2010, stored in rpt_dynamicsubsets.

{Order(
{TM1FilterByLevel( { TM1DrillDownMember( {[Corporate Suppliers].[Corporate Suppliers]}, All, Recursive )}, 0 )}
,[Corporate Suppliers].
([corporate suppliers_m].[ty amount], [Company].[California], [Product Categories].[All Categories, Excl Miscellaneous],
[Corporate Suppliers_a].[Inventory Dollars (EOM)],
StrToMember("[Period].[" + [rpt_Dynamicsubsets].([rpt_DynamicSubsets].[dyn_CorpSuppliers_inv],[rpt_DynamicSubsets_m].[Period]) +"]")
),
BDESC)}

The following does return the correct result so I don't think it's an issue with the StrToMember function.

{Order(
{TM1FilterByLevel( { TM1DrillDownMember( {[Corporate Suppliers].[Corporate Suppliers]}, All, Recursive )}, 0 )}
,[Corporate Suppliers].
([corporate suppliers_m].[ty amount], [Company].[California], [Product Categories].[All Categories, Excl Miscellaneous],
[Corporate Suppliers_a].[Inventory Dollars (EOM)],
StrToMember("[Period].[MAY-2010]")
),
BDESC)}

Any help would be much appreciated.

TM1 9.5 64 bit

[Mike Cowie]

Hi:

First thought: Are there other dimensions in your rpt_Dynamicsubsets cube that you aren't specifying in your cube reference? If there are, could it be that TM1 is just picking an element at random, which may not happen to contain "May-2010"?

Second thought:
Just to cut this down a bit, can you try the following in a separate expression on your Period dimension:

Code: Select all

{ StrToMember("[Period].[" + [rpt_Dynamicsubsets].([rpt_DynamicSubsets].[dyn_CorpSuppliers_inv],[rpt_DynamicSubsets_m].[Period]) +"]") }

If I'm understanding what you've got, this should return a single element in that dynamic subset: May-2010

I'm not in a spot to test this out right now (and don't remember off the top of my head), but it's very possible that cube references are only understood by TM1 to return Numeric values. Can you try setting up a quick element attribute (any dimension) and trying something similar, looking up the value of the string attribute of a specific element instead of the value from your cube? For example:

Code: Select all

{Order(
{TM1FilterByLevel( { TM1DrillDownMember( {[Corporate Suppliers].[Corporate Suppliers]}, All, Recursive )}, 0 )} 
,[Corporate Suppliers].
([corporate suppliers_m].[ty amount], [Company].[California], [Product Categories].[All Categories, Excl Miscellaneous],
[Corporate Suppliers_a].[Inventory Dollars (EOM)],
StrToMember("[Period].[" + [YourDimensionName].[YourElementName].Properties("YourStringAttributeName") +"]")
),
BDESC)}

Hope that helps.

Regards,
Mike

[ccierpik]

Thanks for the replay Mike. Using a dimension attribute works. I believe you are correct that when you reference a cube it expects to return a value instead of a string (and I don't see a way to differentiate what the intended result is). I took the sample in the MDX Primer guide and build a cube where I entered a string and then used a rule to determine the dimension index. When I referenced the dimension index in the MDX statement it worked.

Thanks for your help. I'm usually just winging it when it comes to these MDX statements so sometimes I don't know whether I'm doing it wrong or what I'm trying to do isn't possible.

Curtis