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 user is checked into. The challenge a lot of report writers have is using that same flexibility in BIRT reports.

Maximo has a built in parameter, userName,1 that allows a report writer to pull the USERID of the person who ran the report and have it show up on report itself. The IBM KB document on how 'How to add Username to V7 BIRT Report' shows the steps to use the hidden parameter in BIRT reports. But once you have the USERID of the person who ran the report, this should open a door to use other information related to the current report user.

So how can we pull the DEFSITE value out of the hidden parameter and into a useable data point in the BIRT report?

The rest of this works in Maximo 7.6.0.x with BIRT 4.3.1 and should work in Maximo 7.6.1.x. I'm describing my testing to date, so the "technical" term for some of these functional steps _will not_ be accurate.

Connecting to Maximo data

Normally when a report is created, the report uses a dataSet to initiate the connection to the database and conduct the SQL query for data results. This is a generic example of a report opening the connection to a Maximo dataset:

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

var sqlText = new String();

sqlText = " select ...
where " + params["where"]
and ...

maximoDataSet.setQuery(sqlText);

This is how all OOTB2 BIRT reports connect to the Maximo database.

Additional data connections

The "trick" I'm going to use to get to the related information from the MAXUSER table is to use the hidden parameter as part of an additional database call. On the Open method for the primary data set, I'll add code to make a new data set to make an additional database connection.

// User's Display Name
userDispNameDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "userDispNameDataSet");

userDispNameDataSet.open();

userDispNameSQL = "select DISPLAYNAME as Display_Name from person where personid = (select personid from maxuser where userid = upper('" + params["userName"] + "'))"

userDispNameDataSet.setQuery(userDispNameSQL);

if(userDispNameDataSet.fetch()) { params["displayname"] = userDispNameDataSet.getString("Display_Name"); }

userDispNameDataSet.close();
// End User's Display Name

This code will do the following:

  1. Create a new data set:

    userDispNameDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "userDispNameDataSet");
    
  2. Open the database connection:

    userDispNameDataSet.open();
    
  3. Conduct a selection for a value, in this case DISPLAYNAME.

    userDispNameSQL = "select DISPLAYNAME as Display_Name from person where personid = (select personid from maxuser where userid = upper('" + params["userName"] + "'))"
    
  4. Set the value of the data set:

    userDispNameDataSet.setQuery(userDispNameSQL);
    
  5. Do a conditional check if the selection returned a value, and if it does, push that value to the corresponding parameter:

    if(userDispNameDataSet.fetch()) { params["displayname"] = userDispNameDataSet.getString("Display_Name"); }
    
  6. Close the database connection:

    userDispNameDataSet.close();
    

With this code, I'm able to use the built-in hidden parameter in the SQL select statement to duplicate the functionality of the

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

selection statement.

Example Report

The example report I'm using will pull some details from the Inventory app, with the goal of pulling details based on the user's current site they're checked into. If you run the report in Eclipse, you will need to manually enter the userName parameter, because the code to identify your USERID is inherent when running the report in Maximo.

The report will have 3 parameters:

  • userName
  • displayname
  • site_ID

When setting up the parameters, make sure the 'Is Required' is unchecked.

The userName parameter is the built in hidden parameter and the other parameter values will be pulled from other database connections.

When you add the report into Maximo, delete all of the parameters in the report setup in Maximo.

When the report is setup, the values for the displayname and site_ID will be presented:

So when the report is run, the report present the user's name and the site the user is checked into.

Now I have the ability to have a report run against a site, without having the user select the site.

Source Code

The complete Open method for the report is listed below.

dataSet_inventory = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
dataSet_inventory.open();
var sqlText = new String();

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 "
;

dataSet_inventory.setQuery(sqlText);
dataSet_inventory.registerDataTranslation("description", "itemid", "item", "DESCRIPTION");

// User's Display Name
userDispNameDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "userDispNameDataSet");
userDispNameDataSet.open();
userDispNameSQL = "select DISPLAYNAME from person where personid = " + "(select personid from maxuser where userid = upper('" + params["userName"] + "'))"
userDispNameDataSet.setQuery(userDispNameSQL); 
if(userDispNameDataSet.fetch()) { params["displayname"] = userDispNameDataSet.getString("displayname"); }
userDispNameDataSet.close();
// End User's Display Name

// User's Default SiteID
userSiteIDDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "userSiteIDDataSet");
userSiteIDDataSet.open(); 
userSiteIDSQL = "select DEFSITE from MAXUSER where USERID = upper('" + params["userName"] + "');"
userSiteIDDataSet.setQuery(userSiteIDSQL); 
if(userSiteIDDataSet.fetch()) { params["site_ID"] = userSiteIDDataSet.getString("DEFSITE"); }
userSiteIDDataSet.close();
// End User's Default SiteID

  1. The parameter is case sensitive.  

  2. Out of the box 



Previous posts:

  1. 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 …

  2. 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 …

  3. 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 …
  4. 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 …

  5. Adding Context Messages to your Maximo processes

    One of the analogies I've used over the years is that out of the box Maximo is a giant lump of clay - it's fun to play with in its raw form, but needs to be skillfully molded to be useful. One of these molding steps is to put your organization's …

Top