TM1 Forum

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

Try:
or:

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

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.

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

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 ?

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

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.

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

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.

Just ruling out anything obvious, you haven't applied any case sensitivity to any of your table columns have you?

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.