Sometimes even the "experts" need to be slapped back into reality. This week I got slapped so hard I felt bad for originally claiming my problem was a bug in BIRT. [1] Typically [2] when you design a BIRT report, the report is based on information from an associated Maximo application - e.g. WO Print report is associatied with WO Tracking. So when you create an open method for the report that would typically run against the WO Track app, it could look like this:

sqlText = "select workorder.wonum, workorder.description, workorder.reportdate, "
+ " workorder.pmnum, workorder.orgid, workorder.targstartdate, workorder.siteid, "
+ " workorder.status, workorder.statusdate from workorder "
// Include the Maximo where clause
+ " where " + params["where"]
+ " and ( (workorder.pmnum is null and (workorder.wrtype is null or workorder.wrtype <> 'QA') "
+ " and workorder.reportdate >= (dateadd(day, -14, convert(varchar, getdate(), 101))) " 
+ " and workorder.reportdate < (dateadd(day, -7, convert(varchar, getdate(), 101)))) "
+ " and workorder.istask=0 "
+ " or (workorder.pmnum is not null AND workorder.worktype not in ('PROD', 'QAQC') "
+ " and workorder.targstartdate >= (dateadd(day, -14, convert(varchar, getdate(), 101))) "
+ " and workorder.targstartdate < (dateadd(day, -7, convert(varchar, getdate(), 101))) )) "
+ " and workorder.istask = 0 "

The critical line is + " where " + params["where"], because this is the magic sauce on how the BIRT report is getting parameter information from the application. However, when you add an additional dataset to your report, the tables that dataset is using may, or more likely, may not have any association. So in this example, the second dataset in the report is pulling information from the LABTRANS table could look like this:

sqlText = "select labtrans.startdate, labtrans.laborcode, labtrans.regularhrs, labtrans.siteid, labtrans.orgid, " 
+ "labtrans.refwo, isnull(substring(labtrans.gldebitacct, 4, 4), '9999') as gldebitacct, glcomponents.comptext "
+ " from labtrans "
+ " join glcomponents on (glcomponents.orgid = labtrans.orgid) and (glcomponents.compvalue=isnull(substring(labtrans.gldebitacct, 4, 4), '9999')) and glcomponents.glorder=1 "
+ " where " + params["where"]
+ " and labtrans.siteid = '" + rows[0]["siteid"] + "'"
+ " and labtrans.startdate >= (dateadd(day, -14, convert(varchar, getdate(), 101))) "
+ " and labtrans.startdate < (dateadd(day, -7, convert(varchar, getdate(), 101))) "

There is a problem with this SQL context when the report is run in the BIRT Report Designer versus running the report in Maximo. The line + " where " + params["where"] comes into play again. In the BIRT Report Designer this code snippet does nothing because the report is getting passed a 1=1 condition for the where clause.

But when the report is run in Maximo, the + " where " + params["where"] in the second dataset tries to pull in parameter information too. Since the second dataset is based on tables not directly related to the application it's running in the second dataset will fail to pull in any infromation at all. The proper method is to remove the + " where " + params["where"] and hard code the where statement.

sqlText = "select labtrans.startdate, labtrans.laborcode, labtrans.regularhrs, labtrans.siteid, labtrans.orgid, " 
+ "labtrans.refwo, isnull(substring(labtrans.gldebitacct, 4, 4), '9999') as gldebitacct, glcomponents.comptext "
+ " from labtrans "
+ " join glcomponents on (glcomponents.orgid = labtrans.orgid) and (glcomponents.compvalue=isnull(substring(labtrans.gldebitacct, 4, 4), '9999')) and glcomponents.glorder=1 "
+ " where labtrans.siteid = '" + rows[0]["siteid"] + "'"
+ " and labtrans.startdate >= (dateadd(day, -14, convert(varchar, getdate(), 101))) "
+ " and labtrans.startdate < (dateadd(day, -7, convert(varchar, getdate(), 101))) "

So the golden rule is:

Don't use + " where " + params["where"] on secondary datasets.

Got any questions? Feel free to hit me up on Twitter at @MyGeekDaddy.