Stored Procedures and Maximo Reporting
BrianG. 2700057EC0 Comments (4) Visits (11594)
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.