Veronica Orciuoli
Creating and Delivering Custom Reports with BI Publisher Bursting: personalizing reports based on user-specific data

Photo by Chris Blonk on Unsplash

Creating and Delivering Custom Reports with BI Publisher Bursting: personalizing reports based on user-specific data

Veronica Orciuoli's photo
Veronica Orciuoli
·

5 min read

Introduction

In today’s business environment, the ability to deliver personalized information to each team or customer is essential. Imagine that each department in a company, such as sales, finance, or production, needs a specific report that highlights only the information relevant to them. Instead of creating separate reports manually, a reporting tool can automatically split one large dataset into smaller, customized reports and deliver each to the right person. This is where bursting comes in, an incredibly useful feature in Oracle BI Publisher.

Bursting can be thought of as a process similar to a mail sorting system. Imagine you have a large batch of documents, like monthly statements or performance summaries, and you need to make sure each department, team, or even individual receives only the parts that pertain to them. With bursting, Oracle BI Publisher divides the data based on specific criteria, or a "key," and then creates individual reports for each segment. Each report can be formatted, customized, and delivered automatically to match the preferences of its recipient.

Consider these common examples where bursting is invaluable:

  • Invoices for customers: each customer receives an invoice formatted with their specific information and delivered according to their preferred method, whether by email or secure download.

  • Departmental reports: a master financial report with data for all departments can be divided by cost center, ensuring that each department head receives only their section of the report.

  • Employee pay slips: a company can use a single data extract to generate and email personalized pay slips to all employees, keeping each employee's information confidential and easily accessible.

In this article, we will explore how BI Publisher's bursting feature can be used to meet a specific client request for a customized report. The client required a pivot table report that segments production data by plant location, ensuring that each user receives only the data relevant to their assigned plant. We’ll go through each step of creating and configuring this solution, explaining the logic behind the process and demonstrating how it ensures accuracy and efficiency.

BI Publisher Bursting

Bursting is a process that splits data into blocks, generates documents for each block, and delivers these documents to one or more destinations. The data for the report is generated by running a query once and then splitting it based on a "key" value. A separate document is generated and delivered for each data block according to the specified settings. With BI Publisher’s bursting feature, you can split a single report based on one element in the data model and deliver it based on a second element. The delivery element allows for applying a different template, output format, delivery method, and language to each report segment. Common use cases include:

  • Generating and delivering invoices based on customer-specific layouts and delivery methods;

  • Producing financial reports that, from a master report for all cost centers, split out individual reports for each cost center manager;

  • Generating pay slips for employees from a single data extract and delivering them via email.

For more details on the bursting functionality, see the following link: docs.oracle.com.

Client Request

The client requested a pixel-perfect report containing a pivot table with the following fields:

  • Year

  • Month

  • Source

  • Measure Value

  • Plant

In the database, a table was created to associate each user with their plant of interest. Here’s an example (Figure 1):

Figure 1. Example Table for the bursting.

Creating the Data Model

To begin, a data model was created with two queries. The first represents the head query, containing the field on which the bursting will be performed. The second query gathers the elements needed to construct the pivot table (listed above). Once the data model was set up, the bursting query was created. This allows for sending the report by splitting the data based on a specific field; in this case, each user will receive the report containing only the data relevant to their plant.

In this example, the bursting field is defined as the ‘Plant’ field. Below is a sample of the query used to configure bursting:

SELECT 
Plant "KEY", 
'test template' TEMPLATE, 
'it-IT' LOCAL, 
'XLSX' OUTPUT_FORMAT, 
'test bursting' OUTPUT_NAME, 
'Email' DEL_CHANNEL, 
email PARAMETER1, 
NULL PARAMETER2, 
‘username@domain.it’ PARAMETER3, 
'test' PARAMETER4, 
'body' PARAMETER5, 
'true' PARAMETER6, 
NULL PARAMETER7 
FROM 'TableName' 
ORDER BY Plant;

In this configuration:

  • The "KEY" field (Plant) determines the segmentation for the bursting.

  • TEMPLATE specifies the template applied to the report.

  • LOCAL, OUTPUT_FORMAT, and OUTPUT_NAME define the localization, format, and output name for each report segment.

  • DEL_CHANNEL and other parameters configure the delivery method, email recipient, and additional parameters for email delivery.

Creating the Report

After setting up the query, we downloaded sample data in XML format and created the corresponding RTF template. Detailed instructions for creating the pivot table within the template can be found here: Oracle BI Publisher Pivot Table Documentation.

To achieve the desired segmentation, the report is configured to produce an XLSX output that splits the pivot table by year, creating a separate Excel sheet for each year’s data. Since this split functionality isn’t directly available in the BI Publisher interface, we modified the advanced properties of the field to configure it manually.

The table shown in the image should be inserted within a repeating group, so that the data will be segmented by year:

<?for-each-group:DATASET;./Year?>

In the first row and first column of the table, by double-clicking on the letter ‘C’, you can access the table properties. To replicate the pivot table over a number of pages equal to the years in the dataset, the underlined part should be added to the following formula, achieving the desired effect:

<?crosstab:c85844;"current-group()";
"Source{,o=a,t=t}";"Year{,o=a,t=t},Month{,o=a,t=t}";
"Measure_Value";"sum"?>

Upon completing these steps, it is possible to enhance the report by adding a title, the last data update date, and the plant to which it refers, always remembering that each user will receive the report filtered for their specific plant of interest.

Conclusion

The bursting functionality in Oracle BI Publisher proved extremely effective in meeting the client’s requirements for customization and precision. By creating targeted queries and configuring advanced bursting, we succeeded in producing a detailed report segmented by year, ensuring that each user receives only the data relevant to their plant of interest. This approach not only optimizes workflow but also ensures greater accuracy in information distribution. BI Publisher's ability to support multi-format reports and adapt to custom layouts confirms its value for companies aiming for effective, user-focused reporting.

OracleOracle Cloudbi publisherpublisher