Drill Through to ORACLE ODBC

Post Reply
dharav9
Posts: 72
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Drill Through to ORACLE ODBC

Post by dharav9 »

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
Wim Gielis
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

Post by Wim Gielis »

Try:

Code: Select all

vSQL = Expand( 'Select * from xxchc.xxgl_acc_analysis_det_tbl where Period_Name = ''%sTime%''' );
or:

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
dharav9
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

Post by dharav9 »

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
Wim Gielis
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

Post by Wim Gielis »

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.
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
dharav9
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

Post by dharav9 »

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
Wim Gielis
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

Post by Wim Gielis »

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 ?
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
dharav9
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

Post by dharav9 »

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
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'"
So i saved my Query with

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
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: Drill Through to ORACLE ODBC

Post by tomok »

dharav9 wrote: Tue Feb 18, 2020 6:11 am 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'"
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) | ''';
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
dharav9
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

Post by dharav9 »

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
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: Drill Through to ORACLE ODBC

Post by tomok »

If this:
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'"
is copied verbatim from the error log then my original post is still on point.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Mark RMBC
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

Post by Mark RMBC »

Just ruling out anything obvious, you haven't applied any case sensitivity to any of your table columns have you?
dharav9
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

Post by dharav9 »

Mark RMBC wrote: Tue Feb 18, 2020 7:43 pm Just ruling out anything obvious, you haven't applied any case sensitivity to any of your table columns have you?

@MARK: Thank You so much. I just assigned Upper() and test it. It worked.
Post Reply