TM1 Drill Throughs
Date: 07/02/2014

BY JOHN YOUNG, SENIOR CONSULTANT

TM1 drill throughs - what if you want to conditionally drill through to multiple cubes/views depending on the value of a cell?

Scenario

The requirement is to be able to drill through from a report to see where the value came from; the only trouble is that the value comes from two different cubes.

The report is an audit report of user status: ADMIN, READ or WRITE.

READ and WRITE are determined from the user status in the }CubeSecurity cube and ADMIN is determined from the }ClientsGroup cube.

The Problem

When creating a drill through, the default position is to drill from ONE cell to ONE view, and system generated code is created in the Epilog of the TI to facilitate this, using the RETURNVIEWHANDLE statement.

Being system generated, this code cannot be deleted, so how to amend it?

Solution

I 'conditioned out' the system generated code, copied the RETURNVIEWHANDLE statement below and made it conditional depending on the value.

Code

#Note that the only way to take out the code is to conditionally bypass it.

If (1 = 0);

#****Begin: Generated Statements***

RETURNVIEWHANDLE('sys_CubeSecurity','DrilledView');

#****End: Generated Statements****

EndIf;

sValue = CellGetS(cubename, sys_User, 'SecurityLevel');

If(sValue @= 'WRITE' % sValue @= 'READ' );

RETURNVIEWHANDLE('sys_CubeSecurity','DrilledView');

EndIf;

If(sValue @= 'ADMIN' );

RETURNVIEWHANDLE('sys_ClientGroups','DrilledView');

EndIf;

I hope you found this useful. If you'd like any more information you can click here to send me an email.