Adding Bursting Definitions

About Bursting

Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations. The data for the report is generated by executing a query once and then splitting the data based on a "Key" value. For each block of the data, a separate document is generated and delivered.

Using BI Publisher's bursting feature you can split a single report based on an element in the data model and deliver the report based on a second element in the data model. Driven by the delivery element, you can apply a different template, output format, delivery method, and locale to each split segment of the report. Example implementations include:

• Invoice generation and delivery based on customer-specific layouts and delivery preference

• Financial reporting to generate a master report of all cost centers, splitting out individual cost center reports to the appropriate manager

• Generation of pay slips to all employees based on one extract and delivered through e-mail

What is the Bursting Definition?

A bursting definition is a component of the data model. After you have defined the data sets for the data model, you can set up one or more bursting definitions. When you set up a bursting definition, you define the following:

• The Split By element is an element from the data that governs how the data is split. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_ID. The data set must be sorted or grouped by this element.

• The Deliver By element is the element from the data that governs how formatting and delivery options are applied. In the invoice example, it is likely that each invoice has delivery criteria determined by customer; therefore, the Deliver By element would also be CUSTOMER_ID.

• The Delivery Query is a SQL query that you define for BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details.

Adding a Bursting Definition to Your Data Model

Prerequisites:

• You have defined the data set for this data model

• The data set is sorted or grouped by the element by which you want to split the data in your bursting definition

• The delivery and formatting information is available to BI Publisher. The information can be provided at runtime to BI Publisher in one of the following ways:

  • The information is stored in a database table available to BI Publisher (for a dynamic delivery definition)

  • The information is hard coded in the delivery SQL (for a static delivery definition)

• The report definition for this data model has been created and includes the layouts to be applied to the report data.

To add a bursting definition:

1. On the component pane of the data model editor, click Bursting.

2. On the Bursting definition table, click the Create new Bursting button.

3. Enter the following for this bursting definition:

   Name — For example, "Burst to File"

   Type — SQL Query is currently the only supported type

   Data Source — Select the data source that contains the delivery information

   Figure 19-1 shows a Bursting definition.

   Figure 19-1 Bursting Definition

   
   

4. In the lower region, enter the following for this bursting definition:

   Split By — Select the element from the data set by which to split the data.

   Deliver By — Select the element from the data set by which to format and deliver the data.

   
   

   Note: If the Split By and Deliver By elements reside in an XML document stored as a CLOB in your database, you must enter the full XPATH in the Split By and Delivery By fields. For more information, see Defining the Split By and Deliver By Elements for a CLOB/XML Data Set.

   
   

   SQL Query — Enter the query to construct the delivery XML. For information on how to construct the bursting query, see Defining the Query for the Delivery XML. Figure 19-2 shows a sample bursting query.

   Figure 19-2 Sample Bursting Query

   
   

Defining the Query for the Delivery XML

The bursting query is a SQL query that you define to provide BI Publisher with the required information to format and deliver the report. BI Publisher uses the results from the bursting query to create the delivery XML.

The BI Publisher bursting engine uses the delivery XML as a mapping table for each Deliver By element. The structure of the delivery XML required by BI Publisher is as follows:

<ROWSET>
 <ROW>
  <KEY></KEY> 
  <TEMPLATE></TEMPLATE>    
  <LOCALE></LOCALE> 
  <OUTPUT_FORMAT></OUTPUT_FORMAT> 
  <DEL_CHANNEL></DEL_CHANNEL>
  <TIMEZONE></TIMEZONE>
  <CALENDAR></CALENDAR> 
  <OUTPUT_NAME></OUTPUT_NAME>
  <SAVE_OUTPUT></SAVE_OUTPUT>
  <PARAMETER1></PARAMETER1> 
  <PARAMETER2></PARAMETER2> 
  <PARAMETER3></PARAMETER3> 
  <PARAMETER4></PARAMETER4> 
  <PARAMETER5></PARAMETER5> 
  <PARAMETER6></PARAMETER6> 
  <PARAMETER7></PARAMETER7> 
  <PARAMETER8></PARAMETER8> 
  <PARAMETER9></PARAMETER9> 
  <PARAMETER10></PARAMETER10>
  </ROW>
</ROWSET>

• KEY — The Delivery key and must match the Deliver By element. The bursting engine uses the key to link delivery criteria to a specific section of the burst data. Ensure that you use double quotes around "KEY" in the select statement, for example:

  select d.department_name as "KEY",
  

• TEMPLATE — The name of the Layout to apply. Note that the value is the Layout name (for example, 'Customer Invoice'), not the template file name (for example, invoice.rtf).

• LOCALE — The template locale, for example, 'en-US'.

• OUTPUT_FORMAT — The output format. For a description of each type, see the section "Selecting Output Formats." Table 19-1 shows the valid values to enter for the bursting query.

  Table 19-1 Values to Enter for OUTPUT_FORMAT

  Output Format	Value to Enter in Bursting Query	Template Types That Can Generate This Output Format
  Interactive	N/A	Not supported for bursting
  HTML	html	BI Publisher, RTF, XSL Stylesheet (FO)
  PDF	pdf	BI Publisher, RTF, PDF, Flash, XSL Stylesheet (FO)
  RTF	rtf	BI Publisher, RTF, XSL Stylesheet (FO)
  Excel (mhtml)	excel	BI Publisher, RTF, Excel, XSL Stylesheet (FO)
  Excel (html)	excel2000	BI Publisher, RTF, Excel, XSL Stylesheet (FO)
  Excel (*.xlsx)	xlsx	BI Publisher, RTF, XSL Stylesheet (FO)
  PowerPoint (mhtml)	ppt	BI Publisher, RTF, XSL Stylesheet (FO)
  PowerPoint (.*pptx)	pptx	BI Publisher, RTF, XSL Stylesheet (FO)
  MHTML	mhtml	BI Publisher, RTF, Flash, XSL Stylesheet (FO)
  PDF/A	pdfa	BI Publisher, RTF, XSL Stylesheet (FO)
  PDF/X	pdfx	BI Publisher, RTF, XSL Stylesheet (FO)
  Zipped PDFs	pdfz	BI Publisher, RTF, PDF, XSL Stylesheet (FO)
  FO Formatted XML	xslfo	BI Publisher, RTF, XSL Stylesheet (FO)
  Data (XML)	xml	BI Publisher, RTF, PDF, Excel, Flash, XSL Stylesheet (FO), Etext, XSL Stylesheet (HTML XML/Text)
  Data (CSV)	csv	BI Publisher, RTF, PDF, Excel, Flash, XSL Stylesheet (FO), XSL Stylesheet (HTML XML/Text), Etext
  XML	txml	XSL Stylesheet (HTML XML/Text)
  Text	text	XSL Stylesheet (HTML XML/Text), Etext
  Flash	flash	Flash

  
  

• SAVE_OUTPUT — Indicates whether to save the output documents to BI Publisher history tables that the output can be viewed and downloaded from the Report Job History page.

  Valid values are 'true' (default) and 'false'. If this property is not set, the output is saved.

• DEL_CHANNEL — The delivery method. Valid values are:

  • EMAIL

  • FAX

  • FILE

  • FTP

  • PRINT

  • WEBDAV

  • CONTENT

• TIMEZONE — The time zone to use for the report. Values must be in the Java format, for example: 'America/Los_Angeles'. If time zone is not provided, then the system default time zone is used to generate the report.

• CALENDAR — The calendar to use for the report. Valid values are:

  • GREGORIAN

  • ARABIC_HIJRAH

  • ENGLISH_HIJRAH

  • JAPANESE_IMPERIAL

  • THAI_BUDDHA

  • ROC_OFFICIAL (Taiwan)

  If not provided, the value 'GREGORIAN' is used.

• OUTPUT_NAME — The name to assign to the output file in the report job history.

• Delivery parameters by channel — The values required for the parameters depend on the delivery method chosen. The parameter values mappings for each method are shown in Table 19-2. Not all delivery channels use all the parameters.

Passing a Parameter to the Bursting Query

You can pass the value for an element of your bursting XML using a parameter defined in the data model. For example, if you want to be able to select the template at the time of submission, you can define a parameter in the data model and use the :parameter_name syntax in your query. The following example demonstrates this use case of a parameter in a bursting query.

Assume your report definition includes three layouts: layout1, layout2, and layout3. At submission time you want to select the layout (or TEMPLATE, as defined in the bursting query) to use. In your data model, define a list of values with the layout names. The following figure shows a data model with the layout list of values:

Figure 19-3 Defining the List of Values

Next create a menu type parameter, here named P1:

Figure 19-4 Defining a Parameter

In the bursting query, pass the parameter value to the TEMPLATE field using :P1 as shown in the following figure:

Figure 19-5 Updating the Bursting Query to Accept the P1 Parameter

Defining the Split By and Deliver By Elements for a CLOB/XML Data Set

If the split-by and deliver-by elements required for your bursting definition reside in a data set retrieved from a CLOB column in a database, BI Publisher cannot parse the XML to present the elements in the Split By and Deliver By lists. You therefore must manually enter the XPath to locate each element in the retrieved XML data set. To ensure that you enter the path correctly, use the data model editor's Get XML Output feature to view the XML that is generated by the data engine.

For example, the sample XML code, shown in Figure 19-6, was stored in a CLOB column in the database called "XMLTEXT", and extracted as an XML data set:

Figure 19-6 Sample Data Extract of Data Stored as CLOB

For this example, you want to add a bursting definition with split by and deliver by element based on the DEPARTMENT_ID, which is an element within the CLOB/XML data set.

When you add the bursting definition, the Split By and Deliver By lists cannot parse the structure beneath the XMLTEXT element. Therefore, the list does not display the elements available beneath the XMLTEXT node, as shown in Figure 19-7.

Figure 19-7 Split By List Presents Only Top-Level Nodes

To use the DEPARTMENT_ID element as the Split By element, manually type the XPath into the field as shown in Figure 19-8.

Figure 19-8 Manually Entering the XPath into the Split By Field

Configuring a Report to Use a Bursting Definition

Although you can define multiple bursting definitions for a single data model, you can enable only one for a report.

Enable a report to use a bursting definition on the Report Properties dialog of the report editor. For more information see "Configuring Report Properties."

After you configure the report to use the bursting definition, when you schedule a job for this report you can choose to use the bursting definition to format and deliver the report. For more information see "Creating a Bursting Job."

You can also opt not to use the bursting definition and choose your own output and destination as a regular scheduled report.

Sample Bursting Query

The following example is based on an invoice report. This report is to be delivered by CUSTOMER_ID to each customer's individual e-mail address.

This example assumes that the delivery and formatting preferences for each customer are contained in a database table named "CUSTOMERS". The CUSTOMERS table includes the following columns that will be retrieved to create the delivery XML dynamically at runtime:

• CST_TEMPLATE

• CST_LOCALE

• CST_FORMAT

• CST_EMAIL_ADDRESS

The CUSTOMER_ID will be used as the KEY and also to define the output file name.

The SQL code to generate the delivery data set for this example is as follows:

select distinct
CUSTOMER_ID as "KEY",
CST_TEMPLATE TEMPLATE,
CST_LOCALE LOCALE,
CST_FORMAT OUTPUT_FORMAT,
CUSTOMER_ID OUTPUT_NAME,
'EMAIL' DEL_CHANNEL,
CST_EMAIL_ADDRESS PARAMETER1,
'accounts.receivable@example.com' PARAMETER2,
'bip-collections@example.com' PARAMETER3,
'Your Invoices' PARAMETER4,
'Hi'||CUST_FIRST_NAME||chr(13)|| 'Please find attached your
invoices.' PARAMETER5,
'true' PARAMETER6,
'donotreply@mycompany.com' PARAMETER7
from CUSTOMERS

Creating a Table to Use as a Delivery Data Source

If the delivery information is not easily available in the existing data sources, then you can consider creating a table to use for the query to create the delivery XML. Following is a sample:

CREATE TABLE "XXX"."DELIVERY_CONTROL"
   ( "KEY" NUMBER,
     "TEMPLATE" VARCHAR2(20 BYTE),
     "LOCALE" VARCHAR2(20 BYTE),
     "OUTPUT_FORMAT" VARCHAR2(20 BYTE),
     "DEL_CHANNEL" VARCHAR2(20 BYTE),
     "PARAMETER1" VARCHAR2(100 BYTE),
     "PARAMETER2" VARCHAR2(100 BYTE),
     "PARAMETER3" VARCHAR2(100 BYTE),
     "PARAMETER4" VARCHAR2(100 BYTE),
     "PARAMETER5" VARCHAR2(100 BYTE),
     "PARAMETER6" VARCHAR2(100 BYTE),
     "PARAMETER7" VARCHAR2(100 BYTE),
     "PARAMETER8" VARCHAR2(100 BYTE),
     "PARAMETER9" VARCHAR2(100 BYTE),
     "PARAMETER10" VARCHAR2(100 BYTE),
     "OUTPUT_NAME" VARCHAR2(100 BYTE),
     "SAVE_OUTPUT" VARCHAR2(4 BYTE),
     "TIMEZONE" VARCHAR2(300 BYTE),
     "CALENDAR" VARCHAR2(300 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLES";

Tips for creating a creating bursting delivery table:

• If the split data set does not contain a DELIVERY_KEY value, then the document is neither delivered nor generated. For example, using the preceding example, if customer with ID 123 is not defined in the bursting delivery table, this customer's document is not generated.

• To enable a split data set to generate more than one document or deliver to more than one destination, duplicate the DELIVERY_KEY value and provide different sets of OUTPUT_FORMAT, DEL_CHANNEL, or other parameters. For example, customer with ID 456 wants his document delivered to two e-mail addresses. To achieve this, insert two rows in the table, both with 456 as the DELIVERY_KEY and each with its own e-mail address.