Stored Procedures and Maximo Reporting
BrianG. 2700057EC0 Comments (4) Visits (12270)
Stored procedures are either a loved or hated feature of enterprise database technologies on the market today. They are usually loved by Database Administrators and hated by everyone else. Creating even a simple stored procedure can be a daunting task to an experienced database analyst. Yet, chances are if you are developing custom reports in Maximo, you may have access to a wealth of stored procedures and never realized you could use them to drive your business intelligence outputs until now!
For the purposes of this write-up, the focus will be on the scripted data source which is used universally throughout all out-of-the-box Maximo reports.
In the example below, you will find two examples of a simple stored procedure developed for a DB2 database. NOTE: This is for DB2 Only. The specific details for SQL Server and Oracle are not covered.
When called (executed), both will receive two input parameters (organization and site). However, the first example will return back values stored in output parameters and the second example will return back a full result set. Only Example 2 will work with the scripted data source.
In the scripted data source, the ability to retrieve and interpret output parameters is not available. This is why Example 1 would not work. By returning back a full data set in Example 2, the data source is able to interpret the tables, columns and data returned and successfully display the data on the report.
After the stored procedure is created on the database, rights must be granted to database users in order to use it.
GRANT EXECUTE ON PROCEDURE MAXI
Finally, the scripted data source needs to be developed to execute the stored procedure.
Whether you love or hate stored procedures, they are a powerful tool available in today’s enterprise database technologies. Since all of the work is done on the database server, if used correctly, they can significantly reduce the runtime of a report with considerable overhead. For this reason, stored procedures can be considered when developing your Maximo BIRT reports.