Integrated Document Solutions
for Business Applications

Sort Posts

Get Fresh Updates

Home / Blog / Current Page

How to Burst and Deliver Documents from BI Publisher Enterprise

Posted on by Brent Lowe

To continue with one of my previous posts on creating templates the easy way in BI Publisher Enterprise, let’s talk about bursting in BIP. If you are familiar with the bursting control file concept in EBS, Oracle has set up BI Publisher Enterprise a bit differently. There are quite a few resources out there on the web that go into EBS bursting control files in more detail, so I’ll stay on topic for now.

Using the Bursting Properties screen (shown below), there are 4 steps to follow to enable and configure bursting and delivery from BI Publisher for a specified report.

bip_burst1

1. Enable Bursting: The easy to explain part -> Click on the ‘Enable Bursting’ checkbox!

2. Split By: Choose how you want to split/burst the data. Clicking on this drop down you will be presented with a XPath representation of your XML data. Choose the data element that you want BIP to split the final output on.

For example, if I’m sending out invoices to customers, I may want to create a new PDF file for each customer in my data, so I would choose to split on the Customer ID. While processing the XML, every time BI Publisher encounters this element, and the value is different than the previous, a new document will be created.

3. Deliver By: Choose the ‘KEY’ to deliver the data by. Clicking on this drop down you will again be presented with a XPath representation of your XML data. The data element you choose here will be used to determine which row in the following delivery query’s output will be matched with the document that is currently being burst. I know this really sounds confusing at the moment, but stick with me through this next part….

4. Delivery Data Source: Finally, you need to create a SQL query that returns how you want to deliver the data. This query needs to return the following columns:

a. KEY – This value much match the value of the unique identifier that you chose for the ‘Deliver By’ field in step 3.

For example: for a data set that includes overdue invoices sorted by customer, you may want to deliver the data based on unique customer settings. When writing the query, you want to return the customer id for the KEY column. This means that your ‘Deliver By’ field must be the XML element that represents the customer id so that BIP can match them up.

b. TEMPLATE – Name of the template to apply as setup in BIP Enterprise
c. TEMPLATE_FORMAT – The format of the template (i.e. RTF/PDF/etc…)
d. LOCALE – Locale of the template
e. OUTPUT_FORMAT – How you want the final published output to be formatted. (i.e. PDF, HTML, Excel, etc….)
f. DEL_CHANNEL – The delivery channel to use to send the newly burst and formatted data through.
g. PARAMETERS1-10 – These fields define the parameters for the specified delivery channel. i.e. email server, email address, file path, etc…. See the BIP documentation for more detail.

An example always helps me visualize how things work.

In my previous post regarding Starter Templates, I created a quick report that would output all customers who have overdue invoices and how much they owe. Here is a sample:

BI Publisher Enterprise output

Now I want to deliver the report to each customer, but only show them THEIR overdue invoices, not everyone else’s! Each customer may have a different mechanism through which they would like me to communicate information to them. In this case, let’s say that Acme Corp would prefer to receive emails and Zeta Co prefers faxes. I have all of this information in my database so I can write a delivery query that will send Acme Corp’s information to their email address and Zeta Co’s information to their fax number.

Here is my customer table:

BIP Publisher Enterprise

Here is my BIP setup for Bursting:

BI Publisher Enterprise

Note the query, once run, this query will return 2 rows (You’ll note that I hardcoded a few parameters as well -> These can certainly be dynamic!):

BI Publisher Enterprise

After splitting the XML data using the ‘Split By’ field into individual XML files, BI Publisher will run this query and based on the ‘Deliver By’ field in the XML file, will match up the appropriate row above returned by the query. So for customer ‘Acme Corp’ – Customer ID 1, the values will be used to send an email to invoices@acmecorp and for customer ‘Zeta Co’ – Customer ID 2, a fax will be sent to 804-897-1600. Each customer will ONLY receive THEIR information because I set up my ‘Split By’ field to be the customer_id.

A fairly simple example, but I think it gets the point across. The ability to query is actually very powerful allowing you to deliver documents via different channels with dynamic recipient information for each document in a batch. You can even make the output type, the actual look and feel of the document and locale information all dynamic based on values in your database.

Related resource:


Share This



13 Responses to How to Burst and Deliver Documents from BI Publisher Enterprise

  1. Kris says:

    Thanks for your example. I managed to get this to work but as my data is based on 2 datasets I miss part of my data on the report. Only data that is in the dataset on which is split is shown.

    Browsing the internet I get the impression this can be solved by editing the control file but I don’t see how I could edit the control file. I may look over a link to the control file. I have a layout similar to yours in the screenshots.

    Tx!

    • Brent Lowe says:

      Hi Kris, if you’d like to shoot more information my way I could take a look and see if I could help out. Have you taken a look at data templates? You may be able to merge your 2 datasets into 1 XML file using a data template that would be available for bursting.

      • Jet says:

        Did you get to answer this question? I have my burst control query with two sources, one for the actual data, another for the recipients. my select ends:
        from “x” inner join “y” on “x”.”kpi”.”terr_mgr” = “y”.”staff”.”terr_mgr”

        this is not working.
        I tried using
        from “x”, “y” where “x”.”kpi”.”terr_mgr” = “y”.”staff”.”terr_mgr”
        but that was also a bust…

        Can u help? Thx!

  2. Angsuman Mukherjee says:

    Hi Brent,

    We are generating invoices in pdf format and send the same to customer through email.
    we have two requirements here.
    1. If customer asks for summeraized invoice, we need to generate itemized invoice also.
    2. If customer wants the invoice to be sent to email we will also save the invoice as file.

    we have two different rtf templates one for itemized invocie and one for summarized invoice.

    Can we achieve this is single delivery data source query.

    Your reply is highly appreciated

    Thanks
    Angsuman

  3. shashwath says:

    I have a requirmeent to Burst the Reort both in PDF and XLS format , IS it acheivablae?

  4. shashwath says:

    I have a requirement to spool the report both in PDF and XLS format.The rquirement is higley urgent . 🙁 Can you plz help as earley as possible.

  5. Melinda says:

    My customers want to take our Payroll Deposit Advice (XML) output (PDF file) and split it and email it to employees. When I query up the seeded Data Definition, the XML Schema, Bursting Control File, etc are all disabled. I’m assuming this is because the Deposit Advice (XML) is a seeded report and a seeded Data Definition. They are active for other custom Data Definitions.

    Question 1: Do you know how I can set up bursting for an oracle seeded report and seeded Data Definition?

    Question 2: Do you have any references to “how to” documentation for setting up Bursting?

    Thanks!

  6. Sumit says:

    Hi,
    I am running an invoice program where I need to either email, print or do both depending upon the customer address number. Can I do this using BI Pub and how ?

  7. random says:

    heres d code for sql n my bursting program but it’s giving message ” job failed” after sceduling … can anyone letme know what could be the error :

    select instance_id,INVENTORY_ITEM_ID,QUANTITY,OWNER_PARTY_ID
    from csi_item_instances
    where trunc(ACTIVE_START_DATE)=’10-mar-2012′

    bursting :::

    select distinct(INVENTORY_ITEM_ID) KEY,
    ‘TEMP1’ TEMPLATE,
    ‘RTF’ TEMPLATE_FORMAT,
    ‘en-US’ LOCALE,
    ‘HTML’ OUTPUT_FORMAT,
    ‘FILE’ DEL_CHANNEL,
    ‘D:\oracle\crm\BI PUBLISHER’,PARAMETER 1,
    ‘abc.html’,PARAMETER 2
    FROM CSI_ITEM_INSTANCES CSIB
    where trunc(CSIB.ACTIVE_START_DATE)=’10-mar-2012′

  8. Mike Donohue says:

    Brent – thanks for the great article.

    For those using BI Publisher 11g, the concepts are similar but the implementation is a little different. Please see the documentation:

    http://docs.oracle.com/cd/E28280_01/bi.1111/e22258/add_burst_def.htm#CHEGAEFD

  9. Pingback: Oracle eAM Summit and Follow-up From RMOUG | STR Software

Leave a Reply to Brent Lowe Cancel reply

Your email address will not be published. Required fields are marked *