Referencing alias in MDX Subset for active form

[tosca1978]

Hi all,

I am using TM1 v10.1 and excel 2007.

I have an active form with the following MDX statement incell B9:

Code: Select all

FILTER({TM1DRILLDOWNMEMBER( {[LP Hierarchy].[BD7041170]}, ALL, RECURSIVE )}, [LP Hierarchy].[Status] = 'ACTIVE')

This is a concatenation of three other cells, one of which is bringing back an element from a list that the user selects.

The TM1RPTROw formula looks like this:

Code: Select all

=TM1RPTROW($B$9,"TM1_LP:LP Hierarchy","LP Hierarchy_Expand_All_Active",$F$12,,,$F$8,2)

The results are as I expect them - it only returns those children of the element selected that have "active" in the status attribute.

However, rather than bring back the element name (which is just a system generated uniquie ID and the end user will not understand), I would like it to bring back the "Name" alias.

However, I cannot seem to reference the "Name" alias in the MDX statement. If I was using suset editor, and inserted the MDX statement in teh expression window and selected alias I would get exactly what I want. But I don't see how I can get the MDX statement in Cell B9 of my active form to do the same.

Any tips would be much welcomed.

Kind regards

[lotsaram]

You don't reference an alias in the MDX you do this in the TM1RptRow formula. It's the 5th argument.
So your formula just needs to be.
=TM1RPTROW($B$9,"TM1_LP:LP Hierarchy","LP Hierarchy_Expand_All_Active",$F$12,"Name",,$F$8,2)
Simple.
This things I put in red you can correct/remove. If you are using an MDX expression for the rowset then the subset and subelements arguments are ignored and the drill argument is Boolean, therefore the value should be either 1 or 0. (I guess "2" would be interpreted as false but this is something I never tried.)

[tosca1978]

Lotsaram,

I can't thank you enough...you're a star!

[tez]

Hi

I hope this is ok that I'm posting in addition someone else's post, but I'm wondering if you can reference an alias for the MDXStatement argument of the TM1RPTROW.

I am using a subset for one result of the TM1RPTROW, and using the MDXStatement for the other result of the TM1RPTROW.

Code: Select all

=TM1RPTROW($B$9,"CXMD:Project",$D$11,"","Project Name_Code",0,$D$12,0)

It does happen to be the same "Project Name_Code" alias I wish to use for both the Subset & MDXStatement arguments, so not sure if that would be of assistance.

Many thanks
Terri

CX 10.2.1 FP3, Office 2007

[tomok]

I am using a subset for one result of the TM1RPTROW, and using the MDXStatement for the other result of the TM1RPTROW.

The TM1RPTROW formulas accepts EITHER a subset parameter OR an MDX statement NOT BOTH.

[tez]

Hi Tomok

Yes, I understand that it can only reference a subset or MDX. Sorry, I should have included that the subset argument cell ($D$11) is configured to be blank if I want to use the MDX argument, & vice versa.

Is it impossible to reference an alias when using the MDX argument?

Many thanks
Terri

[Alan Kirk]

Yes, I understand that it can only reference a subset or MDX. Sorry, I should have included that the subset argument cell ($D$11) is configured to be blank if I want to use the MDX argument, & vice versa.

Is it impossible to reference an alias when using the MDX argument?

As per the help in the reference guide, the arguments for that function are:
TM1RptRow(ReportView, Dimension, Subset, SubsetElements, Alias, ExpandAbove,MDXStatement, Indentations, ConsolidationDrilling
(My emphasis, but basically what Lotsaram said in his answer lo those many years ago.)

If you have that argument populated then the function will use that alias for the MDX subset. The only issue is that it will override the one that is saved as part of the subset (if used instead):

Alias A string that defines the alias used for the subset.
When this argument is supplied, it overrides the default alias property defined by the subset specified by the Subset argument.
If this argument is empty, the alias from the subset specified by the Subset argument are used.

Of course, you could also use a conditional formula to only have the Alias argument return a value if the MDX expression is in use. (Though if you use the same alias for both there would be no point.)