When developing BIRT reports, you will eventually come across a time when you need to pass a value from a parent data set query and use it as a parameter to in a child data set.  For example, within a PO report, you can create a data set that queries the PO record and then create another child data set to query the PO lines.  In this scenario, you will want to pass the POID or PONUM to the child data set to get the lines.

In this example, I have two data sets like so:

To define a parameter you want to use, we need to configure them on the child data set.  Right click on the ‘poLinesDataSet’ and click ‘Edit’.  On the left side, select ‘Parameters’.  Here you want to click ‘Add New’ and specify the name of the parameter.  It can be anything you want, in this case, I named it POID.

Set the direction to ‘Input’ and default value t0 zero.  Lastly, set the data type to ‘String’.  This is very important!  Even though the POID is defined as a long integer value in the database, if you specify this value as an ‘Integer’, there will be some cases where your parameter won’t work because the value exceeds the max value of an integer.  So using it as a string should guarantee that it works.  Save and hit OK.

Next, you can add the ‘poLinesDataSet’ to the report by clicking and dragging it to the form designer.  Once on the form, right click on the table and click ‘Edit Data Binding’.

Click ‘Dataset Parameter Binding’, select the parameter and ‘Edit’.  In this dialog, you can use the tool to select the value you want to pass from the parent data set to this child data set.  In this case, I choose to select the POID value from the parent data set.

Click Ok.  Now the next step is to open the script for the child data set so we can use the inbound parameter.  To access the parameter, you can use a variable named ‘inputParams’.  So to get the value, we can write:

var parentPoId = inputParams['POID'];

You can use the SQL binding like so:

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

var sqlText = new String();

// Add query to sqlText variable.
sqlText = "SELECT * FROM POLINES WHERE POID = ?";

// Exclude the Maximo where clause
//sqlText += " where " + params["where"];

// Debug
//scriptLogger.debug(sqlText);

poLinesDataSet.setQuery(sqlText);
poLinesDataSet.setQueryParameterValue(1,inputParams["POID"]);

As you can see in line 10, I also followed the “golden rule”.