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,¹ 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 OOTB² 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. Set the SQL connection string to a variable.

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

4. Use the previously set variable userDispNameSQL for the data fetch:

   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 query:

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

as a selection statement in my BIRT report.

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

Update 2020-06-02: Clarified the step by step breakdown of the secondary database connection after further testing.