Add a relationship to a result set portlet in Maximo 7

Here is a nice little tip for when you create a result set portlet, let’s say for a list of workorders that are assigned to the current user, and you want to add a related column to the result set.  For example, you want the result set to show the workorder #, description, assetnum, asset description, worktype, and reportdate and the asset description is not part of the WORKORDER object in Maximo, so you won’t be able to select it.  Well here is a simple trick provided by IBM to add that relationship column to the result set.

When a Result Set Portlet is setup for the very first time, the user is presented with a list of persistent attributes or columns. These are pulled from Maximo Data Dictionary initially and then put inside another table, RESULTSETCOLS. There will be no related object attribute or dot-notation/join attributes available by default.

If there is a need to add a join column, steps below need to be followed.

Answer
For example, say the customer needs to see LOCATION.DESCRIPTION in a Result Set configured with a query based on Work Order Tracking (WOTRACK) application, where LOCATION is the name of the relationship WOTRACK object has with LOCATION object.
If a portlet was created previously using a query based on WOTRACK, then there would be data in RESULTSETCOLS table where APP=WOTRAK. Assuming there is data, prepare and execute insert statement similar to ones shown below depending upon the database platform being used:Oracle:

insert into resultsetcols
( app, attribute, maintable, resultsetcolsid ,rowstamp)
values
(‘WOTRACK’, ‘ LOCATION.DESCRIPTION ‘, ‘WORKORDER’, resultsetcolsseq.nextval, maxseq.nextval);

DB2:

insert into resultsetcols
( app, attribute, maintable, resultsetcolsid)
values
(‘WOTRACK’, ‘LOCATION.DESCRIPTION ‘, ‘WORKORDER’, nextval for resultsetcolsseq);

update maxsequence
set maxreserved=(select max(resultsetcolsid) from resultsetcols)
where tbname=’RESULTSETCOLS’ and name=’RESULTSETCOLSID’
;

Microsoft SQL Server:

insert into resultsetcols
( app, attribute, maintable, resultsetcolsid)
values
(‘WOTRACK’, ‘ LOCATION.DESCRIPTION ‘, ‘WORKORDER’, resultsetcolsseq.nextval);

Edit the Portlet and the new columns should be available in the Column Display Tab. [IBM Support]

Did You Know...

As Maximo Experts, we have developed several add-on products for Maximo that mobilize the work force, simplifies assignments, provides ad-hoc reporting capabilities and facilitates the seamless integration of Service Requests into Maximo.

Check out our products by clicking on the following links: EZMaxMobile, EZMaxPlanner and EZMaxRequest.

Find Out More

4 thoughts on “Add a relationship to a result set portlet in Maximo 7

Leave a Reply