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):

BI-publisher-5

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.

This entry was posted in BI Reporting and tagged , , , by jlhodson. Bookmark the permalink.

About jlhodson

Jason L. Hodson is a Principal Architect with Edgewater Ranzal. He focuses on the Oracle Business Intelligence platform, with particular emphasis on the federation of EPM and relational data source, Business Intelligence Cloud Service (BICS), as well as data governance with Hyperion DRM. He has experience with clients in the insurance, public utilities, manufacturing distribution, and healthcare industries. A former U.S. Marine, Jason has an undergraduate degree in mathematics/physics from Ball State University, an MBA and MS-Information Systems from the University of Cincinnati, and a MS-Information and Knowledge Strategy from Columbia University. He currently resides in Denver, CO and enjoys hiking, snowshoeing, and the local craft beer industry.

4 thoughts on “Dynamic Report Grouping With Oracle BI Publisher

  1. Pingback: Default and User Friendly Prompting With BI Publisher | Edgewater Ranzal Weblog

  2. Hi Milos:

    If you are using RTF templates, you can use the row spanning function:

    [#]

    Where [#] is the number of rows you want to span. The trick is determining what that number should be as I’m sure your data has a variable number of rows you want to merge. For that, you’ll want to embed a COUNT function in your data model SQL (assuming you’re using SQL).

    If you’re using Excel templates, there is no delivered functionality to merge those cells. Excel macros may help bridge that gap, but that places another step in report generation that may not be welcome. The other solution is to download the RTF template as Excel.

    I hope that helps.

    Jason L. Hodson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s