Dynamic subset - Reference cube with different Dimensions

Post Reply
chewza
Regular Participant
Posts: 156
Joined: Tue Aug 17, 2010 11:51 am
OLAP Product: TM1
Version: 9.5
Excel Version: 7

Dynamic subset - Reference cube with different Dimensions

Post by chewza »

Hi there

I am am trying to create a subset that needs to reference a cube to get a value - in my case, the Current Week. I have seen examples of referencing other cubes in dynamic subsets, but in this case it does not seem to work. The subset I am trying to create is in the "Weeks" dimension, and I have reduced my Mdx to test the "problem" part as follows:
{[Parameters].[Current Week].[String]}

I get the following error message:
"String": member not found

Any idea why? I am confident that it is not related to incorrect dimensions/elements when referencing the Parameters cube. I even tested with in DBRW formula ( DBRW("modelname:Parameters", "Current Week", "String") which resolves fine in Excel.

Any ideas?

Many thx!
declanr
MVP
Posts: 1822
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Dynamic subset - Reference cube with different Dimensions

Post by declanr »

So first thing when you want to retrieve the value in a cell you need to get it in format:
[cubename].([dimname1].[elementname1],[dimname2].[elementname2]…)

The concatenation of dim name and element name above is a member unique name (you can also add an extra square bracket in the middle with hierarchy name.)

Now that would return the string in the cell, let’s say that is “202340” or something like it.
Again to return an element we need a member unique name so you would need to convert 202340 to something like [week].[202340]

Which you can do with the simple + for concatenate E.g

“[week].[“ + [parameters].([dim1].[current week],[dim2].[string]) + “]”

But that is only returning string in the correct format for a member unique name, the MDX engine still doesn’t know what you want to do with it so you need to wrap StrToMember around it to tell it that you want it to return the member E.g.

{StrToMember ( “[week].[“ + [parameters].([dim1].[current week],[dim2].[string]) + “]” )}
Declan Rodger
chewza
Regular Participant
Posts: 156
Joined: Tue Aug 17, 2010 11:51 am
OLAP Product: TM1
Version: 9.5
Excel Version: 7

Re: Dynamic subset - Reference cube with different Dimensions

Post by chewza »

fantastic - many thx Declan!
Post Reply