Extending BIRT's hidden parameters - Pt 2

I've continued to experiment with the use of Maximo BIRT's hidden parameter. In my previous post I was able to display values that were generated by related information of the Maximo user who ran the report. But the original data selection used the hidden username parameter in the SQL where clause:

sqlText = "select inventory.itemnum,i4.description,inventory.location,inventory.binnum,inventory.catalogcode,inventory.siteid, "
+ " inventory.status,i4.commodity,i4.commoditygroup,i4.itemid "
+ " from inventory " 
+ " left outer join item i4 on i4.itemnum  =  inventory.itemnum and i4.itemsetid  =  inventory.itemsetid " 
+ " where " +  params["where"] 
+ " and inventory.siteid  = (select defsite from maxuser where userid=  '" + params["userName"] + "')" 
+ " order by inventory.itemnum " 
;

The line:

+ " and inventory.siteid  = (select defsite from maxuser where userid=  '" + params["userName"] + "')"

gave essentially the same functionality as:

siteid = (select defsite from maxuser where userid = :user)

The larger goal was to use the username parameter to pull related information of the Maximo user and use that information directly in the main dataset's SQL selection statement.

Changing selection sequence

In order to get the main dataset to use a parameter value in the SQL select statement, the parameter value has to be understood before the main dataset selection is made. BIRT runs data connections in the order they are presented in the Open method. The parameter values displayname and site_ID are available for use in the main dataset by moving the secondary connections above the main SQL select statement section.

Now the main dataset SQL where clause can use the site_ID parameter directly in the SQL selection statement. 

sqlText = "select inventory.itemnum,i4.description,inventory.location,inventory.binnum,inventory.catalogcode,inventory.siteid, "
+ " inventory.status,i4.commodity,i4.commoditygroup,i4.itemid "
+ " from inventory " 
+ " left outer join item i4 on i4.itemnum  =  inventory.itemnum and i4.itemsetid  =  inventory.itemsetid "
+ " where " +  params["where"]
+ " and inventory.siteid =  '" + params["site_ID"] + "'" 
+ " order by inventory.itemnum "
;

The use of the params["site_ID"] parameter value now gives the report the functionality of being run against only records the Maximo user is currently checked into.

Comments

Top