Improving performance of Incident.rpt and other reports on an Oracle database

Version 1

    Reports that are launched via the Print button in Console or the View Report link in ServicePortal are passed a variable containg the respective object's (Incident, Problem, Note etc) unique identifier (Guid).  This Guid is then used by the report to look up the relavent data from the database.

     

    With the database column data types used in the Service Desk database there has been a problem with passing the Guid when using Oracle.  To get around this a formula was used in all the standard crystal reports to cope with this however this has caused a major performance issue on sites with large databases.  The reports load very slowly and download a very large amount of data from the database before the report is displayed.

     

    Solution

     

    On all your reports that are launched via a Print button (normally Incident.rpt, Change.rpt etc) take the following steps if you are experiencing slow loading:

     

    1. In Crystal Reports via the Field Explorer create a new SQL Expression field, name it Guid Expression.
    2. The SQL Expression Editor will launch, type in: CAST(“PM_PROCESS”.”PM_GUID” as VARCHAR(32))
    3. Press Save and close the editor.
    4. In the Select Expert (from the Report menu) delete the existing selects and create a new one based on the SQL Expression field is equal to {?Guid}.
    5. The SQL query will now use the SQL expression as the WHERE clause and the report should load much faster than before.

     

    Note that the table and column name used in step 2 should match the existing table and column selected in the Select Expert (removed in step 4).  The ones used in this example will work for any report based on any process object.  These steps also assume the parameter field the Guid is passed to is still called {?Guid} as it is in the standard reports.

     

    Environment

     

    This will work with Service Desk 7.2.6.  In some easlier versions a more complex formula was required to manipulate the format of the Guid from SQL format to Oracle format so while this fix will work on earleir versions you may require further advice specific for the version in use.