Docstoc

BI_Publisher_Enterprise_RMOUG_2011_White_Paper

Document Sample
BI_Publisher_Enterprise_RMOUG_2011_White_Paper Powered By Docstoc
					REPORT BURSTING AND EMAIL/FAX DELIVERY
WITH BI PUBLISHER ENTERPRISE 11G
Brent Lowe, STR Software

Most people recognize BI Publisher as the latest and greatest reporting solution for Oracle E-Business Suite that allows
developers to remove the dependence on Oracle Reports and move to a more user friendly environment, such as Microsoft
Word, to create the look and feel of their reports. In addition to the solution for E-Business Suite, Oracle has created a
standalone version of this product called „Enterprise‟ that boasts the ability to report from data sources other than Oracle
databases as well as deliver finished reports to recipients via fax, email, print and a variety of other channels. BI Publisher
Enterprise 11g now comes integrated with Oracle Business Intelligence Enterprise Edition and can be installed as a
component of the complete solution or on its own. This deployment flexibility allows companies to position a reporting
solution strategically into the landscape. Think of BI Publisher Enterprise as similar to Crystal Reports1 in that you have a
complete reporting solution running as a separate application available for all parts of your business. This paper aims to
educate system administrators and users on using BI Publisher Enterprise for report creation, bursting and delivery by
example.


The example used in this paper will use BI Publisher Enterprise 11.1.1.3.0 and demonstrate functionality with the creation of
a Sales Management Dashboard and associated hardcopy reports. The finished report can be emailed and faxed to those
delinquent companies using standard BI Publisher functionality. All files (database tables definitions, etc…) used in this
example can be found at http://www.strsoftware.com/blogs/oracle/wp-content/uploads/2011/01/BIP_11g_Files.zip.




Data + Template = Finished Report

BI Publisher Enterprise‟s (hereafter known as BIP) strength is that it separates the data model from the actual report
formatting/layout. BIP relies on two fundamental components to create reports, XML data and a layout template that
represents the look and feel of the report. The XML data can be generated from any number of sources and BIP makes
accessing data in the proper format easy. Templates can be created in Microsoft Word and Adobe Acrobat allowing almost
anyone familiar with these desktop applications the ability to create reports.




1
  Note that there are many differences technically between Oracle BI Publisher Enterprise and Crystal Reports. Please see
the following document for a detailed analysis of these differences.
http://download.oracle.com/otndocs/xmlp/CrystaltoBIP.pdf
Delivery and Bursting with BIP11g                                                                                       Lowe




                   Figure 1: BI Publisher separates data model from layout to create reports.



Generating the Data
The first step in creating a report in BIP is to determine what to report on, more specifically, where is the data to create the
report and how is that data accessed. BIP is not tied explicitly to Oracle databases, data can come from other supported
databases such as MySQL, Microsoft SQL Server, IBM DB2 and essentially any data source that can be accessed via JDBC,
JNDI, OLAP, LDAP or even a file. For our example, we will use a simple JDBC database connection to access database
tables. To setup this connection in BIP, navigate to Administration->Data Sources -> JDBC Connection and click the button
to „Add Data Source‟.




                                    Figure 2: Create a connection to a Data Source

www.rmoug.org                                                  2                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                   Lowe




This screen is used to enter and test the connection to the database. For purposes of our example:
Data Source Name: Demo Sales Dashboard
Driver Type: Oracle 11g
Database Driver Class: oracle.jdbc.OracleDriver
Connection String: jdbc:oracle:thin@[host]:[port]:[sid]
    Where [host], [port] and [sid] are specific parameters for your database.
Username: username of demo database schema
Password: password of demo database schema.


Once a connection has been established, the data to be reported on can be extracted from the database for use by BIP. BIP
provides a Data Model Editor to access data via standard SQL queries, web services, XML feeds and other mechanisms. BIP
will take this information to create XML, the basis of the reporting process. As a Data Model is separate from any report, a
single Data Model can be used for the creation of many reports.
To create a new Data Model, from the Home screen click the „Create Data Model‟ icon:




                                          Figure 3: Create a new Data Model


www.rmoug.org                                                 3                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                   Lowe


The Data Model Editor will allow you to set:
   General Properties – Misc options associated with the Data Model including a description and database connection
    options.
   Data Sets – GUI editor to layout queried data.
   Event Triggers – Allows specification of PRE and POST triggers to run before and after querying for data.
   Flexfields – Allows integration with Oracle E-Business Suite Flex Fields.
   List of Values – Allows for creation of LOV‟s for parameter values.
   Parameters – Creation of parameters for use in making Data Sets variable based on user input.
   Bursting Parameters – Sets bursting properties for use with actual reports.

For the purposes of our example, we will create a basic Data Model using SQL Queries against the JDBC connection that
was setup in the previous step. For more advanced usage, please consult the BI Publisher Enterprise documentation.

The first step is to fill in some information regarding General Properties.




                                  Figure 4: Data Model Editor – General Properties

   Description – Sales data organized by Territory -> Sales Rep -> Sale
   Default Data Source – Demo Sales Dashboard
   All other fields – Remain as default

Click the Save icon (upper right) and name your Data Set.

Next, we must create our queries to pull the data from the database. Click on the Data Sets node in the Data Model Editor
tree and the Data Model Editor GUI appears.

www.rmoug.org                                                   4                         RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                           Lowe




                                          Figure 5: Data Model Editor GUI


Click on the New Data Set Icon and you are presented with your choice of options for generating data.




                                             Figure 6: New Data Set Icon


Choose SQL Query and the Create Data Set – SQL form opens and prompts for the actual SQL Query to use.




www.rmoug.org                                                5                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                               Lowe




                                       Figure 7: Create Data Set – SQL Form


Create 3 Data Sets of the SQL type using the following parameters:
Data Set 1:
Name: Territory
Data Source: Default Data Source
SQL Query:
   SELECT territory_id,
          territory_name,
          territory_manager_email
   FROM   sales_territory


Data Set 2:
Name: Sales Rep
Data Source: Default Data Source
SQL Query:
   SELECT rep_id,
          rep_name,
          rep_territory,
          rep_quota,
          rep_email
   FROM   sales_reps


www.rmoug.org                                               6                 RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                 Lowe


Data Set 3:
Name: Sale
Data Source: Default Data Source
SQL Query:
   SELECT sales_id,
          sales_rep_id,
          sale_date,
          sale_amount
     FROM sales


Once complete, the Data Model Editor Diagram should reflect the following:




                                   Figure 8: Initial View of Data Model Diagram



We can view the XML that these queries will create by clicking the „Save‟ button and then the XML icon button in the upper
right of the screen.




                                                 Figure 9: XML Icon


Select the number of rows to return and click on the Run button to view the generated XML




www.rmoug.org                                               7                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                       Lowe




                                     Figure 10: Generate XML from Data Model


Note that the returned XML data is not nested, meaning that sales are not associated with a sales rep and a sales rep is not
associated with a territory, the XML is simply each query returned as a different group.




www.rmoug.org                                                  8                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                    Lowe




                                                  Figure 11: XML Data


If we want to make this XML data a bit more intuitive and make the links between the actual sales data and the sales rep and
then the sales rep with the territory, we can use the Data Model Editor to create our relationships. Return back to the Data
Model Diagram and do the following:


  1.   In G_1 group, drag TERRITORY_ID to G_2‟s REP_TERRITORY field to link the Sales Rep‟s territory to the main
       Territory query.
  2.   In G_2 group, drag REP_ID to G_3‟s SALES_REP_ID to link an actual sale with a Sales Rep.

The result should be as the following diagram.



www.rmoug.org                                                 9                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                  Lowe




                                   Figure 12: Creating Relationships with Data Sets


Notice the lines and the associated icon next now linked fields. Generating XML now produces a much nicer file that has
each sale associated with the correct sales rep and each sales rep associated with a territory.




                                                   Figure 13: XML Data


To continue to add to our generated data, let‟s also calculate a couple values:
   Total Quota for a territory
   Total Sales for a sales rep

To do this, follow these steps:
  1. Drag REP_QUOTA from G_2 to the „Drop here for aggregate function‟ field of G_1.
  2. In the new field that is created, click on the icon labeled „N‟ to choose the Summary function.




www.rmoug.org                                                  10                         RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                      Lowe




                  Figure 14: Create Summary Function of Total REP_QUOTA for a Territory
  3.   Repeat these steps by dragging SALE_AMOUNT from G_3 to G_2‟s „Drop here for aggregate function‟ field and
       Choose Summary.

Regenerating the XML, we can see that the total quota for all sales reps in a territory is now part of the XML data as well as
the total amount of sales that a sales rep has made.




                                   Figure 15: XML Data with Aggregate Functions




www.rmoug.org                                                 11                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                    Lowe


The final step in generating our XML is to clean it up a bit. If you notice in Figure 15 above, element names have been
dynamically created by BI Publisher such as <CS_2>, <G_1>, <DATA_DS>, etc…. These values can be changed by
clicking on the “Structure” tab of the Data Set Editor.




                                     Figure 16: Data Set Editor – Structure Tab


This table has 2 sections labeled XML View and a Business View. The XML View allows you to name the actual XML
Elements that are generated. The Business View allows you to create user friendly names to display in layout editors.
Changing various values, saving and regenerating the XML will show XML that is more descriptive and intuitive. In our
example, we have renamed the G_1 element to be TERRITORY, the G_2 element to be SALES_REP and G_3 element to be
SALE in both the XML and business views.




www.rmoug.org                                                12                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                     Lowe




                              Figure 17: XML Data with Descriptive Element Names



At this point, we now have the XML data that we need to create our report. The next step is to use this data to generate a
Sales Dashboard and Sales Rep Report. Before doing this, we must generate sample XML data to utilize in creating the
report. Click the XML button to generate sample data and click on the „More Actions‟ icon to save the generated data as
sample data.




www.rmoug.org                                                13                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                     Lowe




                                            Figure 18: Save As Sample Data


Now that the query is taken care of, we can begin the process of creating the report definition within BIP. To create a new
report, navigate to Home -> Create Report




                                                Figure 19: Create Report


BIP will prompt you for the Data Model to utilize for this report, choose the one previously created above, „Sales Data Model
By Territory – Sales Rep – Sale‟.




www.rmoug.org                                                14                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                     Lowe




                                   Figure 20: Create Report – Choose Data Model


 Once a Data Model is associated with a report object, you are ready to start developing layout templates. However, before
doing so, you may want to tweak report properties and/or add parameters, this can be done with the „Parameters‟ and
„Properties‟ buttons. Common report properties include how data is to be cached, font mapping and currency formatting.
Parameters are used to feed dynamic values into your Data Model to produce XML data as defined by a user.




                                   Figure 21: Parameters and Properties Buttons


For the purposes of this example, we will leave the Properties set to default and not specify any Parameters. Click the Save
button and save the report with name “Sales Dashboard and Rep Report”.



Creating and Modifying Templates
Each report can have multiple templates of multiple types. Looking at the main report screen there are a number of options
for Template Types including BIP Layout templates (created with the Layout Editor) as well as RTF, PDF, Excel, Flash,

www.rmoug.org                                                15                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                  Lowe


XSL and eText (created in native tools – MS Word, Adobe Acrobat, etc…). In our example we will create an interactive
Sales Dashboard with a BIP Layout Template and a Sales Rep report with an RTF template that will be emailed to each sales
rep.




                                         Figure 22: Choose a Template Type


Creating an Interactive Dashboard
To create an Interactive Dashboard, the BI Publisher Layout Template must be used. This is a new template type in BIP 11g
and is created with the Layout Editor, also a new feature. To create a new BI Publisher Layout Template, choose one of the
Basic or Shared Templates icons from the Figure 22 above, in our case, choose “Blank (Landscape)” which will open the
Layout Editor as shown in Figure 23.




www.rmoug.org                                               16                           RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                          Lowe




                                                  Figure 23: Layout Editor


On the left hand side you will see a representation of your XML data, across the top is a tab page that will allow you to insert
various components onto the page and change the page layout properties. Based on the component that you choose,
properties for that component will be shown as a new tab. For our example, we want to create a dashboard that shows:
   A pie chart that displays the percentage of total sales by territory
   A bar chart that displays total sales by sales rep
   A table that details each sales rep‟s total sales against their quota.

Additionally, this information should be linked, such that if a user chooses a specific territory, only the data for sales reps in
that territory should be shown in the subsequent chart and table.

To ensure a consistent layout, the first step is to choose the Layout Grid tool and insert a layout grid with two rows and two
columns.




www.rmoug.org                                                    17                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                     Lowe




                                          Figure 24: Layout Grid Component



To create our pie chart, drag a Chart component to the row 2 column 1 to produce Figure 25.




                                     Figure 25: Create Chart in Row 2 Column 1


Because we want this chart to be a pie chart, from the Chart Types button, choose a standard Pie Chart as in Figure 26.




www.rmoug.org                                                18                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                          Lowe




                                          Figure 26: Choose Pie Chart


Now to actually assign data to this Pie Chart, from the left hand pane that displays the data, drag the element
TERRITORY_NAME to the „Drop Series Here‟ box on the chart and then drag the TOTAL_SALES element to the „Drop
Value Here‟ box on the chart to produce Figure 27.




www.rmoug.org                                          19                          RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                   Lowe




                                             Figure 27: Finished Pie Chart



To create our second chart, a bar chart, drag and drop another Chart component to row 2 column 2. As the bar chart is the
default chart type, simply drag and drop the REP_NAME element to the „Drop Series Here‟ box and the TOTAL_SALES
element to the „Drop Value Here‟ flag. The finished chart should look like Figure 28.




www.rmoug.org                                                20                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                        Lowe




                                              Figure 28: Finished Bar Chart


To finish off the charts, let‟s label them. Drag a Text Item component (Figure 29) to row 1 column 1.




                                            Figure 29: Text Item Component


Double click the item to edit it, and type in: Percentage of Total Sales By Territory


Notice in the Text Properties tab that you can change font properties to center the text, make bold, etc… (Figure 30).




                                      Figure 30: Text Item Component Properties

Using the same method, label the second chart: Total Sales By Sales Rep.


www.rmoug.org                                                  21                           RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                     Lowe




                                              Figure 31: Completed Charts



Finally, to create the last visual for the dashboard drag and drop a Data Table component from the Insert tab to below the
charts.




                                           Figure 32: Data Table Component


To populate the table, simply drag and drop elements from the Data Source into the table itself. In our example, we want to
display the Sales Rep Name, Territory, Quota and Total Sales. Drag and drop REP_NAME, TERRITORY_NAME,
REP_QUOTA and TOTAL_SALES to the Data Table to create the table as shown in Figure 33.




www.rmoug.org                                                 22                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                     Lowe




                                         Figure 33: Data Table with Columns


Save the data using the Save icon in the top right and name the layout as „Sales Dashboard‟. To preview how the dashboard
will look click on the preview button shown in Figure 34, note that you can preview the data as a PDF, HTML, RTF, Excel,
etc… file. However, we want to preview the report as Interactive so that users can click and drilldown on data.




                                               Figure 34: Preview Button


In the preview there are a number of functions that can be done by the user.


   Hover over a information in a chart and see the Total Sales data associated with the graphic.
   Click on a territory in the Percentage of Total Sales By Territory and watch the Total Sales By Sales Rep and Data Table
    change to reflect only the sales reps in that territory.
   Click on a sales rep in the Total Sales By Sales Rep bar chart and watch the Data Table change to reflect only that sales
    rep‟s data.

This functionality is achieved using Events and by default they are set up to have the data in one chart filter the data in
subsequent charts. These events can be fine tuned from the Page Layout tab in the Layout Editor with the Configure Events
button shown in Figure 35. Once on the Configure Events dialog, clicking on a Component in the list will allow you to
change Events and their Targets.

www.rmoug.org                                                 23                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                       Lowe




                                               Figure 35: Configure Events



Creating an RTF Template
After creating the Interactive Dashboard, we also want to create a report that can be delivered to each sales rep that shows
their individual sales along with their quota. Return back to the report definition by clicking the „Return‟ button in the upper
right hand of the screen and create a new layout by clicking on the „Add New Layout‟ button as shown in Figure 36.




                                              Figure 36: Add a New Layout



www.rmoug.org                                                  24                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                   Lowe




BIP makes creating a default template as easy as clicking a button. Click the „Generate‟ icon and fill in the Template Name
of „Sales Rep Report‟.




                                           Figure 37: Generate RTF Template


BIP will actually create a default layout for you that includes all of the fields specified in your data model.




www.rmoug.org                                                   25                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                    Lowe


While this report is not perfect, it does make a great starting point. To view the report, be sure to „Save‟ your changes and
„View‟ the report again. Now when viewing, you have 2 tabs at the top, one for your previously created Sales Dashboard and
a new one for the auto generated Sales Rep Report, click the Sales Rep Report tab to view the report.




                                            Figure 38: Generated Template


In this example, BIP generated a RTF template that can be edited in Microsoft Word. BIP is able to merge the data with the
template to create a number of different formats including PDF, HTML, Excel, and RTF. As you can see we are still using
the same data model, just displaying it in a different way for a different purpose.
To edit the default or create new templates, BIP provides a plug-in for Microsoft Word called the Template Builder. BIP
provides a link to install the Template Builder from the Home page.




www.rmoug.org                                                26                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                  Lowe




                                 Figure 39: Download Template Builder for Word


Clicking this link will install the Template Builder which will create a new toolbar and new menu named Oracle BI Publisher
in Microsoft Word.




                         Figure 40: Template Builder Plug-In Toolbar and Menu Items



www.rmoug.org                                               27                           RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                   Lowe


To edit the generated template, follow the steps below to login to BIP, open the generated template, make and save changes.


Within Microsoft Word, from the Oracle BI Publisher Menu, select „Log On‟ and enter user name and password credentials.
For Report Server, enter the URL usually used to logon to BIP.




                             Figure 41: Logon to BI Publisher from Template Builder


Once logged in, select the report and the generated template and hit the „Open Layout Template‟ to open the template.




www.rmoug.org                                                28                           RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                 Lowe




                          Figure 42: Open Sales Rep Report Template for Modification


Because Microsoft Word is the template editor, making changes is simple. Please refer to the BIP report designer
documentation located on OTN for more details on advanced template modification. The Template Builder features preview
functionality which allows the report designer to see view any changes in what will become the final format. This preview
functionality is extremely useful and speeds development time of the actual layout.




www.rmoug.org                                              29                           RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                      Lowe




                                 Figure 43: Template Builder Preview Functionality



Once all modifications are complete, the template can be uploaded back into BIP by following the menu navigation Oracle BI
Publisher -> Upload Template and the Template Builder will import your changes back into the BIP report definition.


At this point in the example a BIP report has been created and is ready to be used. Navigating to the report in BIP, it should
be available for viewing.




www.rmoug.org                                                 30                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                 Lowe




                                Figure 44: Viewing the Modified Report in BI Publisher


To demonstrate new functionality in BIP 11g, we can apply common MS Word styles to our RTF templates using Style
Templates. Style Templates allow companies to setup common headers, footers, paragraph styles, table styles, etc… for use
in RTF templates to
    Make changes in company formatting policies easier to roll out.
    Enforce a common look and feel across all reports.

Create a new Style Template in MS Word following the directions.

    1.   Open a new word document and navigate to Format -> Styles and Formatting
    2.   Create 3 new styles by clicking the „New Style‟ button and using the following criteria
         Name: STR_STANDARD_TABLE_HEADER
         Style Type: Table
         Apply formatting to: Header Row
         Bold: On
         Choose a shading color




www.rmoug.org                                                  31                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                 Lowe


       Name: STR_STANDARD_TABLE_FOOTER
       Style Type: Table
       Apply Formatting To: Last Row
       Bold On
       Chose a shading color

       Name: STR_STANDARD_FONT
       Style Type: Paragraph
       Formatting: Font and Pitch of your choice

  3.   Insert a logo into the Header of the document. The document should look like Figure 45 below.




                                              Figure 45: Style Template

  4.   Save this file as a document template (.dot) named STR_STANDARD_STYLES.dot
  5.   Save this file again as a RTF file (.rtf) name STR_STANDARD_STYLES.rtf
  6.   Upload the Style Template into BIP by going to the Home Page and clicking the New -> Style Template Icon




www.rmoug.org                                              32                           RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                       Lowe




                                              Figure 46: New Style Template
  7.   Upload the RTF version of the Style Template and give it the name STR Standard Styles. Set the locale information
       appropriately and the Style Template is created in BIP.




                                            Figure 47: Upload Style Template


Utilizing the style template is tricky, your report developers need to have access to the styles outside of BIP so that when
creating their RTF reports they can actually set the styles appropriately. This is why we also saved the RTF file as a MS
Word Template (.dot). To apply the styles to the actual template, do the following:


  1.   Open word, login to BIP and download your RTF template as shown previously.
  2.   In Word, navigate to Tools->Templates and Add-Ins and click the Organizer button.
  3.   Click „Close File‟ to close the Normal.dot file, and then click Open File to open the previously saved
       STR_STANDARD_STYLES.dot file.


www.rmoug.org                                                  33                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                        Lowe


  4.   Move the 3 new Styles to the left hand table and now the styles will be available for use in your template! These
       styles could also be pushed into the Normal.dot file in order to have report developer‟s do less work when setting up a
       new template.




                                      Figure 48: Move Custom Styles to Template


Update the template to use these styles and upload the template back into BI Publisher. The final step is to then associate the
Style Template previously uploaded to the report and the layout template. Edit the report „Sales Dashboard and Rep Report‟
and click on the link named „View a list‟ as shown in the Figure below.




                                           Figure 49: View Templates as a List


In the view that this creates, you can set the Style Template to use for the report and which layout templates are to use the
new Style Template. Note that you can only apply a Style Template to RTF based Layout Templates. See figure below.



www.rmoug.org                                                  34                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                  Lowe




                        Figure 50: Associate Style Template and Apply to RTF Template


Now, when viewing the report the Style Template is applied and the logo for the document header, standard font and table
styles are all applied.




www.rmoug.org                                               35                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                       Lowe




                        Figure 51: Finished Report with Standard Style Template Applied



Delivering the Report
BIP offers a number of options for actually acting on the report. Through BIP, a report can be printed, faxed, emailed, sent to
the file system, sent to a FTP site, published via WebDAV or posted via HTTP. Reports can be sent through these mediums
in a one off fashion or scheduled at specific intervals. Additionally reports can be „burst‟ into individual documents and
delivered to the appropriate recipients. This paper will focus on the basics of print, fax and email as well as bursting via BIP.




www.rmoug.org                                                  36                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                    Lowe


Print
Printing from BIP is accomplished via CUPS2. CUPS stands for „Common UNIX Printing System‟ and is an open source
printing system that communicates over the IPP protocol. In order to print via BIP, the CUPS package must be installed
and/or printers capable of communicating via IPP must exist on the network. Actually setting up CUPS is beyond the scope
of this paper, but once printers are available, setting them up in BIP is done via the Admin->Delivery options.




                                     Figure 52: Delivery Configuration Location




There are 2 ways to configure printers in BIP, individually or via a CUPS server.
Configuring a printer individually involves navigating to Admin -> Delivery -> Printer and hitting the „Add Server‟ button.




2
    http://www.cups.org/

www.rmoug.org                                                37                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                     Lowe




                                     Figure 53: Configuring an Individual Printer




This screen allows entry of the printer information.


Server Name is any arbitrary identifier.


URI is the actual IPP address of the printer itself. For example, if CUPS is running on a server named printserver on port 631
and the printer name is hplj4, the URI would be: ipp://printserver:631/printers/hplj4


Filter and Filter Command are used in order to convert the data to a printable language as most printers cannot interpret PDF.


The other fields are for more advanced print environment setups and are best referenced with BIP‟s help facility.


Configuring a printer via a print server is done by navigating to Admin -> Delivery -> CUPS Server and clicking the Add
Server button.




www.rmoug.org                                                38                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                         Lowe




                                   Figure 54: Configuring Printers via Print Server



Utilizing this method you can make ALL printers on a defined print server available for use in BIP automatically. For
example, CUPS is installed on the host „printserver‟, and printserver has 5 different printers defined that are distributed
throughout the office. BIP can be pointed to „printserver‟ and automatically have access to all 5 of those printers. In the
example below, the printers listed were all „discovered‟ by simply pointing BIP to a CUPS server.




                                 Figure 55: CUPS Server with all Printers Displayed


One potential caveat to note is that BIP does not allow editing of any of the printer definitions, this means that there is no
way to set up a PDF to Postscript filter for these „discovered‟ printers like there is for individually configured printers.


Email
Email via BIP uses standard SMTP to communicate to a mail server in order to email a report to the intended recipient(s). To
setup the email delivery channel in BIP, navigate to Admin -> Delivery -> Email and click the „Add Server‟ button.




www.rmoug.org                                                   39                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                   Lowe




                                      Figure 56: Configuring an Email Server
Server Name is an arbitrary identifier and host is the actual mail server that will deliver the email. Depending on how the
mail server is configured, a username and password may be necessary in order to actually send mail. Check with the System
Administrator to see if a username and password are necessary.


A „From‟ email address can be setup as well to set who the document is coming from. By default this address is set to
bipublisher-report@oracle.com. This can be overridden by navigating to Admin->Delivery->Delivery Configuration and
changing the „Email From Address‟ field and then restarting BIP. Note that this address can be overridden only when
bursting.




                              Figure 57: Configuring a Default From Email Address




www.rmoug.org                                               40                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                       Lowe


Email through BIP is very full featured and includes the ability to set multiple recipient addresses, carbon copies (CC), blind
carbon copies (BCC), the from address, the reply-to address, the subject and even text for the message body.


Fax
Faxing through BIP, like Print, is also done via CUPS. BIP documents the following abbreviated prerequisites for fax:
Installation of CUPS
Installation of a fax modem connected to the CUPS server
Compilation and installation of open source software „efax‟3 and FAX4CUPS4 to facilitate the communication between
CUPS and the fax modem.
Once the prerequisites are installed, setup of fax within BIP is done by navigating to Admin->Delivery->Fax and hitting the
„Add Server‟ button.
As fax is using CUPS just like print, the setup of fax and print are the same. The URI in this case is address of the
FAX4CUPS printer.
When faxing, the only property that is able to be set via BIP is the fax number itself.




3
    http://www.cce.com/efax/download/
4
    http://www.gnu.org/directory/productivity/special/fax4CUPS.html


www.rmoug.org                                                  41                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                   Lowe




                                             Figure 58: Adding a Fax Server




Delivery - User Experience
As mentioned, there are two main ways to deliver documents:
One-off/adhoc using the „Send‟ button.
Scheduling to send at a particular time or interval.


These methods are accessed via the report itself with the „Send‟ and „Schedule‟ buttons. However, these buttons will not be
enabled until delivery channels are configured. To configure the delivery channel, see above sections.




www.rmoug.org                                                42                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                    Lowe




                                        Figure 59: Send and Schedule Buttons


Scheduling the reports for delivery can also be done through a „Report Job‟, which can be done from multiple locations
within BIP.




                                                 Figure 60: Report Job


Regardless of which option you choose to deliver a document, they all function through the same tabbed train interface.
  1.   Define the Report to send and any report parameters.




www.rmoug.org                                                 43                           RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                 Lowe




                                      Figure 61: Define Report and Parameters


  2.   Define how the report is to output and any destinations.




                                     Figure 62: Report Output and Destinations



       This tab is particularly powerful as you can specify multiple outputs of differing formats as well as different
       destinations for each output. For example, I could output a snapshot of the Sales Dashboard as HTML to the VP of
       Sales‟ email address and also send the Sales Manager the Sales Rep report as a PDF via a fax job.




www.rmoug.org                                                44                          RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                             Lowe




                               Figure 63: Define Multiple Outputs and Destinations

  3.   Define when the report is to be run. (Note that for „Send‟ this is assumed to be immediately.)




                                                Figure 64: Schedule Tab

www.rmoug.org                                                45                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                       Lowe


       The Schedule tab allows users to schedule the report to be run at a certain time or time interval. For example, one
       application of this functionality is to send the report on a weekly basis.

  4.   Define who to notify upon report completion or if the report fails to complete successfully.




                                                Figure 65: Notification Tab


Once submitted, to see the status of the request, go to Open -> Report Job History from the main Report page




                                              Figure 66: Report Job History



The Report Job History link will show all pending, in process and completed jobs for particular queried criteria. The
information also has a status component to determine if the job was completed successfully or not.




www.rmoug.org                                                 46                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                     Lowe




                                             Figure 67: Report Job History


BIP allows users to drill down into the job to determine more information such as the status of each individual send request
by clicking on the Report Job Name.




www.rmoug.org                                                 47                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                        Lowe




                                   Figure 68: Drill Down to See Status of Deliveries




In this example, all deliveries were marked as successful. Unfortunately, this is not the true reality of the situation. BIP is
only able to report on the successful handoff of a document to the delivery channel, not the actual final status of the delivery.
In the case of the fax delivery above that is marked successful, the fax did not actually go anywhere due to a lack of a fax
modem.


The email job however were successful:




www.rmoug.org                                                  48                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                       Lowe




                                        Figure 69: Email Output from Delivery


Note that the email message body is actually not included as this was an HTML based email.



Bursting
Bursting is the act of taking an entire report and breaking it up into logical sub documents for delivery. In our example, it
would not be a good idea to send the entire report to all sales reps. Instead we would only want to send sales reps the
information that pertains to them. Bursting allows us to break up the report into individual documents for delivery to
individual destinations.




www.rmoug.org                                                  49                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                       Lowe




                                                    Figure 70: Bursting



For our working example, we will burst the Sales Rep Report document by sales rep and deliver to the email destination
found on the sales rep record in the sales_rep table.


The bursting definition is set up on the data model for a report. Multiple bursting definitions can be defined in a data model,
however a report can only be assigned one of them. Setting up bursting is a two step process.
  1.   Define the bursting criteria on the data model
  2.   Associate bursting definition with report


Define the Bursting Criteria on the Data Model

To setup bursting, navigate to the data model and click on the Bursting node.




www.rmoug.org                                                 50                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                    Lowe




                                             Figure 71: Setting up Bursting




Create a new bursting definition by hitting the “+” icon and name the bursting definition „Burst by Sales Rep and Deliver‟.
Type should be set to SQL Query (the only option) and Data Source should be the data source we setup at the beginning of
the exercise (Demo Sales Dashboard).




www.rmoug.org                                                51                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                          Lowe




                                        Figure 72: Add a New Bursting Definition


Decide how the report is to be split using the „Split By‟ field. This field will be pre-populated with the structure of the XML
data created by the data template. Because we want to split (or burst) the data by sales rep, choose:


    /SALES_DATA/TERRITORY/SALES_REP/REP_ID


We are choosing this field, because rep_id in the database is a unique identifier for a sales rep. Splitting on this field will
ensure that each document that is created from the entire report will only be for a single sales rep.


Decide what value to go back to the database with to find the email information using the „Deliver By‟ field. Similar to the
„Split By‟ field, this field will be pre-populated with the structure of the XML data created by the data template. Because we
want to determine delivery information by sales rep, choose:


    /SALES_DATA/TERRITORY/SALES_REP/REP_ID




We are choosing this field because rep_id in the database is a unique identifier for a sales rep. Because our table structure
allows us to determine email information by rep_id, this field is the most logical choice for „Deliver By‟.




www.rmoug.org                                                   52                              RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                             Lowe


Input a SQL Query that returns the following information for each sales rep5:




    Column                         Description
    KEY                            This column must match the value returned by the „Deliver
                                   By‟ field above. In our example, the Deliver By field is
                                   returning the sales rep id. This means that this column
                                   must return sales_rep.rep_id in order for BIP to find a
                                   match and use the rest of the information returned from
                                   this query.
    TEMPLATE                       Template is the name of the layout template to apply.
                                   Remember that multiple templates can be applied to the
                                   same data model creating a different look and feel.
    LOCALE                         Locale is the template locale, for example en-US
    OUTPUT_FORMAT                  Output format is the report output format. For example,
                                   HTML, PDF, RTF, EXCEL
    DEL_CHANNEL                    The actual delivery method, i.e. EMAIL, FAX, FILE, FTP,
                                   PRINT, WEBDAV
    TIMEZONE                       The timezone to use for the report
    CALENDAR                       The calendar type to use for the report
    OUTPUT_NAME                    Name that will be assigned to the output file in the report
                                   job history. This also becomes the name of the
                                   attachment when emailed.
    SAVE_OUTPUT                    True or false, whether or not to save the burst output
    PARAMETER1                     Depends on the DEL_CHANNEL see below
    PARAMETER2                     Depends on the DEL_CHANNEL see below
    PARAMETER3                     Depends on the DEL_CHANNEL see below
    PARAMETER4                     Depends on the DEL_CHANNEL see below
    PARAMETER5                     Depends on the DEL_CHANNEL see below
    PARAMETER6                     Depends on the DEL_CHANNEL see below
    PARAMETER7                     Depends on the DEL_CHANNEL see below
    PARAMETER8                     Depends on the DEL_CHANNEL see below
    PARAMETER9                     Depends on the DEL_CHANNEL see below
    PARAMETER10                    Depends on the DEL_CHANNEL see below

                                  Figure 73: Information to Return in Bursting Query




5
    Note, the majority of this information was taken directly from BI Publisher Help documentation.

www.rmoug.org                                                  53                           RMOUG Training Days 2011
Channe    Paramete     Paramete    Paramete    Paramete    Paramete     Parameter      Paramete    Paramete   Paramete   Paramete
l         r1           r2          r3          r4          r5           6              r7          r8         r9         r 10

Email     Email        cc          From        Subject     Message      Attachment    Reply-To     BCC
          address                                          Body         (true/false)
                                                                        Note that if
                                                                        your output
                                                                        format is
                                                                        pdf, you
                                                                        must set this
                                                                        parameter to
                                                                        "true" to
                                                                        attach the
                                                                        pdf to the
                                                                        email.

Printer   Printer      Printer     Number of   Sides       Tray          Print Range
          Group                    copies

Fax       Fax server   Fax
          Name         Number

WEBDAV Server          Username    Password    Remote      Remote File
       Name                                    Directory   Name        Authorizatio
                                                                       n Type

File      Directory    File Name

FTP       Server       Username    Password    Remote      Remote File Secure
          Name                                 Directory   Name        “true” to
                                                                       enable SFTP

                                       Figure 74: Specific Values for Specific Delivery Channels
Because this information is pulled from a database via a query, bursting is an extremely powerful feature of BIP. Having this
information returned in a query means that individual sales reps can have different settings. For example, Sales Rep A may
want to have the report delivered via email in PDF format using a specific layout while Sales Rep B wants the same report
delivered via Fax with the standard layout.


For the purposes of our example, the following query will be used:




                                               Figure 75: Bursting Query


This query will return the following data for our Sales Reps:




 Sales Rep                        Kent Brockman
 KEY                              1
 TEMPLATE                         Sales Rep Report
 LOCALE                           en-US
 OUTPUT_FORMAT                    PDF
 DEL_CHANNEL                      EMAIL
 TIMEZONE                         Null (use default)
 CALENDAR                         Null (use default)
 OUTPUT_NAME                      Kent Brockman.pdf
 SAVE_OUTPUT                      true
 PARAMETER1                       brent.lowe@strsoftware.com
 PARAMETER2
Delivery and Bursting with BIP11g                                                                                     Lowe


 PARAMETER3                      brent.lowe@strsoftware.com
 PARAMETER4                      Sales Report for Kent Brockman
 PARAMETER5                      Dear Kent Brockman, Please see your sales report attached.
 PARAMETER6                      True
 PARAMETER7                      brent.lowe@strsoftware.com
 PARAMETER8
 PARAMETER9
 PARAMETER10

                                   Figure 76: Return Values from Bursting Query
Once the query has been input (note no final semi-colon is necessary), save the changes.


Associate bursting definition with report
Now that the bursting definition has been setup, it must be associated with a report in order to be used. Edit the Sales
Dashboard and Rep Report and view the Properties. By default, the bursting definition associated with the data model for
your report will be selected in the „Advanced‟ section of the report properties. The drop down beside „Enable Bursting‟
allows you to select which bursting definition to use for the report. Remember, you can only use 1 bursting definition in a
single report.




                                 Figure 77: Enable Bursting on Report Parameters

www.rmoug.org                                                 56                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                  Lowe




Scheduling a Report to be Burst

To schedule a report to be burst, create a new Report Job and schedule as normal, except on the Output tab check the
checkbox labeled „Use Bursting Definition to Determine Output & Delivery Destination‟. When this box is checked, the
output and destination input fields are hidden as all delivery information is expected to come from the bursting query.




                                      Figure 78: Scheduling a Report to be Burst


The results of the bursting should be individual documents for each sales rep.




www.rmoug.org                                                 57                          RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                     Lowe




                                             Figure 79: Burst Email Results




Viewing the status of the job will also display the documents that were burst and the current status. Note that as mentioned
before, the status of Success is only an indicator that BIP handed off the document to the delivery channel, not necessarily
that the document was actually delivered.




www.rmoug.org                                                 58                            RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                       Lowe




                                           Figure 80: Bursting Status Results



Bringing It All Together
As demonstrated, BIP is a very powerful reporting and delivery tool. In our example we were able to create XML based on
data in an Oracle database, generate and modify both an interactive BIP Layout and RTF template to create the look and feel
of the report and then burst and deliver our finished report via fax, email and print to intended recipients.


While BIP‟s reporting capabilities are more than sufficient, enterprise organizations wanting to deliver substantial volumes of
outbound documents may encounter difficulties with BIP‟s native delivery and status mechanisms.


Fax delivery is currently limited to using open source software and fax modems. This open source software can be limited by
platform and does not come with a support contract. Unfortunately, fax modems are not known for their reliability and have
been replaced in modern organizations with fax servers capable of high inbound and outbound volumes utilizing multiple
phone lines and even fax over IP. Fax is also limited in that the document can only be sent to one recipient at a time and
there are no other fields available for uniquely identifying the document or populating a coversheet.


Delivery status may be another concern. As mentioned throughout the paper, the delivery status posted to the job status may
not truly indicate the status of the document. For email, there is not much that can be done, it is either sent or bounces back.
For fax however, there can be a whole host of issues such as voice pickups, busy tones, hang ups etc… In all of these cases,
BIP would have marked the status the fax of Successful as it was able to send it to the CUPS printer.



www.rmoug.org                                                  59                             RMOUG Training Days 2011
Delivery and Bursting with BIP11g                                                                                  Lowe




There are ways around these limitations using 3rd party delivery solutions that integrate with BIP. These solutions
complement BIP functionality and together provide a well rounded, reliable document generation and delivery solution.



About STR Software:
STR Software, an Oracle PartnerNetwork Gold Partner, is a global business-to-business software provider with over twenty-
four years in the industry. The company develops, markets, and supports automated data exchange and document delivery
solutions for host ERP applications including the Oracle E-Business Suite, Oracle‟s PeopleSoft, and SAP in UNIX, Linux,
MPE, and Windows environments. Headquartered in Richmond, Virginia, STR Software‟s customer base includes Cisco
Systems, General Electric, Ricoh and many more. For more information, please visit www.strsoftware.com.




www.rmoug.org                                               60                           RMOUG Training Days 2011

				
DOCUMENT INFO