Need help on this MDX

[binny]

Hello there,

I'm trying to do this - to get the parent of the organization and check the level attribute if it's equal to 'Level 0'. If yes, use Subset1, else Subset 2

TM1SubsetToSet([program], IIF([organization].[parent].[Level] = "Level 0" , "Subset1", "Subset2"))

I think there is some syntax error with the [organization].[parent].[Level]... Both the parent and level are string attributes.

Thank you in advance

[lotsaram]

It seems that both Subset1 and Subset2 already exist.

Here pseudo-code is not your friend. Please provide the actual code! Otherwise no one can tell what you are actually querying as the pseudo code is so far off the mark that I don't even know where to start.

[ascheevel]

TM1SubsetToSet([program], IIF([organization].[parent].[Level] = "Level 0" , "Subset1", "Subset2"))

It seems by this code

Code: Select all

[organization].[parent].[Level]

you're trying to first grab the parent attribute of the element from the organization dimension and then the level of that parent. Do I have that right, parent is attribute of one element and level is attribute of the parent element, not the original organization element? I'm not aware of a way to "dot lookup" into multiple chained attributes like that. Instead, why not create an attribute on the original organization element called "parent_level" that is simply an ELLEV rule on the parent attribute of the organization element. Your MDX could then be thus and I think should work:

Code: Select all

TM1SubsetToSet([program], IIF([organization].CurrentMember.Properties("parent_level") = 0 , "Subset1", "Subset2"))

[declanr]

TM1SubsetToSet([program], IIF([organization].[parent].[Level] = "Level 0" , "Subset1", "Subset2"))

It seems by this code

Code: Select all

[organization].[parent].[Level]

you're trying to first grab the parent attribute of the element from the organization dimension and then the level of that parent. Do I have that right, parent is attribute of one element and level is attribute of the parent element, not the original organization element? I'm not aware of a way to "dot lookup" into multiple chained attributes like that. Instead, why not create an attribute on the original organization element called "parent_level" that is simply an ELLEV rule on the parent attribute of the organization element. Your MDX could then be thus and I think should work:

Code: Select all

TM1SubsetToSet([program], IIF([organization].CurrentMember.Properties("parent_level") = 0 , "Subset1", "Subset2"))

That is also how I understood the question.
In terms of nesting attributes you can do something like below:

Code: Select all

{TM1SubsetToSet ( 
   [Organization],
   IIF ( 
      StrToMember("[Organization].[" + [Organization].CurrentMember.Properties("Parent") + "]" ).Properties("Level") = "0", 
      "Subset1", 
      "Subset2" 
   )
)}

The OP didn't specify where "CurrentMember" is coming from so that may need to be replaced with an element name in square brackets.

[binny]

Hi Ascheevel,

That's a good idea but I'm stubborn about getting it to work in MDX

Hi Declanr,

That's the correct understanding as I think I did mentioned in my original post stating 'trying to get the level attribute of the organization's parent (or something like that). As for the code suggestion, I think it is quite near to achieving what is required but there is still an error in resolving the mdx

Code: Select all

{TM1SubsetToSet ( 
   [program],
   IIF ( 
      StrToMember("[Organization].[" + [Organization].CurrentMember.Properties("Parent") + "]" ).Properties("Level") = "Level 0", 
      "z_Level 0", 
      "z_prog_under_PROG009" 
   )
)}

Tried updating this and it's saying STRTOMEMBER: Error in value expression: ""
The attribute parent is a string attribute and resolving that STRTOMEMBER part would yield StrToMember("[Organization].[OrgParent]"). I'm not sure which part of this is invalid

p.s., lotsaram, the only thing that I replaced in the OP is the subset name, which is quite confusing and not helpful at all if you ask me.

[binny]

I replaced CurrentMember with the element name and it works.
But I need the element to be user selection, therefore CurrentMember should work right @Declanr? What is wrong with the syntax here?

Code: Select all

{TM1SubsetToSet ( 
   [program],
   IIF ( 
      STRTOMEMBER("[Organization].[" + [Organization].[OrgParent].Properties("Parent") + "]").Properties("Level") <> "Level 0", 
      "z_Level 0", 
      "z_prog_under_PROG009" 
   )
)}

[declanr]

Yes, this was why I mentioned the currentmember being an issue.

The "Organization" dimension is returning the MDX list of elements, you can't have a user pick an element from the "Organization" dimension --- because the second that they do... the MDX expression stops applying.
You could reference CurrentMember from a different dimension etc and have the code work.
So I am a little confused as to how you hope to apply this.

One option could be that you create a 2-dimensional cube for the users to make their element selection via picklist and have the result of that cell translated over to the MDX expression.
The 2-dimensional cube would contain the }Clients dimension and a measure dimension, each user makes their selection against their own name.

You can then nest in another StrToMember which would replace the initial element selection/CurrentMember:

Code: Select all

StrToMember ( 
	"[Organization].[" +
	[UserSelectionCube].( StrToMember ( "[}Clients].[" + UserName + "]" ), [UserSelectionMeasures].[Selection] )
	+ "]"
) 

[binny]

Sorry, I might not have explained the whole picture.

The objective is to have a cascading filter from organization dim to program dim in a cube view in PAW.

That's why I cannot understand your statement

can't have a user pick an element from the "Organization" dimension --- because the second that they do... the MDX expression stops applying.

The idea is if the user pick organization A, the MDX will check organization A's parent, say it's organization Z. Then the MDX should check if the level attribute of that parent organization is 'Level 0'. If yes, then use a specific subset for program dimension, else use another subset for program dimension

Hope this clarifies abit

[declanr]

Sorry, I might not have explained the whole picture.

The objective is to have a cascading filter from organization dim to program dim in a cube view in PAW.

That's why I cannot understand your statement

can't have a user pick an element from the "Organization" dimension --- because the second that they do... the MDX expression stops applying.

The idea is if the user pick organization A, the MDX will check organization A's parent, say it's organization Z. Then the MDX should check if the level attribute of that parent organization is 'Level 0'. If yes, then use a specific subset for program dimension, else use another subset for program dimension

Hope this clarifies abit

Yes that makes sense, I hadn't read through your newest code with the 2 separate dimension names being specified.
So in this case the CurrentMember should work, you will need to have "Organization" in the title/context area of a cube view and the Program dimension will need to be in the row or column area. of the same cube view.

[binny]

I have a simple one that works

Code: Select all

TM1SubsetToSet([staff_position] , IIF([organization].[Supreme] = 1 , "Supreme_Org" , "z_none"))

For this case, it needs to check the level attribute of the organization's parent attribute.

Correct me if I am wrong as I am trying to learn more - the usage of <Properties> is optional right? because if you refer to the MDX above, I can just do [Supreme] to check this Supreme attribute. Also the usage of <CurrentMember>, because when [organization].[Supreme] is used, it resolves to the organization selected by user and the Supreme attribute will be returned.

Thanks!

[MarenC]

Hi,

the use of .properties is not really optional as it is the only syntax that will be acceptable in the next gen TM1 server. So learn it and stick with it would be my advice.

BTW, I think Lotsaram was correct, you didn't give anywhere near enough info in your first comment.

Have you tried putting all the advice together!

So something like the following:

Code: Select all

{TM1SubsetToSet ( 
   [program],
   IIF ( 
      StrToMember("[Organization].[" + [Organization].CurrentMember.Properties("OrgParent") + "]" ).Properties("Level") = "Level 0", 
      "Subset 1", 
      "Subset 2" 
   )
)}

If that doesn't work I suspect there is something else you failed to mention!

maren

[binny]

to get the parent of the organization and check the level attribute if it's equal to 'Level 0'. If yes, use Subset1, else Subset 2

Hello,

If I am to elaborate the above from my post, the objective is to allow user to select the organization element in PAW, and to shorten the list of programs that are showing based on the organization that is selected. To do this, we'll need to check the parent attribute of the organization, which is also element in organization dimension, and then check it's level (the selected organization's parent element level). If the level is "Level 0", then use a specific subset, else use another.

The only thing I masked out in the original post code is the subset name. So, please let me know what could I possibly missed out

Anyway, I think you made a good point on "next gen TM1 server'. The error in expression only happens when resolving in TM1 architect / perspective, it works in PAW. In the past I never leverage .Properties and .CurrentMember syntax in architect / perspective, and when I try to resolve the MDX in architect / perspective, it does not prompt error and it will just return no elements. So I suspect since this is my first encounter using strtomember, probably it just won't work in architect / perspective together with .CurrentMember and .Properties


Thank you all for the advices.
Cheers