Drill Through to ORACLE ODBC
-
- Posts: 72
- Joined: Wed Aug 15, 2018 3:18 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Drill Through to ORACLE ODBC
Hi, All
Part 1: Work as Expected
I have to create drill through to Oracle Database. I placed SQL query in query box and and set up drill rules.
It works as expected.
Select * from xxchc.xxgl_acc_analysis_det_tbl where Period_Name ='FY-NOV-20';
Part 2:
Our Time dimension has element called "NOV FY2020" and Oracle DB has filed Name 'FY-NOV-20'
so i wrote following code in Prolog
sTime = SUBST(pTime,5,2)|'-'|SUBST(pTime,1,3)|'-'|SUBST(pTime,9,2);
vSQL = 'Select * from xxchc.xxgl_acc_analysis_det_tbl where Period_Name =''?sTime?''';
DataSourceType = 'ODBC';
DataSourceNameForServer ='Oracle_Development';
DataSourceNameForClient ='Oracle_Development';
DataSourceQuery = vSQL;
I saved the process and tried to drill through and i got drill through window with null data.
According to me, ''?sTime?'' is the culprit. I used two single quote.
Could you please let me know what i missed?
Thank You
Dharav
Part 1: Work as Expected
I have to create drill through to Oracle Database. I placed SQL query in query box and and set up drill rules.
It works as expected.
Select * from xxchc.xxgl_acc_analysis_det_tbl where Period_Name ='FY-NOV-20';
Part 2:
Our Time dimension has element called "NOV FY2020" and Oracle DB has filed Name 'FY-NOV-20'
so i wrote following code in Prolog
sTime = SUBST(pTime,5,2)|'-'|SUBST(pTime,1,3)|'-'|SUBST(pTime,9,2);
vSQL = 'Select * from xxchc.xxgl_acc_analysis_det_tbl where Period_Name =''?sTime?''';
DataSourceType = 'ODBC';
DataSourceNameForServer ='Oracle_Development';
DataSourceNameForClient ='Oracle_Development';
DataSourceQuery = vSQL;
I saved the process and tried to drill through and i got drill through window with null data.
According to me, ''?sTime?'' is the culprit. I used two single quote.
Could you please let me know what i missed?
Thank You
Dharav
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Drill Through to ORACLE ODBC
Try:
or:
Code: Select all
vSQL = Expand( 'Select * from xxchc.xxgl_acc_analysis_det_tbl where Period_Name = ''%sTime%''' );
Code: Select all
vSQL = 'Select * from xxchc.xxgl_acc_analysis_det_tbl where Period_Name = ''' | sTime | '''';
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 72
- Joined: Wed Aug 15, 2018 3:18 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: Drill Through to ORACLE ODBC
Wim,
I tried these two statements one by one but both sent me the empty records window.
I tried to edit the SQL in query window of TI Process:
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = 'FY-NOV-20'
LEDGER <>'Consolidation Ledger' and
Department = '?vDept?' and
Company = '?vCo?' and
Account ='?vAcct?' and
Division ='?vDiv?';
and it worked.
In TM1, I have time dimension element called "NOV FY2020". So I re-write the code to remove hard coded value 'FY-NOV-20'
pTime is Parameter defined in drill through
pTime = NOV FY2020
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = concat(Concat(Concat(concat(substr('?pTime?',5,2),'-'),substr('?pTime?',1,3)),'-'),substr('?pTime?',9,2)) and
LEDGER <>'CHC Consolidation Ledger' and
Department = '?vDept?' and
Company = '?vCo?' and
Account ='?vAcct?' and
Division ='?vDiv?';
Where Data Type of PERIOD_NAME is VARCHAR2(15)
It sends me empty windows as well.
1) How would i resolve Period_name ?
2) How can i implement same code through Prolog?
Thank You
Dharav
I tried these two statements one by one but both sent me the empty records window.
I tried to edit the SQL in query window of TI Process:
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = 'FY-NOV-20'
LEDGER <>'Consolidation Ledger' and
Department = '?vDept?' and
Company = '?vCo?' and
Account ='?vAcct?' and
Division ='?vDiv?';
and it worked.
In TM1, I have time dimension element called "NOV FY2020". So I re-write the code to remove hard coded value 'FY-NOV-20'
pTime is Parameter defined in drill through
pTime = NOV FY2020
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = concat(Concat(Concat(concat(substr('?pTime?',5,2),'-'),substr('?pTime?',1,3)),'-'),substr('?pTime?',9,2)) and
LEDGER <>'CHC Consolidation Ledger' and
Department = '?vDept?' and
Company = '?vCo?' and
Account ='?vAcct?' and
Division ='?vDiv?';
Where Data Type of PERIOD_NAME is VARCHAR2(15)
It sends me empty windows as well.
1) How would i resolve Period_name ?
2) How can i implement same code through Prolog?
Thank You
Dharav
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Drill Through to ORACLE ODBC
Please post the real TI code. What you post cannot be real TI code since it would contain syntax errors.
Also, 'CHC Consolidation Ledger' is not the same as 'Consolidation Ledger'
Use the AsciiOutput function to check your code.
Also, 'CHC Consolidation Ledger' is not the same as 'Consolidation Ledger'
Use the AsciiOutput function to check your code.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 72
- Joined: Wed Aug 15, 2018 3:18 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: Drill Through to ORACLE ODBC
Wim
Following is the code in my Query Window:
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = concat(Concat(Concat(concat(substr('''?pTime?''',5,2),'-'),substr('''?pTime?''',1,3)),'-'),substr('''?pTime?''',9,2)) and
LEDGER <>'CHC Consolidation Ledger' and
Department = '?vDept?' and
Company = '?vCo?' and
Account ='?vAcct?' and
Division ='?vDiv?';
It does not work.
If i replace Period_Name= 'FY-NOV-20' then it works.
#----------------------
My Time dimension has value NOV FY2020 while Databse has value in format of 'FY-NOV-20'.
So i tried to concatenate here
Period_Name = concat(Concat(Concat(concat(substr('''?pTime?''',5,2),'-'),substr('''?pTime?''',1,3)),'-'),substr('''?pTime?''',9,2))
#------------------
Please feel free if you require further information.
Thank You
Dharav
Following is the code in my Query Window:
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = concat(Concat(Concat(concat(substr('''?pTime?''',5,2),'-'),substr('''?pTime?''',1,3)),'-'),substr('''?pTime?''',9,2)) and
LEDGER <>'CHC Consolidation Ledger' and
Department = '?vDept?' and
Company = '?vCo?' and
Account ='?vAcct?' and
Division ='?vDiv?';
It does not work.
If i replace Period_Name= 'FY-NOV-20' then it works.
#----------------------
My Time dimension has value NOV FY2020 while Databse has value in format of 'FY-NOV-20'.
So i tried to concatenate here
Period_Name = concat(Concat(Concat(concat(substr('''?pTime?''',5,2),'-'),substr('''?pTime?''',1,3)),'-'),substr('''?pTime?''',9,2))
#------------------
Please feel free if you require further information.
Thank You
Dharav
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Drill Through to ORACLE ODBC
Try to use the Prolog tab and concatenate the query string there.
What AsciiOutput statements did you try and what was the outcome for each of the statements ?
What AsciiOutput statements did you try and what was the outcome for each of the statements ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 72
- Joined: Wed Aug 15, 2018 3:18 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: Drill Through to ORACLE ODBC
Hi, Wim
Query BOX:
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = ?sTime? => I removed Single quote purposefully to see the error
and LEDGER <>'CHC Consolidation Ledger'
and Department = '?vDept?'
and Company = '?vCo?'
and Account ='?vAcct?'
and Division ='?vDiv?'
Prolog:
sTime = SUBST(pTime,5,2)|'-'|SUBST(pTime,1,3)|'-'|SUBST(pTime,9,2);
When i do ASCIIOUTPUT it gives Value FY-NOV-20.
I saved the TI and it showed the messge SQL failed. I still save and click on cell for drill through and i receive following error
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = '?sTime?' => I put back Single quote
and LEDGER <>'CHC Consolidation Ledger'
and Department = '?vDept?'
and Company = '?vCo?'
and Account ='?vAcct?'
and Division ='?vDiv?'
Now TI saved successfully. When i click on cell to drill through it brings empty window. [ FYI : If i hard code value to 'FY-NOV-20' then i do see the drill down value in the window]
Period_Name = 'FY=NOV-20' works well while Period_Name= '?sTime?' (ASCIIOUTPUT VALUE 'FY-NOV-20') does not work.
Following Articles i went through to DEBUG:
https://www.tm1forum.com/viewtopic.php?t=722
https://www.tm1forum.com/viewtopic.php?t=6036
Please feel free if you require further information.
Thank You
Dharav
Query BOX:
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = ?sTime? => I removed Single quote purposefully to see the error
and LEDGER <>'CHC Consolidation Ledger'
and Department = '?vDept?'
and Company = '?vCo?'
and Account ='?vAcct?'
and Division ='?vDiv?'
Prolog:
sTime = SUBST(pTime,5,2)|'-'|SUBST(pTime,1,3)|'-'|SUBST(pTime,9,2);
When i do ASCIIOUTPUT it gives Value FY-NOV-20.
I saved the TI and it showed the messge SQL failed. I still save and click on cell for drill through and i receive following error
So i saved my Query withError executing SQL query:
"Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = FY-Nov-20 and LEDGER <>'CHC Consolidation Ledger' and
Department = '103' and Company = '20' and Account ='411'
and Division ='2003'"
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = '?sTime?' => I put back Single quote
and LEDGER <>'CHC Consolidation Ledger'
and Department = '?vDept?'
and Company = '?vCo?'
and Account ='?vAcct?'
and Division ='?vDiv?'
Now TI saved successfully. When i click on cell to drill through it brings empty window. [ FYI : If i hard code value to 'FY-NOV-20' then i do see the drill down value in the window]
Period_Name = 'FY=NOV-20' works well while Period_Name= '?sTime?' (ASCIIOUTPUT VALUE 'FY-NOV-20') does not work.
Following Articles i went through to DEBUG:
https://www.tm1forum.com/viewtopic.php?t=722
https://www.tm1forum.com/viewtopic.php?t=6036
Please feel free if you require further information.
Thank You
Dharav
-
- 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: Drill Through to ORACLE ODBC
You do realize that the date value you supply to Oracle has to be encapsulated in apostrophes don't you? You have to send 'FY-Nov-20' and not FY-Nov-20.
Code: Select all
sTime = ''' | SUBST(pTime,5,2)|'-'|SUBST(pTime,1,3)|'-'|SUBST(pTime,9,2) | ''';
-
- Posts: 72
- Joined: Wed Aug 15, 2018 3:18 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: Drill Through to ORACLE ODBC
Hi, Tomok
I removed single quote purposefully to see the error. I have mentioned that in my previous post.
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = '?sTime?
and LEDGER <>'CHC Consolidation Ledger'
and Department = '?vDept?'
and Company = '?vCo?'
and Account ='?vAcct?'
and Division ='?vDiv?'
In the above code if i replace '?sTime?' with 'FY-NOV-20' then drill works.
When i did asciioutput or message log to find the values of '?sTime?' then it is 'FY-NOV-20'.
'?sTime?' variable value is same as 'FY-NOV-20' then why drill is not working. Am i missing something obvious?
Thank You
DHARAV
I removed single quote purposefully to see the error. I have mentioned that in my previous post.
Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = '?sTime?
and LEDGER <>'CHC Consolidation Ledger'
and Department = '?vDept?'
and Company = '?vCo?'
and Account ='?vAcct?'
and Division ='?vDiv?'
In the above code if i replace '?sTime?' with 'FY-NOV-20' then drill works.
When i did asciioutput or message log to find the values of '?sTime?' then it is 'FY-NOV-20'.
'?sTime?' variable value is same as 'FY-NOV-20' then why drill is not working. Am i missing something obvious?
Thank You
DHARAV
-
- 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: Drill Through to ORACLE ODBC
If this:
is copied verbatim from the error log then my original post is still on point.Error executing SQL query:
"Select * from xxchc.xxgl_acc_analysis_det_tbl where
Period_Name = FY-Nov-20 and LEDGER <>'CHC Consolidation Ledger' and
Department = '103' and Company = '20' and Account ='411'
and Division ='2003'"
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Drill Through to ORACLE ODBC
Just ruling out anything obvious, you haven't applied any case sensitivity to any of your table columns have you?