TM1 Forum

Hopefully someone can help

I have a TI process that gets data from a SQL database

I had parameterised the SQL to be the following

select * from RefFrontlistTitles where globaldivision = '?p0?'
and on the PROLOG I have the following

p0=CellGetS('RFC - Control','Password','Value');

Within the Cube - 'RFC - Control' the intersection of Password and Value is 'ACAD'

When I try to Preview it now doesn't apply the where clause

This worked until I moved TM1 to a new Virtual Server and now none of the params are getting passed to the SQL

I have tested the CellGetS within a variable and it returns the correct value of 'ACAD'

Any help greatly appreciated

Parameters are not populated before the preview is run. Suggest you use datasourcequery with asciioutput (or LogOutput, if using 10.2.2.3+) to find out what the query text passed to SQL actually is.

David Usherwood wrote:Parameters are not populated before the preview is run. Suggest you use datasourcequery with asciioutput (or LogOutput, if using 10.2.2.3+) to find out what the query text passed to SQL actually is.

Unless you use the function DataSourceQuery in the Prolog tab, this isn't going to work.
TM1 will not resolve the p0.
The AsciiOutput function will show p0 and not its value at run-time.

I normally write a select Top 10 query for the preview section to give me the columns/variables I need. In prolog I then build up a string with the SQL query to be executed. This way you can just concatenate query and variables using pipes. This way you can Asciioutput and check your query by pasting into SQL and executing what TI would. Don't forget to cast string parameters to numeric where required.