Use javascript variable in data set open method?

jverly01

In an effort to reduce duplicated work, I want to see if there is a way to use a javascript variable and have it passed/used as part of the select statement in the dataset open method? <br />
<br />
On the report's Initialize method, I have the following javascript: <br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>var now = new Date();
var begin_shift = new Date(now.getFullYear(), now.getMonth(), now.getDate(), 0, 0, 0, 0);
begin_shift.setHours(begin_shift.getHours() -7);

var end_shift = new Date(now.getFullYear(), now.getMonth(), now.getDate(), 0, 0, 0, 0);
end_shift.setHours(end_shift.getHours() +5);
</pre>
<br />
So instead of replicating the same date/time calculations in SQL, I'd like to pass the date the javascript generates into the open method for the main data set:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();

// Add query to sqlText variable.
sqlText = "select workorder.wonum, workorder.description, workorder.worktype, workorder.changedate "
+ " from workorder "
// Include the Maximo where clause
+ " where " + params["where"]
+ " and workorder.changedate < end_shift "
+ " and workorder.changedate >= begin_shift "
;

maximoDataSet.setQuery(sqlText);
</pre>
<br />
I tried to use the <pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setGlobalVariable("begin_shift","end_shift");</pre>, but I got the following error:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>Data (id = 407):
+ A BIRT exception occurred: Can not convert the value of end_shift to Date type.. See next exception for more information.
Can not convert the value of end_shift to Date type.</pre>
<br />
Any ideas?

wwilliams

I tried this in SQL Server

In the initialize
I added
begin_shift = now.getFullYear()+"-"+ (now.getMonth()+1)+"-"+ now.getDay() + " "+ (now.getHours() -7)+ ":"+ now.getMinutes();

and in the open

+ " and workorder.changedate < '" + end_shift + "'"
+ " and workorder.changedate >= '" + begin_shift +"'"

Might be some other ways to do it, but this seemed like a simple straight forward way.

-W