nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Dynamic WHERE clause not working

jmulders

I've searched this forum but still can't get my BIRT report working. I'm hoping someone can tell me where I'm going wrong. 
 
I have a report that prompts for a "Time Interval" of Day, Week, Month, Quarter, or Year. Based on the response, it should display transaction counts by that time interval. 
 
My data set looks like this: 
 
 
SELECT 
 
SUM(TRANS_SMRY.TRANS_COUNT) as Trans_Count, 
SUM(TRANS_SMRY.TRANS_COUNT)/1000000 as Trans_Count_Mlns, 
**** as Time_Interval 
FROM 
 
TRANS_SMRY, 
DATE_DIM 
WHERE 
 
DATE_DIM.DAY=TRANS_SMRY.TRANS_DATE 
AND DATE_DIM.DAY BETWEEN ? AND ? 
AND TRANS_SMRY.DATA_SOURCE_NAME IN ? 
AND TRANS_SMRY.REGION_NAME IN ? 
GROUP BY 
 
**** 
 
In the Before Open method of the dataset, I have the following code: 
 
 
this.queryText=this.queryText.replace("****",switch (params["pTimeInterval"].value) 
case "Day":"to_char(DATE_DIM.DAY,'yyyy/mm/dd')"; 
case "Week":"DATE_DIM.CAL_YEAR_NUMBER||' Wk '||to_char(DATE_DIM.CAL_WEEK_NUMBER,'09')"; 
case "Month":"to_char(DATE_DIM.DAY,'yyyy/mm')"; 
case "Quarter":"DATE_DIM.CAL_YEAR_NUMBER||' Q'||DATE_DIM.QUARTER_OF_YEAR"; 
case "Year":"DATE_DIM.CALENDAR_YEAR_NAME";default:""); 
 
When I preview the report, I'm getting an error: 
 
"There is a non-fatal error. There are errors evaluating script beforeOpen()." 
 
But there's nothing very specific in the error messages. Any ideas? 
 
Thanks 
Judy

Find more posts tagged with

Comments

mwilliams

In your initialize script, put QT = "";

In your beforeOpen, after your case statement, put:

QT = this.queryText;

In your design, after any element that uses the dataSet you put the beforeOpen code into, add a dynamic textbox and use the expression:

QT;

This should show you the value of your queryText, in your report. Does it look as it should?

jmulders

Michael, thanks for the suggestion, but it won't get that far. I added the code you suggested, but when I click "Preview Report", I get the error and then the report won't run.

I've attached a Word doc (*.docx) with a screen print of the error. Hope you can read it!

Judy

mwilliams

Try doing your switch statement outside of the replace() and then using a variable to store the value in to use in the function. You're probably getting some syntax error there.

jmulders

Hi Michael, I did try that, but couldn't get it to work. I might try it again. Thanks for the reply.

Judy

mwilliams

You also need to add a "break;" at the end of each case, probably.

jmulders

I did add a "break;" to the end of each case, but that didn't work either.

Now I'm trying dynamic visibility to show or hide charts (based on the value of a parameter). So far, that's working. It's klugy, but it works.

Regards,
Judy

mwilliams

Can you attach your actual report? I won't be able to run it, but I might be able to make any necessary changes anyways. Then, I'll just send it to you to test run!

jmulders

Hi Michael,

I've attached the version with the case statement. I added "break;" to each case, but that didn't work.

As I said, I had another version with a separate variable that was assigned the WHERE clause based on value of the parameter, then I concatenated that to the SQL statement, but that didn't work. (So the "switch" function was outside the "replace" function.)

Regards,
Judy

mwilliams

I don't see the report. Can you attach it again?

jmulders

Hi Michael,

I've attached the report.

Regards,
Judy

mwilliams

Try it like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
temp = "";
switch (params["rpTimeInterval"].value){
case "Day": temp = "to_char(DATE_DIM_V.DAY,'yyyy/mm/dd')";
break;
case "Week": temp = "DATE_DIM_V.CAL_YEAR_NUMBER||' Wk '||to_char(DATE_DIM_V.CAL_WEEK_NUMBER,'09')";
break;
case "Month": temp = "to_char(DATE_DIM_V.DAY,'yyyy/mm')";
break;
case "Quarter": temp = "DATE_DIM_V.CAL_YEAR_NUMBER||' Q'||DATE_DIM_V.QUARTER_OF_YEAR";
break;
case "Year": temp = "DATE_DIM_V.CALENDAR_YEAR_NAME";
break;
default: temp = "";
}

this.queryText=this.queryText.replace('****',temp);
</pre>

jmulders

Haven't tried that yet, but if I have time, I'll try your code and update the topic. Thanks for the input.

Regards,
Judy