TM1 Forum

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.

Did you Google the word 'TM1RptRow' and use the first link ?

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.

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.

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.

Can you copy / paste here the TM1RPTROW function that you now use ?

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

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 ?

FYI, the MDX can be simpler.
See my post here: http://www.tm1forum.com/viewtopic.php?f ... 621#p64621

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

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.

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)

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

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.

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

Very sound Mark !