MDX for retrieving cube value in SubsetToSet

[CRP0021]

Hi all,

Just need some assistance in writing an MDX statement to pull in a cube value and pass it in to the function TM1SubsetToSet.

Here are some details:
-Dimension name is Location
-Lookup cube name is User Settings
-Measure trying to retrieve subset name from is Location_Subset_Name

I can get the function TM1 SubsetToSet function to work when I hard-code the location name:

TM1SubsetToSet ( [Location].[Location] , "New_Location" , "public")

And I tried wrapping this insdie the TM1SubsetToSet but I'm not having any luck:
StrToMember( "[Location].[" + CoalesceEmpty( [User Settings].(StrToMember( "[}Clients].[" + UserName + "]"), [User Settings].[Location_Subset_Name]), "<default>" ) + "]"),

My end goal is to have the name of the subset which changes by user be dynamically set in the Location dim with the TM1SubsetToSet function

Any advice would be much appreciated!

[burnstripe]

Try this:

Code: Select all

TM1SubsetToSet (
	[Location].[Location] 
	, "" + [User Settings].(StrToMember( "[}Clients].[" + UserName + "]"), [User Settings].[Location_Subset_Name]) + "" 
	, "public")

[CRP0021]

That did the trick thank you!

[Wim Gielis]

I think that you can remove "" + and + "" unless Burnstripe is sure they are needed ?

[burnstripe]

I think that you can remove "" + and + "" unless Burnstripe is sure they are needed ?

They were needed for passing the subset name within the tm1subsettoset

With the "" it returns as this and works
TM1SubsetToSet ([Location].[Location] , "my subset" , "public")

Without the "" it returns as this and fails
TM1SubsetToSet ([Location].[Location] , my subset , "public")

[Wim Gielis]

I think that you can remove "" + and + "" unless Burnstripe is sure they are needed ?

They were needed for passing the subset name within the tm1subsettoset

With the "" it returns as this and works
TM1SubsetToSet ([Location].[Location] , "my subset" , "public")

Without the "" it returns as this and fails
TM1SubsetToSet ([Location].[Location] , my subset , "public")

I see what is happening. I removed them, but then PAW put them there. So yes they are needed. Solved