Automatically Expand Active Form Two Levels

[rmjohnston]

I have created an TM1 Active Form in Excel 2007 using TM1 9.5.1 that uses a subset of Industry Groups in the rows position. This expands the Industry Group to list the Customers. The Customers have location codes as the next level. To automatically expand the Active Form so it is listing all Customers for the Industry Group the I put this MDX in the TM1RPTROW formula "{DRILLDOWNLEVEL({[MLOC].[MGMT - AIG DOM - CUSTOMERS]})}".

Does anyone have a suggestion how to automatically expand this Active Form one more level to show the Customers of that Industry Group and each Customers Location Codes?

- I tried to record a macro to do this and nothing is recorded when inside the Active Form area of the workbook.

- I tried using DrillDownlevel and specifying the level, but I loose the roll-up of Location Codes to Customer. Maybe I did that wrong.

- I don't want to use the TM!DrilldownMember because it returns all levels and there is one more level that has too much detail and makes the workbook too large.

To try and sum this up, I'm looking for suggestions to automatically drill two levels on an TM1 Active Form.

I appreciate your input,
Rhonda

This is what I get:
MGMT - AIG DOM - CUSTOMERS


+ CUSTOMER - AEES
+ CUSTOMER - BOBCAT
+ CUSTOMER - CATERPILLAR
+ CUSTOMER - DANA
+ CUSTOMER - DETROIT DIESEL
+ CUSTOMER - DIEBOLD
+ CUSTOMER - DTCI
+ CUSTOMER - FORD
+ CUSTOMER - GDX
+ CUSTOMER - GLEEN
+ CUSTOMER - GM
+ CUSTOMER - GMCCA
+ CUSTOMER - LAM RESEARCH
+ CUSTOMER - NAVISTAR
+ CUSTOMER - NEW BUSINESS
+ CUSTOMER - TAKATA
+ CUSTOMER - TOYOTA-WALKER
- MGMT - AIG Dom - Customers
This is a section of what I want:

MGMT - AIG DOM - CUSTOMERS
+ AED
+ AEM
+ AEP
+ AES
+ AET
- CUSTOMER - AEES
+ BOB
+ CBO
- CUSTOMER - BOBCAT
+ CAJ
+ CAL
+ CUK
- CUSTOMER - CATERPILLAR
+ DAE
+ DAM
+ DAN
+ WDN
- CUSTOMER - DANA

Note I have "expand above" selected and the section of "what I want" is just a sample.

[declanr]

Code: Select all

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [MLOC] )}, "MGMT - AIG Dom - Customers")}, ALL, RECURSIVE )}, 0,1,2)}

I am assuming that "MGMT - AIG Dom - Customers" is the top level in your dimension but if not just adjust the level selection accordingly.

Note that you could have recorded the expression within the subset editor to create the MDX to do this in a similar fashion to that which you can record an excel macro. There is also an MDX Primer available which you can find by searching the forum and that is fairly comprehensive.

[cdredmond]

Declanr is correct.
You can also take this one step further by placing the MDX expression in a dynamic subset and then simply refer to the dynamic subset in the Active Form. The benefit of this approach is that the subset is exposed in the system and available to other views as a "single version of the truth" instead of buried in a single workbook where it is not avaiable without replication/recreation for another workbook, cube view, etc. and creates a challenge when auditing your reporting tools inventory.
Best wishes rmjohnston!
Say "Hi" to the rest of the crew for me!

[rmjohnston]

Thank you both Declanr and Christopher. It works perfectly.

I updating the already dynamic subsets to expand to the levels needed. I don't know why I couldn't get that statement to record the way it needed to be for the levels desired.

Thank you both again.

[lotsaram]

I don't know why I couldn't get that statement to record the way it needed to be for the levels desired.

The MDX recorder is pretty limited, it can give you a basic idea but to get to most expressions that you need in the real world you will need to type some code.

[jpm_de]

In relation to the topic:

I tried the (adjusted) code and it did not work for me. Maybe, because I do have a ragged hierarchy, that is 10 levels deep at most.

Two questions of things I do not understand yet:

1. What is the benefit/intention of
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [MLOC] )}, "MGMT - AIG Dom - Customers")}
instead of a simple
{[MLOC].[MGMT - AIG Dom - Customers]}
within the overall statement?

2. To my understanding, TM1FILTERBYLEVEL returns the bottom levels, not the top levels, because TM1 counts levels this way.
How does this help to get the top x levels of a larger (possibly ragged) hierarchy?

Thank you very much in advance!

[Wim Gielis]

1. What is the benefit/intention of
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [MLOC] )}, "MGMT - AIG Dom - Customers")}
instead of a simple
{[MLOC].[MGMT - AIG Dom - Customers]}
within the overall statement?

No benefit. The simpler, the better.

[tomok]

To my understanding, TM1FILTERBYLEVEL returns the bottom levels, not the top levels, because TM1 counts levels this way.
How does this help to get the top x levels of a larger (possibly ragged) hierarchy?

FILTERBYLEVEL returns whatever level you ask for, not just bottom or top.

[jpm_de]

Exactly, TM1FILTERBYLEVEL returns the TM1 level you ask for.
I hoped and tried FILTERBYLEVEL, but in vain, TM1 does not know this function. At least not documented and I did not find a correct syntax.

Having a ragged hierarchy, on top with a varying depth, it is quite hard to determine, what to ask for in terms of TM1 levels:

Just a simple example:

Element TM1 vs. MDX Level
- Root 5 vs. 0

- A 2 vs. 1
- AA 1 vs. 2
- AAA 0 vs. 3

- B 3 vs. 1
- BB 2 vs. 2
- BBB 1 vs. 3
- BBBB 0 vs. 4

- C 4 vs. 1
- CC 3 vs. 2
- CCC 2 vs. 3
- CCCC 1 vs. 4
- CCCCC 0 vs. 5

Within such an example, I am not even able to filter the first MDX level using the TM1 levels, because A, B and C have different levels.

Note:
ELLEV returns TM1 levels, interestingly, the Active Form formula TM1RPTELLEV returns MDX levels, therefore TM1 is capable of providing MDX standard levels!

[jpm_de]

OK, one big step forward:

Using the supported MDX function DRILLDOWNLEVEL I am getting, what I wanted:

Within my example: DRILLDOWNLEVEL( {[Hierarchy Example].[Root]}) results in Root and A, B, C.

And I can easily nest the function as often as I want to drill to a deeper level, e.g. to MDX Level 4:
DRILLDOWNLEVEL(DRILLDOWNLEVEL(DRILLDOWNLEVEL( DRILLDOWNLEVEL( {[Hierarchy Example].[Root]}))))

Using multiple dynamic subsets for each Level, I was able to built a user-friendly drop-down for my active form to define the level of detail.
And thinking of it, there are a thousand situations, where this is helpful (e.g. action buttons for each or a specific level, user or role specific starting levels, etc.).

Unfortunately, I was not able to use the full official MDX syntax, especially the optional parameters to get rid of nesting the function:
see https://msdn.microsoft.com/en-us/library/ms144937.aspx

DrilldownLevel (MDX Syntax)

DrilldownLevel (Set_Expression [,[Level_Expression] ,[Index]] [,INCLUDE_CALC_MEMBERS])

I tried to define and use some MDX hierarchy names within }HierarchyProperties (and yes, I did the TI MDXHierarchyRefresh for the dimension).

Is there anybody, who has done this before or came up with a simple way of MDX statement without having to nest the function?

[Wim Gielis]

Hello,

Here is an example of the syntax to have all descendants of an element, except level 0:

Code: Select all

{EXCEPT( {DESCENDANTS([Customer].[Total Customer]) }, {TM1FILTERBYLEVEL({DESCENDANTS([Customer].[Total Customer])}, 0)} )}

I don't know how to shorten the syntax of the nested DRILLDOWNLEVEL functions, though. Maybe someone else will.

[gtonkin]

Have you tried something like:

Code: Select all

TM1DRILLDOWNMEMBER(
TM1DRILLDOWNMEMBER(
{[MLOC].[MGMT - AIG DOM - CUSTOMERS]}
,ALL)
,ALL)

Note that the RECURSIVE parameter is not present. You can keep nesting the TM1DRILLDOWNMEMBERs