MDX to include aliases in result
-
- Posts: 6
- Joined: Tue Apr 27, 2021 4:09 pm
- OLAP Product: TM1 Planning Analytics
- Version: 2.0.9.4
- Excel Version: 2102
MDX to include aliases in result
Hi all,
I'm attempting to formulate some MDX in such a way as to return aliases rather than member captions.
I've found two issues around this subject on the tm1py github repo, but not much else!
https://github.com/cubewise-code/TM1py- ... /issues/28
https://github.com/cubewise-code/tm1py- ... /issues/31
Unfortunately, I just can't seem to figure it out from the information provided in those issues.
For more context... I'm using the PAX API (v 2.0.57 on 64 bit Excel) to call an ExecuteMDX command to create a cellset.
By default, the member names that are returned are the captions. Instead, I would like for a specific attribute/alias to be returned.
From the looks of the information presented in those github issues, it looks as though I need to look at using "WITH MEMBER" to try to achieve this, but I just can't figure out the syntax. Unfortunately I don't have access to the sample planning model, so can't just do as Marius suggests and try running his sample code there and then adapting.
Marius uses this in his example:
WITH MEMBER [plan_time].[AccountClass] AS
[}ElementAttributes_plan_chart_of_accounts].([}ElementAttributes_plan_chart_of_accounts].[AccountClass])
When I try to adapt this for my dimension, I just receive an error saying that the MDX is invalid.
My dimension is called ABC-Product. It has no hierarchies, other than the default (ABC-Product). I would like it's alias "Reporting" to be returned instead of "Caption".
My current MDX is as follows:
SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
ON 0,
[ABC-Period].[ABC-Period].[PER_1]
ON 1
FROM [ABC-MyCube]
WHERE
(
[ABC-Channel].[CHAN_1],
[ABC-Type].[TYPE_1]
)
I think I need to change it to something like the following:
WITH MEMBER [ABC-Product].[Caption] AS
[}ElementAttributes_ABC-Product].([}ElementAttributes_ABC-Product].[Reporting])
SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
ON 0,
[ABC-Period].[ABC-Period].[PER_1]
ON 1
FROM [ABC-MyCube]
WHERE
(
[ABC-Channel].[CHAN_1],
[ABC-Type].[TYPE_1]
)
Any advice would be very much appreciated. Thank you in advance!
I'm attempting to formulate some MDX in such a way as to return aliases rather than member captions.
I've found two issues around this subject on the tm1py github repo, but not much else!
https://github.com/cubewise-code/TM1py- ... /issues/28
https://github.com/cubewise-code/tm1py- ... /issues/31
Unfortunately, I just can't seem to figure it out from the information provided in those issues.
For more context... I'm using the PAX API (v 2.0.57 on 64 bit Excel) to call an ExecuteMDX command to create a cellset.
By default, the member names that are returned are the captions. Instead, I would like for a specific attribute/alias to be returned.
From the looks of the information presented in those github issues, it looks as though I need to look at using "WITH MEMBER" to try to achieve this, but I just can't figure out the syntax. Unfortunately I don't have access to the sample planning model, so can't just do as Marius suggests and try running his sample code there and then adapting.
Marius uses this in his example:
WITH MEMBER [plan_time].[AccountClass] AS
[}ElementAttributes_plan_chart_of_accounts].([}ElementAttributes_plan_chart_of_accounts].[AccountClass])
When I try to adapt this for my dimension, I just receive an error saying that the MDX is invalid.
My dimension is called ABC-Product. It has no hierarchies, other than the default (ABC-Product). I would like it's alias "Reporting" to be returned instead of "Caption".
My current MDX is as follows:
SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
ON 0,
[ABC-Period].[ABC-Period].[PER_1]
ON 1
FROM [ABC-MyCube]
WHERE
(
[ABC-Channel].[CHAN_1],
[ABC-Type].[TYPE_1]
)
I think I need to change it to something like the following:
WITH MEMBER [ABC-Product].[Caption] AS
[}ElementAttributes_ABC-Product].([}ElementAttributes_ABC-Product].[Reporting])
SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
ON 0,
[ABC-Period].[ABC-Period].[PER_1]
ON 1
FROM [ABC-MyCube]
WHERE
(
[ABC-Channel].[CHAN_1],
[ABC-Type].[TYPE_1]
)
Any advice would be very much appreciated. Thank you in advance!
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX to include aliases in result
Possibly off on a tangent but have you tried using the member properties in the query - something like the syntax below:
[Customer].CurrentMember.Properties("Country")
[Customer].CurrentMember.Properties("Country")
-
- Posts: 6
- Joined: Tue Apr 27, 2021 4:09 pm
- OLAP Product: TM1 Planning Analytics
- Version: 2.0.9.4
- Excel Version: 2102
Re: MDX to include aliases in result
Hi, thanks for the idea. I haven't tried that. To be honest, I'm not sure how / where that would fit into my MDX. Please could you explain how that would fit together?
Thanks
Thanks
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX to include aliases in result
Hi Andy, cannot explain how it would fit in, was just a wild idea of referencing the alias using the properties. Have not done enough cube view selections to be of more help.
The only other thing that comes to mind is with PAW 2.0.63 there is an MDX button for the exploration - maybe if you can create the view, clicking the button will give you something useful. Another stab in the dark.
The only other thing that comes to mind is with PAW 2.0.63 there is an MDX button for the exploration - maybe if you can create the view, clicking the button will give you something useful. Another stab in the dark.
-
- Posts: 6
- Joined: Tue Apr 27, 2021 4:09 pm
- OLAP Product: TM1 Planning Analytics
- Version: 2.0.9.4
- Excel Version: 2102
Re: MDX to include aliases in result
Thanks, yes that's the method I'm using to get the original MDX. It does show some additional lines that refer to the selected alias, but when run via execute MDX it seems to ignore these instructions.
I'm sure there must be a way some how!!
I'm sure there must be a way some how!!
-
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: MDX to include aliases in result
https://docs.microsoft.com/en-us/analys ... llversions
Have a read of this, it should help.
Your with member statement is creating a calculated member/set to use in your select query. So the calculated member / set should be specified within your select statement
So instead of this
WITH MEMBER [ABC-Product].[Caption] AS
[}ElementAttributes_ABC-Product].([}ElementAttributes_ABC-Product].[Reporting])
SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
The selection of prod_1 and prod_2 needs to go after the AS in the With member statement. And the new calculated member/set you're creating (which you've called [ABC-Product].[Caption] needs to go after the select
Select { [ABC-Product].[Caption] }
....
That's my take on it. I'll try out the syntax tomorrow when I have a pc in front of me
Have a read of this, it should help.
Your with member statement is creating a calculated member/set to use in your select query. So the calculated member / set should be specified within your select statement
So instead of this
WITH MEMBER [ABC-Product].[Caption] AS
[}ElementAttributes_ABC-Product].([}ElementAttributes_ABC-Product].[Reporting])
SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
The selection of prod_1 and prod_2 needs to go after the AS in the With member statement. And the new calculated member/set you're creating (which you've called [ABC-Product].[Caption] needs to go after the select
Select { [ABC-Product].[Caption] }
....
That's my take on it. I'll try out the syntax tomorrow when I have a pc in front of me
-
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: MDX to include aliases in result
Taken a different approach but this should give you what you desire
SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
DIMENSION PROPERTIES
[ABC-Product].[Reporting]
ON 0,
[ABC-Period].[ABC-Period].[PER_1]
ON 1
FROM [ABC-MyCube]
WHERE
(
[ABC-Channel].[CHAN_1],
[ABC-Type].[TYPE_1]
)
If you have the GO_NEW_STORES sample database then you can break down this working syntax for your own use
SELECT
NON EMPTY
{ Tm1FilterByLevel ( {Tm1SubsetAll([Month])} , 0 )} *
{ [Exchange Rate].[Exchange Rate] }
DIMENSION PROPERTIES
[Month].[Mois_FR], [Month].[startdate], [Month].[enddate]
ON ROWS,
{ [Local Currency].[CAD], [Local Currency].[EUR] } ON COLUMNS
FROM [Exchange Rates]
It displays:
Column 1: Month Alias called Mois_Fr
Column 2: Month Attribute startdate
Column 3: Month Attribute enddate
Column 4: Displays measure value [Local Currency].[CAD]
Column 5: Displays measure value [Local Currency].[EUR]
Rows show All leaf months
The dimension properties allows use to specify what aliases/attributes you wish to display from the set you've selected.
SELECT
{
[ABC-Product].[ABC-Product].[PROD_1],
[ABC-Product].[ABC-Product].[PROD_2]
}
DIMENSION PROPERTIES
[ABC-Product].[Reporting]
ON 0,
[ABC-Period].[ABC-Period].[PER_1]
ON 1
FROM [ABC-MyCube]
WHERE
(
[ABC-Channel].[CHAN_1],
[ABC-Type].[TYPE_1]
)
If you have the GO_NEW_STORES sample database then you can break down this working syntax for your own use
SELECT
NON EMPTY
{ Tm1FilterByLevel ( {Tm1SubsetAll([Month])} , 0 )} *
{ [Exchange Rate].[Exchange Rate] }
DIMENSION PROPERTIES
[Month].[Mois_FR], [Month].[startdate], [Month].[enddate]
ON ROWS,
{ [Local Currency].[CAD], [Local Currency].[EUR] } ON COLUMNS
FROM [Exchange Rates]
It displays:
Column 1: Month Alias called Mois_Fr
Column 2: Month Attribute startdate
Column 3: Month Attribute enddate
Column 4: Displays measure value [Local Currency].[CAD]
Column 5: Displays measure value [Local Currency].[EUR]
Rows show All leaf months
The dimension properties allows use to specify what aliases/attributes you wish to display from the set you've selected.
-
- MVP
- Posts: 3117
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: MDX to include aliases in result
Burnstripe, thank you for providing working syntax. It isn’t the easiest subject.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 6
- Joined: Tue Apr 27, 2021 4:09 pm
- OLAP Product: TM1 Planning Analytics
- Version: 2.0.9.4
- Excel Version: 2102
Re: MDX to include aliases in result
Thank you all for your contributions. I can see how that will work for creating views, but unfortunately I can't get it to return the aliases when using the API. The cellset axes always seem to just contain the member caption. Any ideas?
-
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: MDX to include aliases in result
Sorry I've not had much experience with api (still relatively new to it myself), but the mdx is what's required, it might be you need to specify which alias when viewing the array.
There's some useful documentation which can be found here.
https://ibm.github.io/paxapi/#refreshbook
It would help us if you could show what coding you have to return.
It looks like the execute mdx returns a result that can be queried in the same way as a json. In the link there's a section "Common View Specification Schema" with mention of alias, perhaps you can specify what property/alias to return.
There's some useful documentation which can be found here.
https://ibm.github.io/paxapi/#refreshbook
It would help us if you could show what coding you have to return.
It looks like the execute mdx returns a result that can be queried in the same way as a json. In the link there's a section "Common View Specification Schema" with mention of alias, perhaps you can specify what property/alias to return.
-
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: MDX to include aliases in result
Stumbled across an old forum on the subject
Have a read
viewtopic.php?t=234
Adjust the Mdx as I suggested earlier and then try something along the lines of this to return the alias instead
oCellset.Axis(1).Positions(intPosition).Members(0).Properties(0).Value
Have a read
viewtopic.php?t=234
Adjust the Mdx as I suggested earlier and then try something along the lines of this to return the alias instead
oCellset.Axis(1).Positions(intPosition).Members(0).Properties(0).Value
-
- Posts: 6
- Joined: Tue Apr 27, 2021 4:09 pm
- OLAP Product: TM1 Planning Analytics
- Version: 2.0.9.4
- Excel Version: 2102
Re: MDX to include aliases in result
Thanks, unfortunately I think that is based on an old version of the API.