BIRT using dynamic sql stmt

olavtar

Hi Experts,
I have a dynamic sql stmt that I'm trying to use in the DataSet. One of the variables is the param that I have to pass at a runtime from JSP. In the sql stmt it looks like this:

if (@DateRange='Last 7 Days')
begin
set @dbfromdate=dateadd(day,-7, @currdate)
set @dbthroughdate=@currdate
end
else if (@DateRange='Last 30 Days')
begin
set @dbfromdate= dateadd(day,-30, @currdate)
set @dbthroughdate=@currdate
end

@DateRange is my param that I have to pass to BIRT at runtime, how do I change this so it would except date range as a param and I would not have to declare it in sql. There are bunch of IF smts like that in the query I have. I created a Report Parameter, I also created parameter in the data set and linked it to the Report Parameter, but I don't know what to change @DateRange to so it would still work in the sql.

Thank you ahead of time for your help.

bhanley

You use question marks in a query to handle the dynamic substitution of parameters. <br />
<br />
However, I would not do all this logic in the SQL itself. Instead, you can do the calculation logic in the BIRT scripting layer (prior to the query firing off).<br />
<br />
Here is an idea on how to handle this. It assumes three parameters on the report:<br />
<ul class='bbcol decimal'><li><em class='bbc'>DateSpan </em>(Text, public, required, user-supplied)</li><li><em class='bbc'>StartDate </em>(Date, hidden, not required)</li><li><em class='bbc'>EndDate </em>(Date, hidden, not required)</li></ul>
<br />
Using these three parameters you can do the calculation us need in the scripting layer (and take the burden off your SQL).<br />
<br />
The script (on the validate event for "<strong class='bbc'>DateSpan</strong>" example) could look like this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>params["EndDate"].value = new Date();

if(params["DateSpan"].value.match("Last 30 Days")){
params["StartDate"].value = DateTimeSpan.subDate(params["EndDate"].value, 0, 0, 30);
return true;
}
else if(params["DateSpan"].value.match("Last 7 Days")){
params["StartDate"].value = DateTimeSpan.subDate(params["EndDate"].value, 0, 0, 7);
return true;
}
else{
return false;
}
</pre>
<br />
NOTE if you use the validate event you have to return true or false before exiting. Using this method has the added benefit of making sure the specified a valid time frame.<br />
<br />
In BIRT, you use question marks for dynamic parameter substitution. So your query would look like this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT something
FROM somewhere
WHERE date_field BETWEEN ? and ?</pre>
<br />
I may have confused this issue (I hope I did not) but that is how I would solve this based on my reading of your question.