Passing values in TI for sql query

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Passing values in TI for sql query

Post by Analytics123 »

Hi ,

I have a ti process which connects to a sql server and have the query in ti . I need to pass the current month, Last 2 months in TI as input in where clause.

I can hardcode like where period in ('201805','201804',201803');

The period is defined as numeric datatype in sql .

How can I pass my TI varaible in here instead of actual value .

Should I do any casting in where clause .

Any help is appreciated .
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Passing values in TI for sql query

Post by ascheevel »

Below is a link i found through google that can probably explain how to do it better than i can. It shows an example of putting the variable right in the datasource window as well as defining the SQL in the prolog tab. We use the variable in the window method and I'm going to switch them to using prolog defined sql with a variable and keep the datasource window a static sql because i get datasource errors whenever i need to update the sql in the window and there's a variable defined. I end up having to replace the variable with a static value, make the intended unrelated update, hit preview, and then switch back to variable.


https://everanalytics.wordpress.com/201 ... grator-ti/
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Passing values in TI for sql query

Post by Analytics123 »

Thanks for the reply ,but that doesnt help still gettign sql failed error when used below synatax.

where [Month] in ('Pm1');
where Pm1 = '201805';

My column in sql table is numeric and has values like 201805

SO in my ti if i hardcore as
where month in ('201805','201806') it works . Which I assume is a string when i put in single quote .

What I am doing is generating this 201805 dynamically using current year and cuurentmonth and storing in a string and passing that string .

pm1= '201805';

Now if I use Pm1 in where of the datasource it says sql failed .

where [Month] in ('Pm1');


This gives sql statement failed .


But one thing that works is

where [CALMONTH] in(Cast('?pm1?' as int))

Using the above statement .

Can any one explain how to do this correctly .
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Passing values in TI for sql query

Post by ascheevel »

If you found a way that works, why do you assume you're not doing it correctly? The where statement that failed: WHERE [MONTH] in ('PM1') likely should have been WHERE [MONTH] IN ('?PM1?'). You need the question marks regardless if you're passing string or number, it's the single quotes that you may or may not need. The link I sent noted that, I'm sorry you felt it didn't help your situation.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Passing values in TI for sql query

Post by tomok »

The question mark "?", is only needed to substitute a parameter for code in the query window. If you build your SQL as a string in the Prolog and then set that as the query with DataSourceQuery variable then you can build the SQL as a string. This is much easier, BTW. Something like this:

Code: Select all

SQL = 'SELECT Whatever FROM YourTable WHERE Month In (' | Pm1 | ')';
DatasourceQuery = SQL;
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Post Reply