TM1 Forum

Hi,

I have created a TI process to drill a seperate table of data but am struggling to make the following work

SELECT*
FROM PRODDTA.F0911
WHERE GLOBJ = '?PL Measures?'

GBLOBJ is the col that displays the p&l account
P&L Measures is the dimension in the cube that contains each P&L account as an element.

Its only the WHERE part of the SQL query that I am having problems with - any help would be great

AWILDE wrote:Hi,

I have created a TI process to drill a seperate table of data but am struggling to make the following work

SELECT*
FROM PRODDTA.F0911
WHERE GLOBJ = '?PL Measures?'

GBLOBJ is the col that displays the p&l account
P&L Measures is the dimension in the cube that contains each P&L account as an element.

Its only the WHERE part of the SQL query that I am having problems with - any help would be great

Welcome to the Forum.

Could I suggest a read through the Request for Assistance Guidelines?

"I am having problems with" is probably not quite specific enough to get you as many answers as you'd like.

Also, the Enhancements forum is only for suggestions for enhancements to TM1; general queries go into the main forum. Not everyone subscribes to the Enhancements forum, so chances are that a lot of members didn't even see the original question. Another Admin has moved the thread accordingly.

SELECT*
FROM PRODDTA.F0911
WHERE GLOBJ = '?PL Measures?'

Bit of a stab in the dark, but you need to pass the _TI Variable_ through to the query - and PL Measure is not a valid TI variable name since it has spaces. Maybe PLMeasure, if that's the valid name?

In any drill-through process a parameter is automatically created for each dimension. When the process executes the cell context that is being drilled from is used to pass through the parameter values (as the element names for each dimension corresponding to the cube cell address). By default the parameters take the names of the dimensions to which they correspond but to be used in code any with spaces in the names would need to be renamed. You don't have to use the parameters in the SQL query but they are available and you do have the opportunity to modify parameter values on the prolog tab prior to the data source being queried. Usually this is necessary with drill through to SQL source as often the element names in TM1 will not match the indexes in the table source (especially with time dimensions.)

In your instance the parameter value for accounts in your "GLOBJ" field would return a single element name (corresponding to the context of the cell the drill was executed from). If you want to return all accounts you would leave this where clause out.

Hi

The parameters in a drill process don't need to be renamed unless they (the 'spaceless' version) are a reserved names/functions.

If a parameter comes through as "PL Measure" it can be used as "PLMeasure" (without a space), no problem. But you might get some errors with say a "Year" or "Month" if you leave them as they are.

Cheers