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 .
Passing values in TI for sql query
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
-
- Community Contributor
- Posts: 287
- 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
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/
https://everanalytics.wordpress.com/201 ... grator-ti/
-
- 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
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 .
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 .
-
- Community Contributor
- Posts: 287
- 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
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.
-
- 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
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;