MDX TM1RPTROW : Stop Drill Down in Only one Element

[pikolikoli]

Hello EveryOne ,

I am facing an issue in report creation . I have a dimension which has many consolidations :

-AccountParent-C0001 ->AccountParent-P0001 -> AccountChild-M0001

-AccountParent-C0002-> AccountParent-P0002 ->AccountChild-M0002

Then we have another consolidation containing The Total of all these Accounts Displayed like this :

-Total
+AccountParent-C0001
+AccountParent-C0002

I would like in my report to make the Accounts drillable but the Total Not drillable , Like this :

+AccountParent-C0001
+AccountParent-C0002
-Total ( Not drillable )

In this report I am using TM1RPTROW, Like this :

Code: Select all

TM1RPTROW($A$1;ServerName&":Account";"";;"Alias";0;$A$2;;1)
with $A$2 = {TOPCOUNT(DESCENDANTS([Account].[Total]),10,Dim2,Dim3,Dim4))}+{[Account].[Total]}

I tried changing the last index of TM1RPTROW to stop drilling down but it is applied on all elements not only Total.

Then I tried Only Selecting 'Total' which is at level 4 , Like this:

Code: Select all

$A$2 = {TOPCOUNT(DESCENDANTS([Account].[Total]),10,Dim2,Dim3,Dim4))}+{Filter( TM1FilterByLevel( [Account],4),[Account].[Total])}"

But Again the solution is Applied to all the elements and I get this :

-AccountParent-C0001( Not drillable )
-AccountParent-C0002( Not drillable )
-Total ( Not drillable )

IS there any way to Make All the AccountParents Drill down and Only Total Not drill down ?

Thank you in advance

[tomok]

If you are talking about an active form report then you cannot make a consolidated element "not drillable". If the element is displayed as part of the TM1RPTRPW formula inside the AF and it is a parent node then it's going to have a plus sign next to it and it's going to be drillable. These are situations where you have to think outside the box. What if you made this line outside of the active form area, just below the last line of the range and put your own DBRW formula on it?

[pikolikoli]

If you are talking about an active form report then you cannot make a consolidated element "not drillable". If the element is displayed as part of the TM1RPTRPW formula inside the AF and it is a parent node then it's going to have a plus sign next to it and it's going to be drillable. These are situations where you have to think outside the box. What if you made this line outside of the active form area, just below the last line of the range and put your own DBRW formula on it?

Hello @tomoK , Thank you for your replay. I didnt think about this solution. I tried it and it worked for me. Thanks Again