TM1 Forum

I am wondering if it is possible to pass a parameter to a TI process, from within a Drill rule assigment. Depending on which dimension hierarchical levels are showing when a user right-clicks and selects "Drill", the resulting drill-down list should be filtered accordingly.

The Drill Process Data Source SQL string is a simple "SELECT * FROM " and I want the TI script to filter the result based on the Dimension level/s parameter/s passed from the Drill Rule. Much gratitude to anyone who will kindly help with this. Ian Digby

Hi Ian,

When you set up the drill TI process the view is displayed in the data tab and all the variables associated with the source view are in the variables tab. You can use these variables to modify the SQL at run time.

http://forums.olapforums.com/viewtopic.php?f=3&t=722
and
http://forums.olapforums.com/viewtopic.php?f=3&t=773

both give some detail on how to get your SQL query modified at runtime.

HTH

Steve,

Just to express my appreciation for this valuable help. In the end I couldn't create a new parameter within a drill process but another post on here describes how to 'hijack' a default parameter. The measures dimension doesn't need to be parameterised but it is , so you can use that one. I used it to represent stocked Items.

My problem was to edit a section of the Source SQL according to what level of "Item" the user was drilling from.
Level 0 Items have no prefix,
Level 1 are prefixed with "NR-Grp",
Level 2 with "NR-Cat".

So here's the code I used in the Prolog tab:

IF (SUBST(Stock_Item, 1, 3)@<>'NR-');
ReliabilityMeasure='Item';
ELSEIF (SUBST(Stock_Item, 1, 6)@='NR-Grp');
ReliabilityMeasure='''NR-''' | '+ImastGrp';
ELSEIF (SUBST(Stock_Item, 1, 6)@='NR-Cat');
ReliabilityMeasure='''NR-''' | '+ImastCat';
ENDIF;

Then I plugged the ReliabilityMeasure into the Data Source SQl as follows:

SELECT * FROM Table1 WHERE ?ReliabilityMeasure?='?Stock_Item?'

It worked a charm except parsing the single quotes within a string can be challenging.

Again, thanks very much for your help

Ian

Thanks for posting your comments and solution Ian, always helps to enrich the forum when people post their solutions as well as their questions!