Writing Portable BIRT Reports across DB2 databases

The following steps need to be applied to BIRT reports if they need to be run on DB2 databases for both ZOS and non-ZOS platforms.

1. Create a new 'Viewer Configuration' parameter, via the CURAM Application, called 'db2numbertype' with a parameter value of 'bitdata'
2. Copy and paste the existing dataset for the relevant report in BIRT Eclipse and
   • Rename this new dataset to be 'zos' - all in lowercase
   • Edit the dataset and remove any parameters specified
   • Go to the query and remove any lines with question marks for parameters
3. Append the following code to the 'Before Factory' script area and enter the relevant chart name in the code below

   if(params["db2numbertype"].value == "bitdata") {
   
     mytable = reportContext.getDesignHandle().
     findElement("<ENTER CHART NAME HERE>"); 
     
     mytable.setProperty( "dataSet", "zos" ); 
   
   }

4. Click on the 'zos' data set and then select the 'script' tab Tab (located at the bottom of the 'Report Designer' window) and choose the 'beforeOpen' Script option. Edit the following code snippet to match your query needs and paste it in the report.

   importPackage(Packages.java.math);
   importPackage( Packages.biapp );
   
   var param_<append parameter name here> = BigDecimal.valueOf
   (params["<ENTER Parameter name here>"].value);
   
   param_<append parameter name here>_long_to_string = UniqueIDUtil.longToString
   (param_<append parameter name here>);
   
   this.queryText = " <Enter the SQL select statement here>"

Note: The parameter name is case sensitive. You must enter it as it appears in the report.

E.g. The following is an example of a completed code section. You will need to create the relevant parameters based on the query given. In this example there is only 1 parameter called 'concernRoleID', this parameter is passed in to the report and then it is converted to be used in the SQL query as follows:

importPackage(Packages.java.math);
importPackage( Packages.biapp );

var param_concernroleid = BigDecimal.valueOf(params["concernRoleID"]
.value);

param_concernroleid_long_to_string = UniqueIDUtil.longToString
(param_concernroleid);

this.queryText = "SELECT  numberPermanentStaff as numberStaff"
   + ", 'PermanentStaff' as staffType"
   + " FROM Employer"
   + " WHERE concernRoleID ="  + param_concernroleid_long_to_string 
   + " UNION"
   + " SELECT" 
   + " numberCasualStaff as numberStaff,"
   + " 'CasualStaff' as staffType"
   + " FROM Employer"
   + " WHERE concernRoleID ="  + param_concernroleid_long_to_string