• Adi Jaradat

Connect Maximo and Google Charts

Updated: Feb 25

Maximo and google Charts


Graphs and charts add significant advantage to any application. They can instantly provide information to the user and, if designed correctly, even help in meeting obligations and deadlines. Visually presenting data within an app helps users who are less versed in numerical analysis understand and comprehend information fully.


Out-of-the-box Charts and Graphs


Maximo ships with some charting and graphing capabilities with special attention to Start Center and few other applications. Additional means to present data can be obtained by using reporting and BI tools. However, there are a few disadvantages to the current offering:

  1. Capable charts are focused to Start Center-like applications; which puts typical applications in disadvantage.

  2. Using reporting tools requires the user to leave the page context which is not ideal if we are targeting seamless in-page presentation of data

In-page Charts


In an ideal situation, the user must be able to, within the context of a single page, to not only get a full information but also visual indicators to help him take a decision. Depending on the objective of the application, a set of decisions are expected to be made by the user and consequently appropriate charts can help him reach there faster.


Google Charts


There is no specific reason for using Google Charts in this post, however, Google Charts are quite capable and diverse library. In short, Google Charts is an interactive Web Service to create graphical charts from user-supplier data. Using the fairly simple and well organized JavaScript library, a whole a lot of ready-to-present charts can be produced.


Here are few examples from Google Charts web site. The complete list of charts can be viewed in Google Charts website.

Maximo Google Charts Examples

Maximo Google Charts Examples

Google Charts Data Tables


I won’t spend a lot of time on Google Charts, but I will quickly go over Data Tables as I will use them later on. Data Tables are two-dimensional, mutable table of values. It can be populated directly in the web page or from database or any data provider (must support Chart Tools Data source protocol).

An instance of Data Tables can be instantiated using:

var data = new google.visualization.DataTable(); 

Rows can be added using “addRows” method.


Connecting Maximo and Google Charts


The basic idea is illustrated in the following algorithm:

  1. Build a simple web page representing the chart to be displayed, let’s call it Charting Page.

  2. Allow Charting Page to accept data as parameters

  3. Use Generic iFrame to embed the Charting Page into an application

  4. Develop logic to aggregate the data from the application MBO and serialize it

Charting Page


Our target will be to build Timeline Charting Page. A compressed chart to display the change in record status and in this specific example we will use Work Order. The following code was taken from Google Charts Timelines and modified slightly:

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
 <script type="text/javascript">
  
 function getAllUrlParams(url) {}
  
   google.charts.load("current", {packages:["timeline"]});
   google.charts.setOnLoadCallback(drawChart);
   function drawChart()
   {
     var json = JSON.parse(getAllUrlParams().data);
     var container = document.getElementById('example3.1');
     var chart = new google.visualization.Timeline(container);
     var dataTable = new google.visualization.DataTable();
     dataTable.addColumn({ type: 'string', id: 'WONUM' });
     dataTable.addColumn({ type: 'string', id: 'STATUS' });
     dataTable.addColumn({ type: 'date', id: 'Start' });
     dataTable.addColumn({ type: 'date', id: 'End' });
  
     for(var i = 0; i < json.length; i++)
     {
       dataTable.addRows([[ json[i].WONUM, json[i].STATUS, new Date(json[i].STARTDATE), new Date(json[i].ENDDATE) ]]);
     }
  
     var options = {
        height:100
       ,enableInteractivity: false
       ,tooltip: {isHtml: false,trigger:'none'}
 }
  
     chart.draw(dataTable,options);
   }
 </script>
  
 <div id="example3.1" style="display:block;height: 100px;"></div>  

This page expects a parameter named “data” and its value is JSON array. The code will pick up this parameter and parse it into JSON object and fill in the rows.


Aggregate MBO Data


As our Timeline Page is ready to accept JSON data, we need to find a way to provide this information within the following rules:

  1. The data must be encapsulated within the MBO framework.

  2. Whether the data is representing a single attribute, multiple attributes, or multiple rows, the output must by a single line.

  3. The configuration must be minimal and, preferable, no Java Classes to be developed.

  4. Configurations must be easily editable without the need to configDB or Restart.

  5. The setup must be easy to replicate and add more charts.

In the Work Order Status case, we know that each Work Order will have many lines representing it’s status history. In order to comply with Google Charts Timeline requirements, data must be present in the following format:

In comparison, WOSTATUS looks like this:

We can map ATTR 1 to WONUM, ATTR 2 to STATUS, and START to CHANGEDATE. For END we will advise the following logic:

  1. If the status has a following status, then we will use the CHANGEDATE of the next status as the END

  2. If the status doesn’t have a next status, for example, CLOSE or CANCEL, then we will just use the same CHANGEDATE + 1 day (in order to be displayed).

 SELECT WOS.WONUM
        ,WOS.STATUS
        ,TO_CHAR(WOS.CHANGEDATE,'DD-MON-YYYY') STARTDATE
        ,NVL
        (
             FIRST_VALUE (TO_CHAR(WOS.CHANGEDATE,'DD-MON-YYYY')) 
             OVER 
             (PARTITION BY WOS.WONUM ORDER BY WOS.CHANGEDATE ROWS BETWEEN 1 FOLLOWING  AND 1 FOLLOWING )
        ,TO_CHAR(WOS.CHANGEDATE+1,'DD-MON-YYYY')
        ) ENDDATE
   FROM WOSTATUS WOS
  WHERE 1=1
    AND WOS.WONUM='XXXXXX'
  ORDER BY WOS.CHANGEDATE ASC  

Learn more about sliding Window boundaries: https://docs.oracle.com/database/121/SQLRF/functions004.htm#SQLRF06174


As we managed to get all the data we need, the next step is to format it into JSON. This part depends on the database, but if you are on Oracle 12c you can use JSON_ARRAY.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-6C3441E8-4F02-4E95-969C-BBCA6BDBBD9A


Otherwise, you will find it easier to just concatenate the output to generate JSON data.


The Select status with sliding Window boundaries in JSON format will be used to build MAXRELATIONSHIP with DUMMY_TABLE to return exactly a single record containing the JSON data.

  [
   {
     "WONUM": "1003335",
     "STATUS": "WAPPR",
     "STARTDATE": "30-JAN-2007",
     "ENDDATE": "31-JAN-2007"
   },
   {
     "WONUM": "1003335",
     "STATUS": "APPR",
     "STARTDATE": "31-JAN-2007",
     "ENDDATE": "02-FEB-2007"
   },
   {
     "WONUM": "1003335",
     "STATUS": "WPCOND",
     "STARTDATE": "02-FEB-2007",
     "ENDDATE": "05-FEB-2007"
   },
   {
     "WONUM": "1003335",
     "STATUS": "WMATL",
     "STARTDATE": "05-FEB-2007",
     "ENDDATE": "05-FEB-2007"
   },
   {
     "WONUM": "1003335",
     "STATUS": "INPRG",
     "STARTDATE": "05-FEB-2007",
     "ENDDATE": "07-FEB-2007"
   },
   {
     "WONUM": "1003335",
     "STATUS": "COMP",
     "STARTDATE": "07-FEB-2007",
     "ENDDATE": "07-FEB-2007"
   },
   {
     "WONUM": "1003335",
     "STATUS": "CLOSE",
     "STARTDATE": "07-FEB-2007",
     "ENDDATE": "08-FEB-2007"
   }
 ]  


Configuring Generic iFrame


Now it’s time use the Application designer and drag a “Generic iFrame” component and add it somewhere in the application. This is how it should look like in XML:

<genericiframe id="1578817260721" datasrc="../customCharts/StatusTimeLine.jsp?data=$:JSON_STATUS.DUMMY_CLOB$" scroll="no" height="90px" width="100%"/>  


The Final Result

Maximo Status Google Charts Timeline

Maximo Status Google Charts Timeline

Maximo Google Charts Timeline

Maximo Google Charts Timeline

There are many other ways to accomplish the same results, you can experiment with:

  1. Using other charting libraries

  2. Building a custom Charting component

  3. Building a servlet

But keep in mind that charts are always changeable, they are never fixed as goals and objectives change. The implementation must always be loosely coupled and maintainable.


For any question, please drop me an email.


Links

https://adijaradat.com/post/creating-maximo-generic-iframe-custom-component/

https://developers.google.com/chart/interactive/docs/gallery/timeline

https://docs.oracle.com/database/121/SQLRF/functions004.htm#SQLRF06174

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-6C3441E8-4F02-4E95-969C-BBCA6BDBBD9A

44 views0 comments

Recent Posts

See All