Display element alias using TM1RPTRow and MDX

Post Reply
kaazimraza
Posts: 95
Joined: Mon Jun 25, 2012 6:58 am
OLAP Product: TM1, SSAS, Power BI
Version: 10.2.2
Excel Version: 2016

Display element alias using TM1RPTRow and MDX

Post by kaazimraza »

Hi guys,

Is it possible to display element alias when using MDX within TM1RptRow in Active Forms? I know that we are able to display aliases when using a subset within TM1RptRow instead of MDX.
Thanks,

Kaz
Wim Gielis
MVP
Posts: 3105
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: Display element alias using TM1RPTRow and MDX

Post by Wim Gielis »

Did you Google the word 'TM1RptRow' and use the first link ?
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
kaazimraza
Posts: 95
Joined: Mon Jun 25, 2012 6:58 am
OLAP Product: TM1, SSAS, Power BI
Version: 10.2.2
Excel Version: 2016

Re: Display element alias using TM1RPTRow and MDX

Post by kaazimraza »

Hi Wim

I did, and indeed tried TM1Rpt and tried specifying the alias in Alias parameter after Subset parameters. Didn't work.

Perhaps it's worth mentioning that my Active Form skills are quite limited.
Thanks,

Kaz
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Display element alias using TM1RPTRow and MDX

Post by tomok »

kaazimraza wrote: Tue Feb 20, 2018 11:11 am tried TM1Rpt and tried specifying the alias in Alias parameter after Subset parameters. Didn't work.
Then you didn't do it right. It's best to use the function editor in Excel (click on the little fx icon next to the formula bar). This keeps you from specifying the parameters in the wrong order.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
kaazimraza
Posts: 95
Joined: Mon Jun 25, 2012 6:58 am
OLAP Product: TM1, SSAS, Power BI
Version: 10.2.2
Excel Version: 2016

Re: Display element alias using TM1RPTRow and MDX

Post by kaazimraza »

tomok wrote: Tue Feb 20, 2018 11:40 am
kaazimraza wrote: Tue Feb 20, 2018 11:11 am tried TM1Rpt and tried specifying the alias in Alias parameter after Subset parameters. Didn't work.
Then you didn't do it right. It's best to use the function editor in Excel (click on the little fx icon next to the formula bar). This keeps you from specifying the parameters in the wrong order.
Hi Tom,

Thanks for the reply. I tried the insert function wizard/window, but no luck. If supply the alias name in Alias parameter, it does not work at all, meaning, no values are visible. If I leave the Alias parameter blank, then at least it's returning the value, raw element names that is.
Thanks,

Kaz
Wim Gielis
MVP
Posts: 3105
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: Display element alias using TM1RPTRow and MDX

Post by Wim Gielis »

Can you copy / paste here the TM1RPTROW function that you now use ?
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
kaazimraza
Posts: 95
Joined: Mon Jun 25, 2012 6:58 am
OLAP Product: TM1, SSAS, Power BI
Version: 10.2.2
Excel Version: 2016

Re: Display element alias using TM1RPTRow and MDX

Post by kaazimraza »

Here are the functions and the MDX that i am using

TM1RPTROW($C$8,"FinancePlan:Line of Business","",,"Code and Description",0,$D$22)

TM1RPTView in cell $c$8: TM1RPTVIEW("FinancePlan:Line of Business:Plan:1", 0, TM1RPTTITLE("FinancePlan:Line of Business:System Cycle",$D$34), TM1RPTTITLE("FinancePlan:Line of Business:Company",$D$35), TM1RPTTITLE("FinancePlan:Line of Business:Identifier",$D$39), TM1RPTTITLE("FinancePlan:Line of Business:Account",$D$38), TM1RPTTITLE("FinancePlan:Line of Business:GL Measures",$D$21),TM1RPTFMTRNG,TM1RPTFMTIDCOL)

MDX in cell $D$22: {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Line of Business] )}, "LOB4000")}, ALL, RECURSIVE )}, 0)}
Thanks,

Kaz
Wim Gielis
MVP
Posts: 3105
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: Display element alias using TM1RPTRow and MDX

Post by Wim Gielis »

At first sight, this should work.

Can you create a view in the cube viewer with the alias turned on and your MDX expression,
then Active form to Excel and see if that works ?
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
MVP
Posts: 3105
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: Display element alias using TM1RPTRow and MDX

Post by Wim Gielis »

FYI, the MDX can be simpler.
See my post here: http://www.tm1forum.com/viewtopic.php?f ... 621#p64621
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
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Display element alias using TM1RPTRow and MDX

Post by Mark RMBC »

Hi,

I notice the alias in the TM1RPTROW is code and description but your mdx seems to be just code without description

Could this be the issue?

cheers, Mark
Wim Gielis
MVP
Posts: 3105
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: Display element alias using TM1RPTRow and MDX

Post by Wim Gielis »

Mark RMBC wrote: Thu Feb 22, 2018 11:29 am Hi,

I notice the alias in the TM1RPTROW is code and description but your mdx seems to be just code without description

Could this be the issue?

cheers, Mark
An MDX returns a number of elements, you can still apply whatever alias you want, to the returned elements.
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
kaazimraza
Posts: 95
Joined: Mon Jun 25, 2012 6:58 am
OLAP Product: TM1, SSAS, Power BI
Version: 10.2.2
Excel Version: 2016

Re: Display element alias using TM1RPTRow and MDX

Post by kaazimraza »

Wim Gielis wrote: Thu Feb 22, 2018 7:49 am At first sight, this should work.

Can you create a view in the cube viewer with the alias turned on and your MDX expression,
then Active form to Excel and see if that works ?
Hi Wim,

That does work. Following is the MDX for RptRow, where {AR} is a hidden worksheet containing static element names from Line of Business instead of MDX.

TM1RPTROW($B$9,"FinancePlan:Line of Business","",'{AR}01'!$B$23:$B$26,"Code and Description",0)
Thanks,

Kaz
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Display element alias using TM1RPTRow and MDX

Post by Mark RMBC »

Hi Wim,
An MDX returns a number of elements, you can still apply whatever alias you want, to the returned elements.
So is it just me where filterbypattern must match the string? If I need to include descriptions I usually might have to put in a wildcard, i.e. *

What am I doing wrong?

cheers, Mark
Wim Gielis
MVP
Posts: 3105
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: Display element alias using TM1RPTRow and MDX

Post by Wim Gielis »

Hi Mark

Hope I understand your question.
I should qualified my previous reply better.
There is indeed a relation between an MDX and an alias.

For instance:

A Period dimension with elements M01 to M12.
Alias Jan, Feb, ..., Dec.

Code: Select all

{TM1FilterByPattern( TM1FilterByLevel( TM1SubsetAll( [Period] ), 0), "M01")}
gives you element M01 when the alias is not used.
When the alias is active, it gives you noting, certainly not Jan.

This however:

Code: Select all

{[Period].[M01]]}
always works, it gives you either M01 or Jan, depending on the alias being selected or not.

This also happens when using functions like TM1Sort. It can be useful (like sorting alias values alphabetically) but it can also be annoying, as we can also get sorted months as:

Apr
Aug
Dec
Feb
Jan
Jul
Jun
Mar
May
Nov
Oct
Sep

So let's be careful :-)

In general, for descriptions, you need to include * if you turn on the alias - assuming alias values are equal to the element names appended with some text at the end.
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
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Display element alias using TM1RPTRow and MDX

Post by Mark RMBC »

Hi Wim,

Well all I was trying to suggest was the following,

Kaazimraza said his TM1RPTROW formula was:

Code: Select all

TM1RPTROW($C$8,"FinancePlan:Line of Business","",,"Code and Description",0,$D$22)
He said the mdx was:

Code: Select all

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Line of Business] )}, "LOB4000")}, ALL, RECURSIVE )}, 0)}
All I did was wonder if the reason it wasn’t returning anything is because the mdx referred to just the code (LOB4000) whereas the Alias within the TM1RPTROW asked for Code and Description.

Some presumptions on my part I grant you!

I was then wondering based on your reply whether my logic above is sound.

Cheers, Mark
Wim Gielis
MVP
Posts: 3105
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: Display element alias using TM1RPTRow and MDX

Post by Wim Gielis »

Very sound Mark !
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
Post Reply