Alithya worked 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 did not use the BI Publisher Layout Editor, but rather relied 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:
Sample Data Only
If the user adds the wildcard criteria (%) for the optional prompt Product, the results are further grouped by Product as such:
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):
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:
By leveraging the Data Model SQL rather than XLST within the RTF template, Alithya 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 Alithya's expertise in the Oracle Business Intelligence tool set.
For comments, questions, or suggestions for future topics, please reach out to us at infosolutions@alithya.com. Visit our blog regularly for new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management. Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.