Dynamic Report Grouping With Oracle BI Publisher

Edgewater Ranzal is working with a client to convert XML Publisher and nVision reports to BI Publisher as part of a larger initiative to consolidate reporting under the Oracle Business Intelligence solution.  The client currently does not use the BI Publisher Layout Editor, but rather relies on RTF templates to display results to the user. One nVision report in particular presented a challenge because of the grouping requirements.

The report requires users to enter an Account range, and the resulting tables need to be grouped by individual accounts. There are also seven optional wildcard prompts, including Product and Department.  If the optional prompts are left blank, the results need only to be grouped by the individual accounts; however, if values are entered for the optional prompts, the subsequent results need to be grouped by all resulting unique combinations for the prompts entered. For instance, if the user enters 10000 to 20000 for the account range and all departments beginning with 10, an example report may be:

BI publisher 1

Sample Data Only

If the user adds the wildcard criteria (%) for the optional prompt Product, the results are further grouped by Product as such:BI publisher 4

Sample Data Only

One approach to meet the client’s needs for this particular nVision report is to use the grouping feature in the Microsoft Word BI Publisher Table Wizard. To fulfill the requirement of grouping only when users enter values in the optional prompts, the XLST statements that create groupings can be wrapped in IF statements that evaluate the prompts for null entries. For example, Department and Product can be evaluated as such (note that all prompts begin with PRM):

BI publisher 2

This approach has two drawbacks: First, after adding the additional evaluations for the other five optional prompts, the XLST within the RTF template becomes quite convoluted. The second, and more limiting factor, is that the IF statements to end the “group by” statements (below the table template) result in either a non-functioning report or incorrect groupings.

 A second and more viable option is to use the Data Model to leverage Oracle SQL which has more robust logic evaluation capabilities than XLST. To meet the client’s needs, an additional column was added to evaluate user prompt values (note that all prompt variables begin with PRM, and CHR(13) returns a carriage return):


This column evaluates each optional prompt for a value and, if the user has made an entry, concatenates the common name (i.e. Department and Product) with the value of the respective row and a carriage return; otherwise, a null value is returned. The Microsoft Word BI Publisher Table Wizard feature is then used to generate the XLST needed to group the table by the new column GRPOPT in a more succinct fashion:

BI publisher 3By leveraging the Data Model SQL rather than XLST within the RTF template, Ranzal recreated this nVision report while maintaining the sort of dynamic capabilities normally seen in Oracle Business Intelligence Answers. The additional SQL statements add no overhead to the BI Publisher report, and the report runs as fast as or faster than, the corresponding nVision report. The delivery of this report opens up new reporting possibilities for the client, and it reinforces Ranzal’s expertise in the Oracle Business Intelligence tool set.