Optimizing Remedy Web (aka BIRT) reports in Remedy

Version 3
    Share This:

    Introduction


    We recently had the challenge of integrating a collection of charts, graphs and other report features (known as the Heads Up Display aka HUD) from another Remedy instance into our Remedy instance while importing their forms, workflow and data (consolidating their instance into ours).

     

    This HUD consisted of 40 or so separate web pages or screens implemented as ASP code using MS SQL stored procedures accessing the MS SQL database directly (which would not work on our Oracle-based system). It was decided that a member of the other Remedy team would convert the HUD to Remedy-based Flashboards.

     

    Our team focused on loading the forms, workflow  (renamed by the other Remedy team) to avoid name collisions) and data and resolving those issues.

     

    The week before the go-live date, we were supplied with the converted HUD forms and workflow.

     

    These had been done as Remedy Web-style reports using the BIRT (Business Intelligence and Reporting Tools) development tool, set up according to BMC’s white paper “Using a BIRT Editor to Create or Modify Web Reports”.

     

    On our first tests, we found that the performance of these reports were... less than optimal. Some took several minutes to display results with one taking more than 15 minutes, on our Development and Testing environments.

     

    As we had not anticipated a need to tune or optimize the BIRT reports, we had not set up a BIRT instance in our local environment, so initially we were only able to offer general tips (learned from developing Crystal Reports) for tuning these reports:

     

    • Use a qualifier to restrict the number of entries or records returned by the application server…
    • Move any other processing to the database (in Crystal Reports, enabling the  “Group By on Server” option often helped performance tremendously).


    We were told by the remote developer that he had been unable to do this... that in a typical Remedy report the qualification could be supplied at runtime by workflow, but as each page or screen other than the first was not a Remedy form, and each graph, chart or other element needed different qualifiers (in BIRT  terminology, used a different Data Set) this was impractical.

    For these reasons, typical data sets used by the HUD were prompting the user for a date range or start date, possibly an application, then retrieving ALL the records for ANY of the applications that might be displayed in the report element used for that data set... (this could be a very large number of rows in the Production system). After retrieving these records, processing of the records (at the mid-tier server)  for Computed Columns would be done as well as implementing the Data Set filter - which excluded the records outside the date range/start date and those not pertaining to the application selected (unless the “All Applications” selection had been made).

     

    We set up a BIRT instance on our Development server and began evaluating these reports individually.

     

    After some time and challenges, we found that the user parameters COULD be passed to the Data Set Query Qualifier and the Data Set Filters, for the most part, removed. After a lot of work by the other team’s developer, these reports now ran much better: many in under 5 seconds, the worst now completed in just over 60 seconds, without any errors.

     

    Optimizing

     

    Remove any unused fields from the Data Set  - Output Columns:

    (if they aren’t needed/used later in the Chart/Report, remove them as shown below. It was not immediately obvious to me, but this is done by  removing them from the list of fields shown in the  Data Set – Query – Qualification tab… even if they are used in the Qualification box below, they probably don’t need to be in the list of fields shown above, as these are ALL returned by the SQL Query.)

     


    Reduce the number of rows returned by each query by moving processing from the Data Set Filters (which do processing on the entire result set (data returned by the query) after the query) to the Query Qualification: date range, etc.

    Corollary – reduce the amount of work done in the Filter for each Data Set as much as possible:


    The Filter below  could be moved to the Query Qualification:

    ('Create Date' >= [param:dsBeginDate] AND 'Create Date' < [param:dsEndDate]) AND 'Closeout Problem Class' != $NULL$ AND ...

     


    Naturally, there turned out to be some challenges making this work:


    SYNTAX:


    BMC’s whitepaper “Using a BIRT Editor to Create or Modify Web Reports” show this in passing, but does not explain some nuances I’ll go into below:

     

    There is plenty of BIRT documentation on how to reference a Report Parameter from within a Data Set Query Qualification:

     

    • (Example: SELECT column1, column2 FROM table WHERE field1 = ? or field2 > ? )

     

    But this doesn’t work in our BIRT reports created for Remedy (AR Web reports) because the Data Source (BMC Remedy ODBC driver) uses Remedy Search qualifier syntax :

     

    • (Example:  'Assigned To' = "Hist" AND ('State' = "Open"…  )

     

    And the ? for a parameter placeholder causes a syntax error.

    The correct syntax for the parameter would be:

     

    • (Example:  'Create Date' >= [param:dsBeginDate] AND … )


    LINKING TO REPORT PARAMETERS:


    Parameters can be set for the overall report, and for each sub-report or chart.

    As these prompt the user to enter or select data and we don’t want multiple charts/Data Sets prompting the user multiple times, the Data Set parameters should be linked to the Report Parameters (eg, reuse or share the Report Parameters with each sub-report or chart).

    (as the examples I’ve seen show local data set parameter names starting with lowercase “d” & “s” and no spaces, I’ll follow that convention), using these names for now:

     

    dsBeginDate

    dsEndDate

     

    This  linking is done at each Data Set by selecting Edit Data Set, selecting the Parameters setting and creating a new entry:

    Example :

     

    Name: dsBeginDate

    Data Type: Date

    Direction: Input

    Linked to Report Parameter : Begin Date


    ORDER MATTERS IN THE DATA SET PARAMETER DEFINTION:


    In regular BIRT stuff, Parameters are represented with single "?" (question marks) in the Data Set (SQL) query... and replaced one-for-one with any parameters defined for that Data Set in the order they are listed (even if they are linked to an overall Report Parameter):

     

    DataSet Parameters:

     

        dsBeginDate

        dsEndDate

     

    will replace the question marks in this sequence in the order that they are found:

    (paraphrasing):

     

    Select * from Ticket where 'Create Date' >= ? AND 'Create Date' < ?


    In BIRT reports using the BMC Remedy ODBC driver, I found that Order matters EVEN THOUGH YOU USE (local Data Set) names for the DataSet Parameters:


    As shown above, the FIRST use of a parameter is filled in with the one shown FIRST in the Parameters defined for the local Data Set (they can be re-ordered after you create them). In the above example, the report will fail in the View tab, and the unhelpful error, if expanded, will have “Invalid Data Type for Operation” (or something similar) buried in the massive java error text. This is the only clue that a String (the Application parameter) is being supplied to that first parameter reference in the Query Qualification – in other words, the name after “[param:” is ignored, just its position used.

     

    The Fix – reorder the Parameters so the one you reference first in your query is displayed first… etc. Subsequent parameters can be referenced in the query, in the order they are displayed, and you can use each parameter multiple times in the query – but I’m not sure if you can use them out of order after the first use.

    In other words, I’ve verified that a query using something like:

     

    param1 … param2… param2….param3 …param3…param3…

     

    works, but I haven’t verified that this would work:

     

    param1 … param2… param3….param2 …param1…param3…

     

    REPORT FAILS AFTER ADDING PARAMETER TO QUERY QUALIFICATION


    Due to a known defect in BIRT 2.5.1, after editing the Query /Qualification for a Data Set, the report will FAIL (in Preview) as will the Data Source – Preview Results function.

    This is  due to the computed columns in the Data Set being renamed with an incrementing extension consisting of an underscore and 1 or 2 (increasing) digits – eg “_14”, “_15”. References elsewhere now fail with an “Invalid field name” as these no longer match. The fix is to  MANULLY edit the XML in the XML Source tab for the report.

     

    • Update the Query, verify its syntax with the Verify button.
    • DO NOT Select the Computed Columns or Output Fields entries – this is the part that misnamed the computed fields…! Preview  should now  work.


    If the report fails to run or the Data Set – Preview no longer works, verify the  Computed Column names haven’t had this extension added. If they have, edit them using the BIRT editor XML Source tab, or an external text editor.

     

    • Save the report first, or the unwanted extensions won’t be visible in the XML tab… use a find/replace regular expression such as “_\d\d” or “_\d” to find all instances and replace them with (nothing)…

     

     

    Credit (John deserves the credit as I just branched the discussion):

     

    This document was generated from the following discussion: Optimizing Remedy Web (aka BIRT) reports in Remedy authored by John Palmer