Display element alias using TM1RPTRow and MDX
-
- 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
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.
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
Kaz
-
- MVP
- Posts: 3113
- 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
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
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: 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
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.
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
Kaz
-
- MVP
- Posts: 2832
- 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
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.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.
-
- 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
Hi Tom,tomok wrote: ↑Tue Feb 20, 2018 11:40 amThen 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.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.
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
Kaz
-
- MVP
- Posts: 3113
- 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
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
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: 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
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)}
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
Kaz
-
- MVP
- Posts: 3113
- 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
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 ?
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
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
-
- MVP
- Posts: 3113
- 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
FYI, the MDX can be simpler.
See my post here: http://www.tm1forum.com/viewtopic.php?f ... 621#p64621
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
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
-
- 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
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
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
-
- MVP
- Posts: 3113
- 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
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
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: 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
Hi Wim,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 ?
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
Kaz
-
- 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
Hi Wim,
What am I doing wrong?
cheers, Mark
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. *An MDX returns a number of elements, you can still apply whatever alias you want, to the returned elements.
What am I doing wrong?
cheers, Mark
-
- MVP
- Posts: 3113
- 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
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.
gives you element M01 when the alias is not used.
When the alias is active, it gives you noting, certainly not Jan.
This however:
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.
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")}
When the alias is active, it gives you noting, certainly not Jan.
This however:
Code: Select all
{[Period].[M01]]}
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
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
-
- 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
Hi Wim,
Well all I was trying to suggest was the following,
Kaazimraza said his TM1RPTROW formula was:
He said the mdx was:
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
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)
Code: Select all
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Line of Business] )}, "LOB4000")}, ALL, RECURSIVE )}, 0)}
Some presumptions on my part I grant you!
I was then wondering based on your reply whether my logic above is sound.
Cheers, Mark
-
- MVP
- Posts: 3113
- 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
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
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