I was recently asked the following:
I'm starting to learn BIRT and have recently taken over report writing at my company. I'm confused about the best way to pass information to a sub-report. I've looked at some of our existing reports and they don't do it the same way.
I responded back that there are two primary method to pass information from a main report to a sub-report.
I call this technique the IBM Method because it's how IBM would teach a new user of BIRT to pass information to a sub-report and is typically the technique seen in any out of the box report from IBM. An example of the Open Method script on a sub-report is shown below:
JobTaskdataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName()); JobTaskdataSet.open(); var sqlText = new String(); sqlText = "select jobtask.jpnum as jptask_jpnum, jobtask.jptask, jobtask.description as jptask_desc, " + " jobtask.taskduration as jptask_dur " + " from jobtask " + " where jobtask.jpnum = '" + rows["pmseq_jpnum"] + "'" + " and jobtask.siteid = '" + rows["siteid"] + "'" + " order by jobtask.jptask " ; JobTaskdataSet.setQuery(sqlText);
The key in the IBM Method is a report writer will explicitly set the comparison of a field in the sub-report against a value passed from the main report. For example:
+ " and jobtask.siteid = '" + rows["siteid"] + "'"
In this case, the field jobtask.siteid is being explicitly compared against the value "siteid" from main report.
I call this technique the QBR Method because of the when a Detailed report is created in Maximo, it will use the method described below in the system generated .rtpdesign file. Below is the Open Method script from a QBR report that included information from the PO and POLINES tables. Because the report included information from the two different tables, Maximo forced the report to be a Detailed report. The report that is created by Maximo will have a main and sub-report datasets. The dataset below is from the sub-report for the POLINE information.
dataSet_poline = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName()); dataSet_poline.open(); var sqlText = new String(); sqlText = "select poline.polinenum,poline.description,poline.orderqty,poline.orderunit, + " poline.unitcost, poline.linecost,poline.gldebitacct,poline.requestedby " + " from poline " + " where " + " poline.ponum = ? " + " and poline.siteid = ?" + " order by poline.polinenum " ; dataSet_poline.setQuery(sqlText); dataSet_poline.setQueryParameterValue(1, rows["p_ponum"]); dataSet_poline.setQueryParameterValue(2, rows["p_siteid"]);
In the QBR Method, the Maximo generated rptdesign file will implicitly set the comparison of a field in the sub-report against a value passed from the main report. For example:
+ " and poline.siteid = ?"
In this case, the field poline.siteid is being compared against a parameter value. Further down in the report, the parameter is defined not as one an end user would enter, but is explicitly set against a value from the main report:
In the QBR Method, the sub-report field is set against a parameter value and that parameter value is set against a value from the main report. In the example above, the field poline.siteid is implicitly set to the main report value via the report parameter.
Differences between methods
So what are the functional differences between the two methods? Nothing. While the QBR Method uses two steps (main report value -> paramenter -> sub-report value) and the IBM Method uses one step (main report value -> sub-report value), in the end they both accomplish the same thing. While there could be a performance hit on a report that is run against thousands of records or run by hundreds of employees multiple times an hour, those are edge cases. For a normal Maximo user, they should see no difference between the two methods.
What about other methods
I originally stated that the IBM Method and the QBR Method are the two main techniques to pass information from a main report to a sub-report. There are other methods that are used to pass information along. Two examples of other methods used to pass information within the report include:
- The Asset Cost Rollup report actually pulls information in the Initialize Method script instead of the Open Method.
- The Projected PM Labor report pulls information on the Fetch Method script in both the main report and sub-report datasets.
Take a look at the rptdesign files of these two reports in BIRT to see details on how these two reports were written.