MDX to include aliases in result

[andy8888]

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!

[gtonkin]

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")

[andy8888]

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

[gtonkin]

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.

[andy8888]

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!!

[burnstripe]

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

[burnstripe]

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.

[Wim Gielis]

Burnstripe, thank you for providing working syntax. It isn’t the easiest subject.

[andy8888]

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?

[burnstripe]

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.

[burnstripe]

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

[andy8888]

Thanks, unfortunately I think that is based on an old version of the API.