Methods for date selection in BIRT

A while back I wrote about how to use Javascript to calculate date functions in BIRT. In this post I will show different methods that can be used to automatically calculate dates so it can be scheduled as a rolling date report. The example I will use is for a report to run every day and show a given date range summary - e.g. WO costs month to date. Since this report needs to run everyday, the report itself should determine what the first and last day of the month is.

Hard code selection in Open Method

The easiest way to do your date selection is hard code it in the query itself. Typically this can be done with date specific SQL for your database type. For example, using a selection statement for SQL Server to automatically show dates from 1 week ago could be coded liked this:

In this example, the following lines hard code the selection query to basically one week ago.

+ " and workorder.reportdate >= (dateadd(day, -14, convert(varchar, getdate(), 101))) "
+ " and workorder.reportdate < (dateadd(day, -7, convert(varchar, getdate(), 101))) "

While this is a quick and easy, there is no way to reuse the calculated date. The next two methods calculate the same date value, but allow a report writer to reuse the information.

Date selection in the Open Method (MXReportSqlFormat)

This method uses some of the custom classes in the BIRT Report Designer and Maximo to calculate and reuse the information.

The code for the open method:

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

var sqlText = new String();

var currDate = new Date();
var firstdate = new Date(currDate.getFullYear(), currDate.getMonth(), 1);
var lastdate = new Date (new Date(currDate.getFullYear(), currDate.getMonth() +1 , 1) - 1);

sqlText = "select workorder.workorderid, workorder.wonum, workorder.description, workorder.woclass, workorder.status, "
+ " workorder.assetnum, workorder.worktype, workorder.wopriority, workorder.lead, workorder.reportdate, "
+ " workorder.location,  workorder.jpnum, workorder.parent, workorder.siteid, workorder.schedstart, workorder.schedfinish "
+ " from workorder "
+ "where " + params["where"]
+ "  and workorder.istask = 0 "
+ " and workorder.reportdate >= " + MXReportSqlFormat.getTimestampFunction(firstdate) 
+ " and workorder.reportdate < " + MXReportSqlFormat.getTimestampFunction(lastdate) 
+ " order by workorder.siteid, workorder.wonum"
;

maximoDataSet.setQuery(sqlText);

When you a run a report using this date selection method, you can include a DEBUG statement on the initialize method to see what is actually getting parsed by BIRT. When you look at the debug output you would see the following:

Wed Aug 07 21:33:39 CDT 2013  INFO  DataSet [dataSet]: select workorder.workorderid, 
workorder.wonum, workorder.description, workorder.woclass, workorder.status,  
workorder.assetnum, workorder.worktype, workorder.wopriority, workorder.lead, 
workorder.reportdate,  workorder.location,  workorder.jpnum, workorder.parent, workorder.siteid, 
workorder.schedstart, workorder.schedfinish  from workorder  where workorder.istask = 0  
and workorder.reportdate >= { ts '2013-07-01 00:00:00.000' } 
and workorder.reportdate < { ts '2013-07-31 23:59:59.999' } order by workorder.siteid, workorder.wonum
Wed Aug 07 21:33:39 CDT 2013  INFO  DataSet [dataSet] close called.

You can see BIRT took the calculated values from the firstdate and lastdate variables and then converted them to a date string. The beauty of this method is that since the date/time values for firstdate and lastdate are variables, the report can be reused against any Maximo install - SQL Server, Oracle or DB2.

Date selection in the Initialize Method (aka pure Javascript)

This method uses just Javascript to calculate the first of the month and last day of the month and then transform those dates to a format that can be used by BIRT. Looking at the initialize method on the report you find some of the javascript that is calculating the first and last dates.

The trick here is the fact that javascript doesn't handle objects the same as BIRT can, [1] so that's why each variable is updated with a string version of the calculated date. The actual code for the initialize method is :

importPackage(Packages.com.ibm.tivoli.maximo.report.script);

mxReportScriptContext = MXReportScriptContext.initialize(reportContext);

mxReportScriptContext.setDefaultLogLevel("DEBUG");
mxReportScriptContext.setDefaultLogFile("/temp/my_report.log");

var now = new Date(); 

var first_month = new Date(now.getFullYear(), now.getMonth(), 1);
first_month = first_month.getFullYear() + "-" + (first_month.getMonth()+1) + "-" + first_month.getDate() + " 00:00";

var end_month = new Date (new Date(now.getFullYear(), now.getMonth() +1 , 1) - 1);
end_month = end_month.getFullYear() + "-" + (end_month.getMonth()+1) + "-" + end_month.getDate() + " 23:59";

Again, using the log output when the report is run, we can see how BIRT parses the selection to Maximo:

Wed Aug 07 23:21:54 CDT 2013  INFO  DataSet [dataSet]: select workorder.workorderid, 
workorder.wonum, workorder.description, workorder.woclass, workorder.status,  
workorder.assetnum, workorder.worktype, workorder.wopriority, workorder.lead, 
workorder.reportdate,  workorder.location,  workorder.jpnum, workorder.parent, workorder.siteid, 
workorder.schedstart, workorder.schedfinish  from workorder workorder.istask = 0  
and workorder.reportdate >= '2013-7-1 00:00' and workorder.reportdate < '2013-7-31 23:59' 
order by workorder.siteid, workorder.wonum
Wed Aug 07 23:21:54 CDT 2013  INFO  DataSet [dataSet] close called.

Now because these variables were created on the initialize method, these variables can be reused in the report. In the example below, the variable was added to a Data element and then included in the report header.