Scripted Query For Using a Report Parameter of DATETIME format

italugyaja

Hi Guys!!

I am working on a requirement which i am listing here .This Requirement requires input parameter as Start date and End date as in puts form the user in maximo and then according to that some straight forward Columns will be picked up from the WORKORDER TABLE.

Now in maximo the Startdate and EndDate have datatype as DATETIME
The report page will have 2 textboxes in the following manner

STARTDATE---TxtBox---Datetimeicon
ENDDATE----TxtBox

---

Datetimeicon.

The above 2 lines say that u have a textbox along side which is a datetime calendar type icon from which user selects the date this date is then populated in the text box.

Now this value of text box should be matched with the reportparameter of BIRT Design which is again a DATETIME type.

I need a scripted query which will get the DATETIME from the text box match it with the databse thro BIRT's input parameter and display the resultset.

The simple query which would have run in case there was a manual entry by user would be .

Select WONUM,STATUS,WOTYPE from WORKORDER where STARTDATE =? and ENDDATE=?

Now i want this query to be in scripted form which can support that value selected thro that calendar type datetime icon on the request page of maximo

Can any one Please provide the exact query.
If anyone is unable to understand the scenario please pool in ur input .I will try my best to re phrase the scenario in more lucid manner.

Thanks
AJ

kingsdare

Hi AJ,

I'm new to the forum and new to the Birt report writer, but this is how I've been doing what your asking.

I add the following code to the open event for the dataset.

maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();

// Add query to sqlText variable.
sqlText="select workorder.wonum,workorder.actfinish from workorder "

// Include the Maximo where clause
+ "where convert(varchar(10),actfinish,101) between "
+ "'" + params["startdate"] + "'"
+ " and "
+ "'" + params["enddate"] + "'"
;

maximoDataSet.setQuery(sqlText);

I use the SQLServer convert function so I get all of the rows for those days without having to specify times.

Then update the fetch event with the fields your fetching.

Dave