Need help on this MDX
Need help on this MDX
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
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
-
- MVP
- Posts: 3661
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Need help on this MDX
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.
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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Community Contributor
- Posts: 288
- Joined: Fri Feb 15, 2013 5:49 pm
- OLAP Product: TM1
- Version: PA 2.0.9.1
- Excel Version: 365
- Location: Minneapolis, USA
Re: Need help on this MDX
It seems by this code
Code: Select all
[organization].[parent].[Level]
Code: Select all
TM1SubsetToSet([program], IIF([organization].CurrentMember.Properties("parent_level") = 0 , "Subset1", "Subset2"))
-
- MVP
- Posts: 1815
- 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: Need help on this MDX
That is also how I understood the question.ascheevel wrote: ↑Mon Jan 09, 2023 6:35 pm
It seems by this codeyou'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
[organization].[parent].[Level]
Code: Select all
TM1SubsetToSet([program], IIF([organization].CurrentMember.Properties("parent_level") = 0 , "Subset1", "Subset2"))
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"
)
)}
Declan Rodger
Re: Need help on this MDX
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
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.
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"
)
)}
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.
Re: Need help on this MDX
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?
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"
)
)}
-
- MVP
- Posts: 1815
- 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: Need help on this MDX
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:
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] )
+ "]"
)
Declan Rodger
Re: Need help on this MDX
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
Hope this clarifies abit
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
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
-
- MVP
- Posts: 1815
- 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: Need help on this MDX
Yes that makes sense, I hadn't read through your newest code with the 2 separate dimension names being specified.binny wrote: ↑Tue Jan 10, 2023 8:08 am 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 statementThe 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
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.
Declan Rodger
Re: Need help on this MDX
I have a simple one that works
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!
Code: Select all
TM1SubsetToSet([staff_position] , IIF([organization].[Supreme] = 1 , "Supreme_Org" , "z_none"))
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!
-
- Regular Participant
- Posts: 356
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: Need help on this MDX
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:
If that doesn't work I suspect there is something else you failed to mention!
maren
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
Re: Need help on this MDX
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