Data Load of Job Plan Records via Maximo Integration Framework (MIF)

MIF data loading in format of csv for Job plans require a sequence of data loads to complete a ACTIVE job plan. 

A Job Plan consists of child objects like Job Plan Tasks, Job Labor, Job Materials, Job Services and Job Tools.

Create separate Enterprise Services for Job plan, Job Plan + Job Task, Job Plan + Job Material, Job Plan + Job Labor and Job Plan + Job Service. 

A Job Plan has different statuses, and we are not allowed to modify Job Plan after it is in ACTIVE status. So, we need to follow a sequence of data load to create a single Job Plan with its related child records.

Job Plan (DRAFT status) -> Job Tasks -> Job Labor -> Job Material -> Job Service -> Job Tool -> Job Plan (ACTIVE)

Use External system application to load these csv files.

Job Plan 

ORGID,SITEID,JPNUM,DESCRIPTION,STATUS,TEMPLATETYPE,JPDURATION,PLUSCREVNUM,PRIORITY,INTERRUPTIBLE
EAGLENA,BEDFORD,16353456,Circuit Breaker Plan,DRAFT,MAINTENANCE,0.75,0,1,1

Job Plan + Job Task

ORGID$SITEID$JPNUM$PLUSCREVNUM$JPTASK$DESCRIPTION_id$DESCRIPTION_LD$HASLD$TASKSEQUENCE$TASKDURATION$PLUSCJPREVNUM$PREDECESSORTASKS
EAGLENA$BEDFORD$16353456$1$10$"Locomotive gear"$"a) Consign the equipment in accordance with the LOTO procedure; 

b) Consign adjacent equipment;"$$1$0

Delimiter for loading Job task data should be dollar sign ($), because we often have comma (,) in long description in the JOBTASK, so in order to differentiate the delimiter and actual content, we use $ instead of comma (,).

Job Plan + Job Labor

ORGID,SITEID,JPNUM,PLUSCREVNUM,JPTASK,CRAFT,QUANTITY,LABORHRS,VENDOR
EAGLENA,BEDFORD,16353456,1,MECH,2,1.5,

Job Plan + Job Material

ORGID,SITEID,JPNUM,PLUSCREVNUM,JPTASK,ITEMSETID,ITEMNUM,ITEMQTY,DIRECTREQ,LOCATION,STORELOCSITE
EAGLENA,BEDFORD,16353456,1,10,ITEMSETID,1120002028,1,0,LABSTORE,BEDFORD

Job Plan 

ORGID,SITEID,JPNUM,DESCRIPTION,STATUS,TEMPLATETYPE,JPDURATION,PLUSCREVNUM,PRIORITY,INTERRUPTIBLE
EAGLENA,BEDFORD,16353456,Circuit Breaker Plan,ACTIVE,MAINTENANCE,0.75,0,1,1

Configuring Websphere 7 for SAML SSO to authenticate Users in Maximo

This post details on how to configure Websphere 7.x version for SAML SSO to authenticate users in Maximo application

What is SAML ? 

• Security Assertion Markup Language (SAML) is a standard for logging users into applications based on their sessions in another context
• Most organizations already know the identity of users because they are logged in to their Active Directory domain or intranet, So they use this information to login into Maximo
• SAML SSO works by transferring the user’s identity from one place (the identity provider) to another (the service provider)
• When the user accesses the Maximo URL, the application identifies the user's origin, then redirects the user back to the Identity provider for authentication 
• The user either has an existing active browser session with the identity provider or establishes one by logging into the identity provider 
• The identity provider (AWS or Azure) builds the authentication response in the form of an XML-document containing the user’s username or email address, signs it using an X.509 certificate, and posts this information to the service provider
• The service provider (Maximo) retrieves the authentication response and validates it using the certification and metadata
• The identity of the user is established and the user is provided with Maximo access

 Steps to be followed:

1. Login to the operating system where WebSphere is installed

2. Install the default SAML ACS (Assertion Consumer Service) servlet supplied with WebSphere

• If using Windows, open a command prompt
• Navigate to WAS application bin directory (/opt/IBM/WebSphere/AppServer/bin on Linux/Unix or C:\Program Files\IBM\WebSphere\AppServer\bin on Windows)
• We can install SAML ACS to a cluster or single-server. Please run the following command:    

Operating System	Command
Windows	wsadmin.bat -lang jython -f installSamlACS.py install clusterName  (or) wsadmin.bat -lang jython -f installSamlACS.py install nodeName serverName
Linux/Unix	./wsadmin.sh -lang jython -f installSamlACS.py install clusterName (or)  ./wsadmin.sh -lang jython -f installSamlACS.py install nodeName serverName

                where clusterName is the name of your WebSphere cluster ; nodeName and serverName are your node and server values respectively

• If you are using a web server such as IBM HTTP Server in front of your application be sure that the newly installed EAR is targeted to the web server
•  Login to the WebSphere Admin Console
•  Using the left-hand menu go to Applications and then WebSphere enterprise  applications
•  Click the link for WebSphereSamlSP
•  Under Modules click Manage Modules
• Confirm that both the cluster and the web server are assigned to the module

             If changes were required, generate and propagate the plugin configuration

• Using the left hand side menu, go to Servers, then Server Types and click Web Servers
• Click the checkbox next to your web server and click Generate Plug-in from the toolbar menu
• Click the checkbox next to your web server and click Propagate Plug-in from the toolbar menu
• Restart the web server

3. Create a new Security Domain 

• Using the left-hand menu, select Security then Security Domains
• Click New
• Provide a name and description for security domain
• Click OK

4. Confirm that Application Security is enabled

• From the list of Security Domains, click the new domain you created 
• Check the value next to Application Security. If the value is Enabled then you can continue on to step 5
• Expand the Application Security section and select Customize for this domain
• Enable the Enable application security checkbox and click Apply

5.  Configure a new Trust Association Interceptor

• From the list of Security Domains, click the new domain you created
• Expand the Trust Association section and select the Customize for this domain option
• Click to enable the Enable trust association checkbox and click Apply
• Click the Interceptors link under Trust Association
• Click New
• For the Interceptor class name enter com.ibm.ws.security.web.saml.ACSTrustAssociationInterceptor
• Under Custom Properties enter the property name sso_1.sp.acsUrl with a value of your ACS URL 
• Click New to add an additional property
• Enter the name sso_1.sp.EntityID and provide a value for the SP entity ID and click OK

6.  Save settings and synchronize nodes

7.  Export SAML SP metadata

• Navigate to the WAS application bin directory (/opt/IBM/WebSphere/AppServer/bin on Linux/Unix or C:\Program Files\IBM\WebSphere\AppServer\bin on Windows)

• Launch the wsadmin tool

Operating System	Command
Windows	wsadmin.bat -lang jython
Linux/Unix	./wsadmin.sh -lang jython

• Execute the following command 

AdminTask.exportSAMLSpMetadata('-spMetadataFileName sp_metadata.xml -ssoId 1 -securityDomainName DOMAINNAME')

• DOMAINNAME --> should be the same name which is created on Step 3  
• By default, metadatafile will be stored in this path "/opt/IBM/WebSphere/AppServer/profiles/ctgDmgr01"  

8. Share the Service Provider metadata to your IdP (Identity Provider like AWS or Azure Active Directory) with the following information:

• Target URL of the application
• IdP will provide its signing certificate inside the metadata file or request it separately and import it manually later
• Please validate the certificate in the signature KeyInfo element of the assertion from IdP provider

9. After you received your IdP's sp_metadata.xml, ClientMaximo.cer and entityDescription file - Import them 

• Launch the wsadmin tool using step 7
• Execute the following commands
• AdminTask.importSAMLIdpMetadata('-idpMetadataFileName idp_metadata.xml -signingCertAlias MyCertAlias -securityDomainName DOMAINNAME')
• AdminConfig.save()

If the idp_metadata.xml file is not in the same path as the wsadmin tool, then you will need to specify the full path to the file.

The value for signingCertAlias can be any string; it will be used to identify the signing certificate in the WebSphere Trust Store so just choose a suitable name that is not already in the store (see Security > SSL certificate and key management > Key stores and certificates > CellDefaultTrustStore > Signer certificates for a list of keys already in the store)

DOMAINNAME - should be the same name which is created on Step 3

• Exit the wsadmin tool and return to the WebSphere Admin Console

10. Verify TAI custom properties

• Using the left hand menu, select Security and then Security Domains
• Click the link to your security domain
• Expand the Trust Association section and click the Interceptors link
• Click com.ibm.ws.security.web.saml.ACSTrustAssociationInterceptor

 The following fields may be defined: 

Property	Value
sso_1.sp.acsUrl	value set in Step 5 - https://hostname/samlsps
sso_1.sp.EntityID	value set in Step 5 - https://hostname/
sso_1.sp.targetURL	https://hostname/maximo/webclient/login/login.jsp
sso_1.idp_1.certAlias	name of the certificate alias you provided in point 9
sso_1.idp_1.entityID	entity ID of the IdP which is provided in the IdP metadata file and will be automatically populated
sso_1.idp_1.singleSignOnUrl	URL endpoint for IdP authentication (automatically populated from metadata)

 

                  

sso_1.sp.filter – this is an optional property. We can filter out servers that can be exempted from using SSO. Usually, we enable SSO only for UI server, and filter out MIF/CRON/REPORT servers.

If you do not see the sso_1.idp_1.certAlias property then a certificate was not provided with the metadata file. We will need to obtain the certificate from the IdP and add it to WebSphere manually by going to Security > SSL certificate and key management > Key stores and certificates > CellDefaultTrustStore > Signer certificates and clicking Add.

 

Once imported, you will need to add a custom property to Trust Association Interceptor TAI   - sso_1.idp_1.certAlias and assign it the value of the new certificate alias you created

11. Finalize Security Domain setup

• Using the left-hand menu select Security and then Security Domains
• Expand User Realm, click the Customize for this domain radio button
• Click Apply at the bottom of the screen and save changes
• Go back to the Security Domain, expand User Realm (it should already be set to Customize) and click Configure... 
• If you are not taken to the Trusted authentication realms - inbound page automatically then click the associated link in the lower right part of the screen (under Related Items)
• Click the Add External Realm... button in the toolbar
• Enter the value of the sso_1.idp_1.entityID from point 10 and click OK
• Click Apply and save changes and return to the security domain configuration screen by following steps 
• Click the Custom Properties link at the bottom of the screen
• Add the following two properties:

Property	Value
com.ibm.websphere.security.DeferTAItoSSO	com.ibm.ws.security.web.saml.ACSTrustAssociationInterceptor
com.ibm.websphere.security.InvokeTAIbeforeSSO	com.ibm.ws.security.web.saml.ACSTrustAssociationInterceptor

• Click OK and save changes

12. Assign security domain to servers or clusters

• Assign server/cluster where WebSphereSamlSP.ear was deployed in point 2
• Proceed to the security domain configuration screen as described in point 11
• Under the heading Assigned Scopes expand the tree starting at Cell
• Locate the server(s) or cluster(s) where you would like to enable SSO and click each one to enable it. If you are not using clusters then your servers will appear under the Nodes section. If your servers are in clusters then you must look under the Clusters section
• Enable all appropriate servers click the OK button at the bottom of the page and save your changes

13. Restart application servers and web server to pick up configuration changes

14. Test SSO using the login URL provided by your IdP

Debugging

• To debug issues with SAML, we need to enable trace logging on the server where the SAML ACS servlet has been installed
• By default, WebSphere provides no feedback in the standard logs for most SSO issues 
• If you are troubleshooting SAML in a cluster where multiple servers are running it’s recommended you stop all but one server to simplify diagnosing your problem
• To enable trace logging for the server where the ACS servlet is installed, login to the WebSphere Admin Console and do the following

• Using the left-hand menu select Servers then Server Types then WebSphere application servers

• Locate the server where you installed the ACS servlet in point 2 of the Step-by-step guide and click it

• Under Troubleshooting on the right side click Diagnostic trace service

• Under Additional Properties click Change Log Detail Levels

• Add the following log levels, separating each with a colon :  com.ibm.ws.security.*=all: com.ibm.wsspi.wssecurity.*=all: com.ibm.ws.wssecurity.saml.*=all:

• Click OK and save your changes
• Restart the server where you have enabled trace logging
• Now test your SSO flow again and view the trace.log file in the log folder of your server for errors
• The messages will generally give some indication of where the problem lies but you may need to find a proper person to escalate to if you cannot determine the problem 

Maximo varcharmultiplier batch file for upgrade or configdb process

What is varcharmultiplier ? 

• varcharmultiplier is Out of the Box Maximo batch file.
• It is used to increase the database column length by Maximo column length (MAXATTRIBUTE.LENGTH) multiplied by the input provided in -m parameter
• This multiplier value will be stored in MAXVARS table for VARNAME = VARCHARMULTIPLE
• It's applicable only for DB2 and Microsoft SQL Server databases because Oracle by default has VARCHAR(2) as the multiplier

Why varcharmultiplier is used ?

• for translation process after upgrade of database, so that import of translated data in form of xliff files are loaded into Maximo without any truncation errors
• to modify any column length of a table which has double-byte (Chinese, Korean, German etc) language description, without "data too long" error in description column during the insert process from XXBASETABLE to converted BASETABLE.
•  for example, if you increase the decimal points of a column in WORKORDER table, Maximo will take backup of existing WORKORDER table into a new table XXWORKORDER , then it will modify the table definition of WORKORDER. Maximo will run the insert statements from XXWORKORDER to revised WORKORDER table. If the description column has any special characters, then we might get an error like "data too long to insert" on description column. varcharmultiplier is used to resolve the error by increasing the database length than Maximo UI column length.  

How to execute ? 

•  This batch file is located in the Maximo Installed path: <WindowsDrive>\IBM\SMP\maximo\tools\maximo\varcharmultiplier.bat (Windows)  or /opt/IBM/SMP/maximo/tools/maximo/varcharmultiplier.sh (linux)
• Stop the Servers
• Navigate to this path and run varcharmultiplier.bat -m<Positive Number>. For example, varcharmultiplier -m2
• Start the Servers
• The log for the execution will be stored in the folder <WindowsDrive>\IBM\SMP\maximo\tools\maximo\log\VarcharMultiplierYYYYMMDDHHmmss.log or <Linux>/opt/IBM/SMP/maximo/tools/maximo/log/VarcharMultiplierYYYYMMDDHHmmss.log
• In case, if you encounter any errors on the database while altering the column length, please check the string_units [SYSTEM or CODEUNITS32] configuration parameter setting.

Courtesy: Madhavan https://www.linkedin.com/in/madhavan-s-872b346/

Reference: https://www.ibm.com/support/pages/tdtoolkit-truncate-error-import-microsoft-sql-server-db2-double-byte-languages

Maximo SQL query to retrieve Active Users of a group or from a specific server

Information about the number of users logging in a specific JVM is required for sizing the infrastructure capability of a JVM. 

One such example is to get the users logging into Maximo from Anywhere solution. Client can decide on the number of the user licenses needed for Mobile Add-on. 

Administrator can come up with JVM size with the number of concurrent users connecting to this server where Maximo Anywhere Users are connecting to. 

This data can be retrieved using a SQL query from a specific JVM. This query will list the successful login of users into a dedicated JVM with their last login date.

SELECT DISTINCT

    b.userid,     b.defsite,     c.displayname,     b.status,

    MAX (trunc(d.attemptdate)) AS lastlogin

FROM

    maximo.groupuser       a,     maximo.maxuser         b,

    maximo.person          c,     maximo.logintracking   d

WHERE

    a.groupname LIKE 'ANYWHERE_TECHNICIAN%'

    AND a.userid = b.userid

    AND b.defsite IN (  'XX01', 'YY01' )

    AND c.personid = b.personid

    AND b.status = 'ACTIVE'

    AND a.userid = d.userid

    AND d.servername = 'MXServer_AW01'

    AND d.attemptresult = 'LOGIN'

GROUP BY

    b.userid,     b.defsite,     c.displayname,     b.status

ORDER BY

    b.defsite,     b.userid

Trigger publish channel without event listener using automation script

The transactions from Maximo is passed to external system using Publish Channel component. 

The records can be initiated to Publish Channel using 2 ways: by an event or export from database.

• Frequently occurring scenario is the record will be triggered by an event, Publish Channel would pick up them and transfer it to external system after processing all rules. 
• There are a few other scenarios where in we need to resend the outbound data by export option without any user event on the object. These configuration will have "Event Listener ? " flag disabled.

Business Cases for Export:

1. The data sent from Maximo didn't reach external system due to internal data error. So, we need to resend the same transaction again to external system

2. Middleware outage or intermittent technical glitch requires resending of same record

For example, re-send Inventory Issue Transactions from Maximo to external system. MATUSETRANS object in Maximo holds the Inventory Issues transactions. 

We need to create a ACTION automation script, Sig Option, Button and Security Access for Resend operation by following this link - https://www.maximoscripting.com/use-a-button-to-launch-an-action-script/

Code to resend inventory issue - iface/invokePublishChannelfromScript.py

References - https://maximodeploy.blogspot.com/2020/05/invokecall-publish-channel.html

MIF Data Loading with non-English characters in Description without invalid characters or ? question mark in UI

When we receive data from clients to load them into Maximo, they might contain description with non-English characters. Non-English description are characters from languages like French, German, Portuguese etc., 

What we generally do is create a csv file for Enterprise Service with the content from file (.xls or .xlsx) gathered from client in Excel Application. If we load them into Maximo External System, we might end up with question marks or invalid characters in description field. 

Non-English description won't belong to ASCII character set. These characters in description would be removed in csv file if you save csv file by standard file format without encoding.

In order to retain the non-English (non-ASCII) characters in Maximo description field, we need to save the csv file with UTF-8 (Unicode Transformation Format - 8 bit) encoding.

Steps to follow for saving the file in UTF-8 format:

• Open the .csv file in plain notepad application
• Click on File -> Save As ; On the window that is opened, Choose Encoding = UTF-8   

Load Maximo Failure codes or Failure class hierarchy using sql scripts

Failure Class is a hierarchy with Problem, Cause and Remedy for a specific category of failure, for example, PUMPS, ECIRCUIT etc.,

Failure Class can be associated with an Asset or Location. It will be copied to a Work Order if it is linked to this Asset or Location. 

Load the Failure Class and Failure Codes
Failure codes are PB01, CA01, RY01 etc.,  failureclass --> PUMPS.

Load the Failure Class and Failure Codes using MXLoader or using DML sql scripts. 

MXLoader:

Failure Codes

Failure Class

SQL:

insert into failurecode (failurecode, description, orgid, failurecodeid, langcode, hasld) 

values ( ' STOPPED' , 'Stopped', 'EAGLENA', failurecodeseq.nextval, 'EN' , 0  ) ; 

-- create failure code to form hierarchy. These codes can be a problem, cause or remedy defined by type field in failurelist table

insert into failurecode (failurecode, description, orgid, failurecodeid, langcode, hasld) 

values ( ' PUMPS' , 'Failure Class for PUMPS', 'EAGLENA', failurecodeseq.nextval, 'EN' , 0  ) ; 

-- create failure class in failurecode table

insert into failurelist (failurelist,failurecode, type, orgid, parent) 

values ( (select failurecodeid from failurecode where failurecode = 'PUMPS' and orgid = 'EAGLENA')  ,'PUMPS', null,'EAGLENA', null);

-- create failure class in failurelist table to display in Failure Code application

PARENT column in the insert DML statement is retrieved using the SELECT subquery. The sql scripts for the entire hierarchy need to be prepared using the CONCAT formula in EXCEL

Create Problem with failure codes  

insert into failurelist (failurelist,failurecode, type, orgid, parent) values (failurelistseq.nextval,'PB01','PROBLEM','EAGLENA', (select failurelist from failurelist where failurecode ='PUMPS' and orgid='EAGLENA'));

PB01 is the problem code ; PUMPS is the failure class under which the Problem-Cause-Remedy hierarchy is going to be formed

Create Cause with failure codes  

insert into failurelist (failurelist,failurecode, type, orgid, parent) values (failurelistseq.nextval,'CA01','CAUSE', 'EAGLENA', (select failurelist from failurelist where type = 'PROBLEM' and failurecode ='PB01' and orgid='EAGLENA'));

PB01 is the problem code already created in step 2 ; CA01 is the cause under PB01 ; PB01 --> CA01

Create Remedy with failure codes 

insert into failurelist (failurelist, failurecode,type, orgid, parent) values (failurelistseq.nextval,'RY01','REMEDY','EAGLENA', (select failurelist from failurelist where failurecode ='CA01' and type = 'CAUSE' and orgid = 'EAGLENA' and parent = (select failurelist from failurelist where orgid = ' EAGLENA' and type = 'PROBLEM' and failurecode = 'PB01'))); 

PB01 is the problem code already created in step 2  ; CA01 is the cause already created in step 3

RY01 is remedy created for PB01 -> CA01 -> RY01

To form the insert scripts, we would use excel =CONCAT formula in the worksheet.

Use sql to verify the data loaded into Maximo

Pros:

Failure Class hierarchy can be loaded using other methods such as MIF enterprise service or MXLoader. 

MXLoader will be slow if the number of records are in thousands. 

If loading error happens at any point - the re-loading starts from the first row and debugging the load failure is difficult.

Conventional method using MIF Object Structure and Enterprise service is faster.

But, we need to prepare the data for parent field with failurelist ID from the database. So, it is time consuming.

If we have a repetitive data load of failure hierarchy for different Organizations within a same company, then creation of failure hierarchy using sql scripts would be easier as it needs only a change of ORGID for loading them.

Cons:

 

We will be bypassing the Maximo Data validation. for example, we can load a problem type of failurelist without an actual failurecode available.

Reference:

associating-failure-classes-records

bportaluri-load-failure-codes-in-maximo

Maximo Yes/No/Cancel Exception using Automation Script on Work Order Status Change

Many clients want to avoid customization and use only Automation Scripts for implementing business logic; One of the frequent request is to throw warning on Work Order status change.

The best triggering event is to have an attribute launch point on object:attribute WOCHANGESTATUS:STATUS to throw error or warning. 

code automationscripts/attributelaunchpoint/wochangestatusscript.py

Create the below items for the script to run in the environment

1. Relationship name - C_OPENINVUSE ; Parent - WPMATERIAL ; Child - INVUSELINE

(refwo=:wonum or refwo in (select wonum from workorder where parent=:wonum and siteid=:siteid)) and itemnum = :itemnum and itemsetid = :itemsetid and siteid = :siteid and invusenum in (select invusenum from invuse where siteid = :siteid and status in ('ENTERED', 'STAGED'))

2. Maximo Messages 

i) Message Key: AVOIDRECORD  Message Group: WPINVUSE 

Display Method: MSGBOX  Error

Value: Work Order Plan Materials is used in open Inventory Usage {0}

Buttons: OK ? 

ii) Message Key: CANAPPROVEWO  Message Group: WORKORDER 

Display Method: MSGBOX  Information

Value: Planned materials are not yet issued to WO from Inventory Usage {0}. Completing the work order will block the Inventory issue and retain the materials in storeroom. Would you like to proceed?

Buttons: Yes ?  No ? 

Testing:

When you try to change the status of WO to COMP and the WO has open Inventory Usage record in statuses - ENTERED and STAGED, you will get the Yes/No pop-up window.

This example doesn't include Cancel button - you can configure it in message (ii) and add a method def cancel() in the code.

References: https://www.bpdzenith.com/prompting-a-yesno-dialog-from-an-automation-script/

Maximo Asset Hierarchy BIRT Report

Asset Hierarchy is used to define the physical or functional parent-child relationship of assets created within the Location application. 

The real advantage of developing such location/asset hierarchy is to easily locate an asset to perform work orders, grouping of assets under a location to represent plant systems, etc.,

OOB Maximo has Location Hierarchy by system report, but it doesn't have similar report to display the Asset Hierarchy. 

A report for asset hierarchy using ASSET and ASSETANCESTOR tables is available in the link asset_hierarchy.rptdesign

The flowchart describes the nested table structure used to design the hierarchy report.

                                               

Configure the dataSet with a Parameter to accept the value from the parent table design

Parent parameter should be linked to the assetnum of parent table

Always use - Auto Layout - Fixed Layout would cause the results go in separate pages.

Report Parameters need to be filled with attribute (siteid,location,assetnum) and lookup values (site,locations,asset) to let the user choose values from the lookup.

 

Report Output:

2) We can also build an Asset Hierarchy Data structure using a SQL query 

SELECT

    level,  assetnum,  parent,  lpad (' ', level * 4, ' ') || assetnum,

    description,  location,  siteid

FROM asset

WHERE  siteid = 'BEDFORD'

START WITH siteid = 'BEDFORD'  AND parent IS NULL AND children = 1 

CONNECT BY PRIOR assetnum = parent

References:

maximo-asset-hierarchy-sql

ibmtechexchange_community_post

designfile_asset_hierarchy.rptdesign

importance-of-asset-location-hierarchies/

Forecast reports using Cross Tab feature in BIRT Maximo

Why Forecast is needed ? 

For planning of work or budgeting for long term operations, we need to forecast the upcoming work along with costs/labors/services/materials/tools associated with them.

What is Cross tab ? 

A Crosstab (or Cross Tabulation) is a table showing the relationship between two or more variables for quantitative analysis by showing the correlation change from one group of variables to another. It allows for the identification of patterns, trends and probabilities within data sets. Cross Tabulation is used across various industries, job roles and analysts. It benefits many people for forecasting cost or material requirement for the company.

Sample Requirement
I would like to create steps to develop a crosstab table in Maximo BIRT reporting functionality with a use case.; PM (Preventive Maintenance) Cost Forecast Reporting using Cross Tab feature.

Development steps
Create a report with predefined template – “Tivoli Maximo List Template”

Remove the “Detail Row” section from the template. Cross tab inserted into Detail section would result in duplicated rows, so it needs to be created in Header section.

 

Merge the cells in the Table header and insert the “Cross Tab” element into the table header

Construct the Data Set with all required output columns – this report shows all OOB fields to display the PM Cost forecast report with required Work Orders per month

Create a new Data Cube 

Associate the Data Set for Data Cube. Add Groups (Dimensions) for columns that we want to display in rows section and for column header (month in this case) of Cross Tab Section.

Summary field is the one which is at the right-hand side of Cross Tab element design.

Drag and drop the fields from Dimensions to rows section of the Cross Tab element.

Place the MONTH field from Dimension to column header section of cross tab

Place the Summarize field in the column field section. 

The design file is available in gitlab - https://gitlab.com/bysurendar/maximo/-/blob/master/reports/pmcostforecast.rptdesign

Output of the report:

Rows are the list of Preventive Maintenance records which have forecast generated for the given date range

Columns are months for which the forecast exists. 

Summarized values are the Work Orders that would be needed for a month to perform the Work.

Courtesy: Vijayabanu Pitachi 

References: 

https://www.ibm.com/docs/en/elo-mc/7.6.0?topic=tab-tutorial-creating-cross

https://www.youtube.com/watch?v=LKuCNuz67YA