BiLog: What Wood you do? Maximo 76 Ad Hoc Report Expression Library

Body

What wood you do?  Faced with the task of moving and stacking two cords of wood -  weighing over 4 tons - wood you 
    (1) Book a flight to Honolulu?
    (2)Take a nap?
    (3) Grab a wheelbarrow, a few Ibuprofen and get to work?

What wood you do if you wanted to create an ad hoc report in Maximo 76  - but couldn't get a value like 'Number of Days Overdue' to display?  Wood you

    (1) Knock your head against your computer keyboard?
    (2) Go for a sandwich? 
    (3) Use the Expression Library to add sql functions?

And the right answer is...Use the Expression Library!  

Introduced in Maximo 7.6, the expression library is a listing of sql functions that the user can add to his expression (or calculation) on the content tab of the Ad Hoc Reporting Dialog.  The expression library is maintained by the report administrator, and is designed to insure that users do not inadvertently create calculations which can harm or update the database.  

Five database agnostic examples are delivered in the expression library as shown below.   You may want to supplement these with additional sql functions specific to your database type and business needs.  

Getting back to our example where the Ad Hoc report needs to display the field 'Number of Days Work Orders (Or POs Or Tickets) are overdue - you would add a new sql function to subtract an Attribute's Date Time field from the current date.  These functions will vary by database type.   

The sql function and resulting data type for each of the three database types that are supported are listed here, where <field> would be the Maximo corresponding attribute value like SCHEDFINISH. 

Sql Server
*Expression:  datediff(day, &DATETIME&,<field>)
Data Type:   Integer

Oracle
Expression:  &DATETIME& - <field>
Data Type:   Integer

DB2
*Expression:  days (&DATETIME&) - days (date((<field>)))
Data Type:   Decimal

*To minimize the data type errors that a power user may receive when creating complex expressions, add details to the description section to include what expression data type value is expected

                       The description could be something like this:
                        Description:  Subtracts a Date Time field from the current date, and returns the value as a __________(Decimal or Integer depending on database type)
    

Once this sql function is added to the expression library, the user can utilize it to create calculations (or new fields) for their Ad Hoc report.

A video recording of this process is available, along with other Maximo 76 BI Videos at this playlist.  Additionally, Maximo 76 Ad Hoc Reporting and other BI Feature guides are available here