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.



Previous posts:

  1. Extending the use of Maximo's hidden BIRT parameter

    One of the common queries used by Maximo power users is:

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

    This selection limits records to the default site the current Maximo user is checked into. So if a query is saved for future use, it will work with any site a …

  2. Conditionally make 'Memo' a requirement

    I will always admit I get inspiration on how I administer my company's Maximo environment from other sources. The most recent update I've made to our Maximo environment was based on a something I've had on the back burner, but got a virtual nudge from Steven Shull (@shullsa) after reading …

  3. Controlling BIRT Chart Colors in Maximo

    One of the efforts I'm making as we head into 20201 is data consistency. I started looking at some of our key reports and noticed that as our maturity evolved, our reports evolved as well, but sometimes in a disjointed way. We'd discover a new technique in BIRT or …

  4. Running a Maximo Escalation to catch an event every 30d

    I normally read through the Maximo forums for new ideas or lend a helping hand when I can. A recent question was asked on how to have a Maximo Escalation send a notification every 3 months.

    Looking at the question, there were a couple of points raised:

    • The escalation needs …
  5. BIRT Data Set Types

    This is complete a personal reference for Maximo BIRT Reporting. The information is originally sourced from IBM's Report Developer Guide v7.1 PDF file. 1

    The following chart shows the database type, the corresponding BIRT Data Type, and the method used within the BIRT Designer to retrieve its value. This …

Top