ODBC Drillthrough query for consolidated elements

[telula_w]

Hello,
I am trying to write the prolog to deal with scenarios where the consolidated element is selected for drill through.
For example, the product hierachy is

Total Product
- AA
-1101
-1401
-AB
- 2301
-2411
-AC
-3813
-3555

Sql query = Select * from dbo.GL where product ='?pproduct?'
I want the results for the output for the query to be like
If pproduct ='AA', select * from from dbo.GL where product In ('1101','1401')
Is there a way to list the products in the 'In' statement programatically?

Thanking you in advance!

[kpk]

Why do not you create a view or table with an extra field "product_group" as a target to drill into?
When the product level is 1 you can use this field when 0 you can use the product.

[telula_w]

I have thought of that idea but we have several hierarchies for different business groups so 1 product could belong to many hierarchies.

[tomok]

The solution that Peter gave you is really the best practice way to accomplish this. Trying to do it via the IN construct in SQL "might" work but 1) you would have to create some sort of looping deal in the Prolog to build your IN section and 2) it would always be subject to breaking if your IN statement got too long (because of too many products inside the parentheses). If you have multiple hierarchies then you'll need a column for each in the view.

[Michel Zijlema]

In a situation where TM1 users update dimension hierarchies (consolidations) in TM1 directly, a flexible solution I regularly use is having TI processes create/update dimension tables in the relational DB, where in these dimension tables each consolidated element is listed against all of its N-level children.
The drill-through selection will select on the consolidated element in the dimension table, where the dimension table is joined on the fact table through the N-level elements field. This will automatically filter out the relevant set from the fact table.
Attention point is that you need to run the relevant dimension update process after changing a dimension structure to keep TM1 and the relational DB in sync.

Michel