Skip Headers
Oracle® Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher (Oracle Fusion Applications Edition)
11g Release 1 (11.1.1)
Part Number E26386-02
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
PDF · Mobi · ePub

7 Adding Bursting Definitions

This chapter describes BI Publisher's support for bursting reports and how to define a bursting definition in the data model to split and deliver your report to multiple recipients.

This chapter includes the following sections:

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

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

7.3 Adding a Bursting Definition to Your Data Model

Prerequisites:

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 7-1 shows a Bursting definition.

    Figure 7-1 Bursting Definition

    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 Section 7.6, "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 Section 7.4, "Defining the Query for the Delivery XML." Figure 7-2 shows a sample bursting query.

    Figure 7-2 Sample Bursting Query

    Sample bursting query

7.4 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>

Table 7-2 Parameter Values Mapping by Method

Delivery Channel PARAMETER Values

Email

PARAMETER1: Email address

PARAMETER2: cc

PARAMETER3: From

PARAMETER4: Subject

PARAMETER5: Message body

PARAMETER6: Attachment value ('true' or 'false'). If your output format is PDF, you must set this parameter to "true" to attach the PDF to the e-mail.

PARAMETER7: Reply-To

PARAMETER8: Bcc (PARAMETER 9-10 are not used)

Printer

PARAMETER1: Printer group

PARAMETER2: Printer name or for a printer on CUPS, the printer URI, for example: ipp://myserver.com:631/printers/printer1

PARAMETER3: Number of Copies

PARAMETER4: Sides. Valid values are:

  • "d_single_sided" for single-sided

  • "d_double_sided_l" for duplex/long edge

  • "d_double_sided_s" for tumble/short edge

If the parameter is not specified, single-sided is used.

PARAMETER5: Tray. Valid values are:

  • "t1" for "Tray 1"

  • "t2" for "Tray 2"

  • "t3" for "Tray 3"

If not specified, the printer default is used.

PARAMETER6: Print range. For example "3" prints page 3 only, "2-5" prints pages 2-5, "1,3-5" prints pages 1 and 3-5

(PARAMETER 7-10 are not used)

Fax

PARAMETER1: Fax server name

PARAMETER2: Fax number

(PARAMETER 3-10 are not used)

WebDAV

PARAMETER1: Server Name PARAMETER2: Username

PARAMETER3: Password PARAMETER4: Remote Directory

PARAMETER5: Remote File Name

PARAMETER6: Authorization type, values are 'basic' or 'digest'

(PARAMETER 7-10 are not used)

File

PARAMETER1: Directory

PARAMETER2: File Name

(PARAMETER 3-10 are not used)

FTP and SFTP

PARAMETER1: Server name PARAMETER2: Username

PARAMETER3: Password

PARAMETER4: Remote Directory

PARAMETER5: Remote File Name

PARAMETER6: Secure (set this value to 'true' to enable Secure FTP)

(PARAMETER 7-10 are not used)

7.5 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 7-3 Defining the List of Values

Defining the List of Values

Next create a menu type parameter, here named P1:

Figure 7-4 Defining a Parameter

Description of Figure 7-4 follows
Description of "Figure 7-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 7-5 Updating the Bursting Query to Accept the P1 Parameter

Surrounding text describes Figure 7-5 .

7.6 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 7-6, was stored in a CLOB column in the database called "XMLTEXT", and extracted as an XML data set:

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

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 7-7.

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

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 7-8.

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

Manually Entering the XPath into the Split By Field

7.7 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 the section "Configuring Report Properties" in Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

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 the section "Creating a Bursting Job" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Publisher.

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

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

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,
'EMAIL' DEL_CHANNEL,
CST_EMAIL_ADDRESS PARAMETER1,
'accounts.receivable@oracle.com' PARAMETER2,
'bip-collections@oracle.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

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

Important:

If the JDBC driver that you use does not support column alias, when you define the bursting control table, the columns must match exactly the control XML tag name. For example, the KEY column must be named "KEY", upper case is required. PARAMETER1 must be named "PARAMETER1", not "parameter1" nor "param1", and so on.

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:

Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us