Drill-through to a cube view

[PavoGa]

There isn't a view name listed in any of the TM1 user interfaces. However, you basically have full control of the Drill-Through "view" that it creates, except with the limitations of the Title dimensions as mention earlier. It wouldn't surprised me If this is unique to PA 2.0.8.

Gotcha. I would not be surprised if it was that way in 10.2. I've always needed custom subsets/views so if I ever knew that, I do not recall it, especially since I've slept since then.

[Wim Gielis]

Fast forward to Q2 2025 and this seems to be fully working. [Still waiting for drills to MDX views though.]

I can get a fully dynamic target view without any other action by the user but there is a but (the same but as 5 years ago, not coincidentally since TM1 server did not change here).

I wrote a generic drill-through process (WG_DRILL_cube_view_drill_setup), called for instance like this:

Code: Select all

cCube = 'Supply Chain';
vObject_Name = 'Drill_Details';

NumericGlobalVariable( 'nErrors' );
StringGlobalVariable( 'vObject_Name' );

# Create the drill object (the order of subsets is retained)
nErrors = nErrors + ExecuteProcess( 'WG_DRILL_cube_view_drill_setup', 'pCube', cCube, 'pObject_Name', vObject_Name, 'pZero_Suppression', 'NN', 'pFilter', '
    ~
    Account¦mdx:d(Total Volumes) & 
    ~
    Year¦' | Attrs( 'Year', Year, 'Previous' ) | ' & 
    Month¦' | Month );

# Launch the drill object
If( nErrors > 0 ); ProcessError; EndIf;
ReturnViewHandle( cCube, vObject_Name );

Or with Expand and a filter on the same line:

Code: Select all

cCube = 'Supply Chain';
vObject_Name = 'Drill_Details';

NumericGlobalVariable( 'nErrors' );
StringGlobalVariable( 'vObject_Name' );

# Create the drill object (the order of subsets is retained)
nErrors = nErrors + ExecuteProcess( 'WG_DRILL_cube_view_drill_setup', 'pCube', cCube, 'pObject_Name', vObject_Name, 'pZero_Suppression', 'NN', 'pFilter', Expand( 'Account¦Total Volumes & ~ Year¦%Year% ~ Month¦%Month%' ));

# Launch the drill object
If( nErrors > 0 ); ProcessError; EndIf;
ReturnViewHandle( cCube, vObject_Name );

It's all Prolog tab code, no code in the Epilog tab.

The ~ character marks a section: first dimensions for the Title section, followed by dimensions for the Rows section, then followed by dimensions for the Columns section. Line breaks are for readability.

Bedrock is underlying some parts of this process so you need to have that library to use this process.

What is noteworthy:
- In principle I would say that selections cannot be set for the titles in the shared dimensions! You get what you drilled upon. If you want to set a different selection in a shared dimension, redirect that dimension to rows or columns. HOWEVER, oftentimes I saw that it worked so I am in doubt on this one. What always works: leave the shared dimensions in rows or columns, even if it is just 1 element, in case you want to change the selection. As a second-best solution and what always works: leave the dimension in the titles, cancel the drill after rendering the view, and open the generated view from the cube. The view is created with changed selections in the shared dimension, but the drill object is stubborn and will not render it.
- It works fine in PAW and PAfE ánd TM1 Web, TM1 Architect.
- MDX expressions are allowed in pFilter, including shorthand notation like '0' for all the leaves ! See process. You need the indicator 'mdx:'. This means that the core Bedrock proces was given more flexibility. I always wanted to have an MDX option inside Bedrock pFilter parameter values.
- The Expand( ) function with % % characters are useful to condense the notation above.
- You can make view and subset names fully unique with: %u% for the username, %t% for a timestamp, %r% for a random number. Put it in vObject_Name.
- Instead of dimension names above you can also use the index order of the dimension in the cube (1 to n for an n-dimensional cube). I am sure someone can find a use case for this.
- the ReturnViewHandle function should be in the drill TI process. Otherwise the drill option is greyed out in the view.
- ReturnViewHandle is allowed to be in the Prolog tab of the process, not only in the Epilog tab.
- the data source of the drill process can be Null/None. No variables at all. As long as the parameters are there and the ReturnViewHandle function, it's okay. (George already noted this.)
- When passing in a string to a string process parameter and it contains a line break, then Ascii table extended characters (>= 128) are ignored ! (https://www.ascii-code.com) Without line breaks they work. This means that the pipe or broken vertical bar (Alt-0166) does not work in case of line breaks. Hence my choice for ~ characters. If you do not use line breaks then the section character § or Alt-0167 is perfect. Example of an issue:
vFilter = 'Year¦2024 & Account
¦
Gross Sales';
Many Bedrock processes and other processes are affected.
- The generic process shows the usage of global variables being passed back and forth. Very powerful.
- Random observation: the NUMBR() function says that it ignores all characters other than '0' through '9', '+', '-', '.', and 'E'. Then why does 0wim not convert to 0 but instead gives an error ?
- The process '}bedrock.server.util.string.validate' is invalid in Github. I proposed a solution, to be approved soon, I expect.

WG_DRILL_cube_view_drill_setup.pro

(25.34 KiB) Downloaded 20 times

Enjoy and please share your feedback.