OBIEE Step by Step Guide

January 8, 2010

BI Publisher Report Bursting

What is report bursting?

Rather than giving a definition, think of what it does as explained below and build up the definition yourself. 

Using BI Publisher’s bursting feature you can split a single report based on a key in the report data and deliver the report based on a second key in the report data. Driven by the delivery key, you can apply a different template, output format, delivery method, and locale to each split segment of your 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, bursting out individual cost center reports to the appropriate manager

• Generation of payslips to all employees based on one extract and delivered via e-mail

 How does the BI Publisher bursting work?

BI Publisher will build the delivery XML data set based on the sql query that was used in creating the result set. The delivery data in this XML document is used as a mapping table for each Deliver By element. 

As you know, for report bursting to work, you need to supply two result set elements that determines the following: 

The Split By element is the data element from the report file that you wish to split the report by. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_NAME.

 The Deliver By element is the data element from the report file by which to determine the delivery method. In the invoice example, it is likely that each invoice will have delivery criteria determined by customer, therefore the Deliver By element may be CUSTOMER_ID.

 How does the delivery XML look like in BI Publisher?

<ROWSET>

<ROW>

<KEY></KEY>

<TEMPLATE></TEMPLATE>

<TEMPLATE_FORMAT></TEMPLATE_FORMAT>

<LOCALE></LOCALE>

<OUTPUT_FORMAT></OUTPUT_FORMAT>

<DEL_CHANNEL></DEL_CHANNEL>

<PARAMETER1></PARAMETER1>

<PARAMETER2></PARAMETER2>

<PARAMETER3></PARAMETER3>

<PARAMETER4></PARAMETER4>

<PARAMETER5></PARAMETER5>

<PARAMETER6></PARAMETER6>

<PARAMETER7></PARAMETER7>

<PARAMETER8></PARAMETER8>

<PARAMETER9></PARAMETER9>

<PARAMETER10></PARAMETER10>

</ROW>

</ROWSET>

where

• KEY is 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.

• TEMPLATE – is the name of the Layout template to apply. Note that the value is the

Layout name (for example, “Invoice”), not the template file name (for example,

invoice.rtf).

• TEMPLATE_FORMAT – is the format of the layout template. Valid values are:

• RTF

• PDF

• ETEXT

• XSL_FO

• LOCALE – is the template locale, for example, “en-US”.

• OUTPUT_FORMAT – is the output format. Valid values are:for example: pdf, html,

excel.

• HTML

• PDF

• RTF

• EXCEL

• DEL_CHANNEL – is the delivery method. Valid values are:

• EMAIL

• FAX

• FILE

• FTP

• PRINT

• WEBDAV

Channel Parameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6 Parameter7
EMAIL Emailaddress cc From Subject Message body Attachment tru/false(For PDF, always set this parameter to true) Reply-to
Printer PrinterGroup Printer Number ofcopies Sides Tray    
Fax Fax serverName Fax Number          
WEBDAV ServerName Username Password RemoteDirectory Remote FileName    
File Directory File Name          
FTP ServerName Username Password RemoteDirectory Remote FileName    

Do you need BI Scheduler to work for report bursting?

Of Course, Yes.

Thanks for your feedback……

 

Advertisements

4 Comments »

  1. This is very help. But in what cases why this wouldn’t work? The notification email works fine, but the bursting isn’t working at all.

    The delivery XML looks good. Still dosen’t work at all.

    Can you Help??

    Thanks Anita

    Comment by Anita Clifford — January 14, 2011 @ 7:04 pm

    • Anita
      Need more info to see why..

      Comment by harikv — January 15, 2011 @ 6:18 am

  2. I am testing 11g bipub, however, it works good if a I send a report without burst, with burst it runs but doesn’t deliver any email.

    The data query is :

    SELECT cod_cuenta, id_nss, nombre, nominal, saldo_cta_hoy,
    saldo_cta_hoy – nominal rentabilidad, diabalancexmail, email,
    round(nominal/saldo_cta_hoy,2) pct_nominal,
    round((saldo_cta_hoy – nominal)/saldo_cta_hoy,2) pct_rentab
    FROM (SELECT cod_cuenta, id_nss, nombre,
    (SELECT SUM (mon_movimto) mon_nominal
    FROM fo_movimtos_deta
    WHERE cod_cuenta = z.cod_cuenta AND estado = ‘A’) nominal,
    saldo_a_fecha (‘2’, cod_cuenta, SYSDATE, 10, 20) saldo_cta_hoy,
    diabalancexmail, email
    FROM (SELECT /*+ ordered use_nl(b,c) use_nl(c,p) */
    b.cod_cuenta, c.id_nss,
    p.primer_nombre
    || ‘ ‘
    || segundo_nombre
    || ‘ ‘
    || primer_apellido
    || ‘ ‘
    || segundo_apellido nombre,
    diabalancexmail,
    NVL2 (email_usuario,
    email_usuario || ‘@’ || email_servidor,
    ‘jviquez@afpsiembra.com’
    ) email
    FROM si_cuentas_web b, fo_cuentas c, personas_fisicas p
    WHERE b.cod_cuenta = c.cod_cuenta
    AND b.tipo_fondo = ‘2’
    AND balancexmail = ‘1’
    and b.cod_cuenta in (‘5268′,’10345’)
    AND diabalancexmail = :p_dia
    AND c.cod_empresa = ‘2’
    AND c.cod_cliente = p.cod_per_fisica
    AND c.est_cuenta = ‘A’) z)

    and the burst query is:

    SELECT cod_cuenta KEY, ‘BalancexEmail_ly’ TEMPLATE,
    ‘PDF’ output_format, ‘EMAIL’ del_channel,
    email parameter1, — to
    NULL parameter2, –cc
    ‘hramirez@afpsiembra.com’ parameter3,
    –from
    ‘Balance de Saldo de tu CCI AFP Siembra’ parameter4, –subject
    ‘Para ver este correo…..utilizar Acrobat PDF reader….’ parameter5,

    –body
    ‘true’ parameter6, ‘info@afpsiembra.com’ parameter7
    FROM (SELECT /*+ ordered use_nl(b,c) use_nl(c,p) */
    b.cod_cuenta, c.id_nss,
    p.primer_nombre
    || ‘ ‘
    || segundo_nombre
    || ‘ ‘
    || primer_apellido
    || ‘ ‘
    || segundo_apellido nombre,
    diabalancexmail,
    NVL2 (email_usuario,
    email_usuario || ‘@’ || email_servidor,
    ‘jviquez@afpsiembra.com’
    ) email
    FROM si_cuentas_web b, fo_cuentas c, personas_fisicas p
    WHERE b.cod_cuenta = c.cod_cuenta
    and b.cod_cuenta in (‘5268′,’10345’)
    AND b.tipo_fondo = ‘2’
    AND balancexmail = ‘1’
    AND diabalancexmail = :p_dia
    AND c.cod_empresa = ‘2’
    AND c.cod_cliente = p.cod_per_fisica
    AND c.est_cuenta = ‘A’) z

    divide by cod_cuenta
    delivery by “cod_cuenta”

    Thanks.

    Comment by Javier — March 4, 2011 @ 2:15 pm

  3. Clear & basic explanation of bursting. It’d be nice if you followed it up with a full example.

    Cheers!

    Comment by Nick — July 21, 2011 @ 8:06 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: