We recently received a BIRT reporting request from one of our clients after they have pushed the limits of their skill and time to deliver a report to their end-users. We realized that they were trying to create a report within Maximo that took advantage of the flexibility of the Cross Tab element, to allow for dynamic rows and columns to be defined. We were excited to assist them in completing this request and so we jumped right in!
For a bit of background and for those that are not familiar with BIRT terminology, a Cross Tab element is similar in Excel to a Pivot Table, allowing you to analyze and correlate data across multiple variables. This sort of analysis inside Maximo is very useful whenever you are looking to more efficiently review larger amounts of data without having to manually manipulate within another tool or utility, after extracting out of Maximo.
Within BIRT, you can create a Data Cube, which would normally be based off of one of your Data Sets (which represents the SQL Query), that will allow for the creation of the Groups (also known as Dimensions) and the Summary Fields (also known as Measures), which we would want to use in our Cross Tab element on the report. The Groups will be used to define the rows of data in the report, as well as the groupings that are calculated per row. The Summary Fields will be used to define the actual calculations that we are trying to analyze.
For the purposes of this walk-through, let us pretend that we want to provide a Cross Tab report that could be used as an Aging report of our Work Orders across all our sites. After discussion and agreement from our customer, we have decided that we need a simple Work Order summary report, grouped by site and status, with various buckets to represent the elapsed time since a Work Order was reported. Think of the buckets as something like: “Within the last week”, “Within the last month”, “within the last 90 days”, for examples. With this information in mind, we begin with the actual development steps required.
Step 1 – Develop Query
In this example, we start by defining the query to retrieve our work orders, specifically excluding any Tasks or Work Orders in any of the Statuses that we do not want to include in our report. You can see that we select the key fields to be used in our Groups (Status and Site), as well as the fields required to provide our Summary Fields (Report Date and Work Order #). We aliased the fields so they could be changed in the future, if required.
Step 2 – Add Fields to Data Set
The Data Set creation is shown below and it should seem obvious based on the query above, with the exception of the open_age and open_age_sort fields. These fields are required to manage the different buckets. The actual text of the buckets will be stored in the open_age column, while the open_age_sort column will be used to make sure they buckets show up in the right order on the Cross Tab element of the report.
Step 3 – Create Helper Function
Due to the fact that we are not wanting to display the Work Orders by their Reported Date, and instead want to display them as Buckets, we created a small Helper Function that we will use to make sure each row has the appropriate Bucket, based on the Reported Date from the query.
On the initialize method of the report itself, we created the following function which can be easily configured for any other buckets or groupings. We will see that this function is being called from the Open method of our Data Set, but the simple configuration that can be made here is in the groupings variable. The first digit you see (7), represents the number of days elapsed from today to the Reported Date, and if it’s less than or equal to that, the text “Last Week” and “1” are returned from the function. If the difference in days is greater than 30 but less than 60, then “Last 60 Days” and “4” are returned from the function. The “1” and “4” in this example are used to determine the sort order, otherwise BIRT tries to alphabetically sort the Groups, which doesn’t make sense in our scenario.
This groupings variable could be configured for other buckets or values, or this function could easily be adjusted to handle future dates (i.e. If you were looking at Scheduled Start Dates, or Scheduled Finish Dates).
Step 4 – Complete Fetch Method
As you can see from the fetch method for the Data Set, it contains the expected calls, with the exception of the usage of our Helper Function, but you can see how it is in use. We pass the Reported Date to the function, and it provides back the grouping and grouping sort. We then set these two values on our Data Set, to be used in the Data Cube.
Step 5 – Build Data Cube
In order to build the Data Cube, since this report only has one Data Set, it was defaulted on my Data Cube and we moved onto the Groups and Summaries menu, as you can see, we created two Groups:
- Site – To be used for the Rows of data for the Sites and Status
- Age – To be used for the Columns of data for the Buckets by Age (and the sorting)
For the Summary Fields, we just created a count of the Work Orders, as shown below.
Step 6 – Add Cross Tab to Report (and some formatting)
To finish this report up, we just had to add the Cross Tab element associated with our Data Cube to the report, as well as add some optional formatting and header information.
In this example, you would want to add the Site Group (from our Data Cube) to the Row definition section of the Cross Tab, and then you would want to add the Age Group (from our Data Cube) to the Column definition section of the Cross Tab. Lastly, you would add the Work Order (from our Data Cube) to the Summarized definition of the Cross Tab.
We then cleaned up the headings, adding a label for the “Work Order Count”, some headers and deployed the report into Report Administration for use with the Work Order application.
To download the example above, you will need BIRT installed on your computer. The file is a BIRT Report source, so if you do not have BIRT installed, you will not be able to open our example.
