Skip Headers

Oracle Business Intelligence Publisher Report Designer's Guide
Release 10.1.3.4
Part Number E12187-01		Go to Table of Contents
Contents 		Go to previous page
Previous 		Go to next page
Next

Creating a New Report

Process Overview

Note: You must be assigned the BI Publisher Developer role or BI Publisher Administrator role to create or edit reports.

Creating a new report consists of the following steps:

  1. Create the report entry in the desired folder on the Reports page.

  2. Open the Report Editor.

  3. Specify the general properties for the report.

  4. Define the Data Model.

    Your report data may come from a SQL query, an HTTP feed, a Web service, an Oracle BI Answers request, a file, an Oracle Discoverer Worksheet, or BI Publisher's data template.

  5. Define the parameters that you want users to pass to the query, and define lists of values for users to select parameter values.

  6. Test your data model.

  7. Design the layout template.

  8. Upload your templates to the Report Editor.

  9. (Optional) Set runtime configuration properties for your report. See Setting Runtime Configuration Properties.

  10. (Optional) Enable bursting.

  11. (Optional) Add translations for your reports. See Translating Reports.

Create the Report Entry and Specify General Properties

Note: You must be assigned the BI Publisher Developer role or BI Publisher Administrator Role to create or upload reports.

  1. Navigate to the folder in which you want the new report to reside.

    To create a new folder for this report, select the Create a new folder link.

  2. Select the Create a new report link from the Folder and Report Tasks menu. This will invoke a text box for you to enter the name of your new report.

    the picture is described in the document text

  3. Enter the name for your new report and select Create. This creates the listing for your report within the current folder.

  4. Select the Edit link for the new report entry. This invokes the Report Editor.

Using the Report Editor

  1. Navigate to the Report Editor by selecting the Edit link for a report.

    From this page you can:

    Report Editor

    the picture is described in the document text

  2. Enter the Report Properties:

  3. Select the Save icon to save your report definition.

Defining the Data Model

BI Publisher requires XML data to publish reports. The XML data can come from any of the following sources:

You can define multiple data sets for one report and each data set can have a different data source and source type. When you define multiple SQL queries, you can concatenate the resulting data sets.

To Define the Data Model:

  1. Select Data Model.

    the picture is described in the document text

    This will display the Main Data Set list. This list will be empty until you define a data set. To define a data set, select New.

  2. After you have defined your data sets, select Data Model. The data sets that you have defined will now populate the list for Main Data Set.

    Note: If you are defining multiple data sets from SQL queries, you can combine them into a single data set by selecting Concatenated SQL Data Source. It is strongly recommended that you select Make row names unique if you are concatenating datasets.

Adding Parameters and Lists of Values

Add parameters to your report definition to enable your users to interact with the report and specify the data of interest from the data set; or specify hidden parameters to control the data returned to a user from a data set.

Note: Parameters are not supported for Oracle BI Answers request data set type.

BI Publisher supports the following parameter types:

Adding a List of Values:

Adding Parameters

Select Parameters and then select the New icon to define parameters for the report.

the picture is described in the document text

Adding Layouts to the Report Definition

BI Publisher offers several options for designing templates for your reports. Templates can be in any of the following formats. Note that some formats restrict output types.

To add a layout to your report definition, select Layouts to specify the layout template for the report. Defining layouts consists of two steps: Upload a template file, and then assign the template file to a Layout definition. If you are connected to BI Publisher through the Template Builder or Excel Analyzer, you can upload the layout file in one step.

Note: To build a template for your report, you must have sample data. Once you have defined your query, you can select the View link to generate XML. Select the Export button and save the file to your local directory. If you are building an RTF template or Excel template you can load this data directly to the Template Builder for Word or Excel using BI Publisher's desktop tools described in the following sections.

Adding a Layout - General Steps

To add a layout to your report definition, select Layouts to specify the layout template for the report. Defining layouts consists of two steps: Upload a template file, and then assign the template file to a Layout definition. If you are connected to BI Publisher through the Template Builder or Excel Analyzer, you can upload the layout file in one step.

See Creating an RTF Template Using the Template Builder for Word.

BI Publisher can also autogenerate a template for you if your data model type is a SQL query or data template. See Autogenerating a Layout.

The general guidelines for uploading and defining the layout for any template type are as follows:

  1. Upload your layout template file.

    From the BI Publisher Report Editor. Select Layouts.

    Use the Browse button to locate it in your local file system, then select Upload. The template will now appear in the Manage Template Files region. You can upload as many templates as you want to make available to this report.

    the picture is described in the document text

  2. Select the New icon to create the definition for the new template.

    the picture is described in the document text

  3. Select Save. The Layout will now appear as an available template when you run the report.

  4. Select a Default Template. The Default Template will be used by default by the online viewer and the scheduler unless the user selects another.

Autogenerating a Layout

If your data model type is a SQL query or a Data Template, BI Publisher can autogenerate an RTF template for you using the default data model for your report. The autogenerated template will present the elements from your data in a simple table layout.

To autogenerate a layout:

  1. From the BI Publisher Report Editor. Select Layouts.

  2. In the Auto Generate Layout region enter a name with the .rtf extension for your layout file (for example: simplelisting.rtf).

  3. Follow the instructions starting with Step 2 in the above procedure, Adding a Layout - General Steps to associate the template file with a layout definition.

Creating an RTF Template Using the Template Builder for Word

Prerequisites:

Features of the Template Builder

When you open Microsoft Word after installing the Template Builder you will notice the Oracle BI Publisher menu and the BI Publisher toolbar.

the picture is described in the document text

The toolbar and the menu provide two methods of performing many of the same functions, including:

Building and Uploading Your Template

You can build and upload your template via a direct connection with the BI Publisher server, or you can build and upload your template in disconnected mode.

Connected Mode

  1. Open Microsoft Word.

  2. From the Oracle BI Publisher menu, select Log On.

  3. Enter your BI Publisher credentials and the URL for the BI Publisher server. (Contact your system administrator if you do not know the URL.)

  4. The Open Template dialog presents the same folder structure as your BI Publisher Reports home page. Select the report for which you want to build a template.

  5. Select Open Report to load the data to the Template Builder; or double-click <New> in the Layout Templates pane.

    Note that any existing templates will be listed in the Layout Templates pane.

    the picture is described in the document text

  6. Follow the guidelines in the Template Builder online help (from the Oracle BI Publisher menu) to insert data fields and design your template using features such as tables, charts, graphics, and crosstabs. Use Microsoft Word to apply formatting to fonts and other objects in your template.

    For more advanced template options, use the guidelines in Creating an RTF Template.

  7. To upload your template to the BI Publisher server and add it to your report definition, select Publish Template As from the Oracle BI Publisher menu.

    If you have not saved your template, you will be prompted to save it in Rich Text Format.

  8. Enter a name for your template in the Upload as New dialog. Note that this is the name that appears under Layouts in the Report Editor. This is also the template name that will be displayed whenever the user is presented an option for selecting a template for this report (for example, in the View Report page).

  9. (Optional) Limit the output formats for this template.

    From the BI Publisher Enterprise interface, open the report in the Report Editor. Under Layouts, select your uploaded template. If you wish to limit the output formats for this report, select only the formats you want to make available.

Disconnected Mode

From the Report Editor:

  1. Generate a sample data file.

    From the Report Editor or from the Reports page, select View. If no layouts are defined for your report, then the output type will default to xml, otherwise, choose data for the output type. Select Export. Save the results as an XML file to a local directory.

  2. Open Microsoft Word with the Template Builder installed.

  3. From the Oracle BI Publisher menu select Data and then select Load Sample XML Data. Locate your sample data file in your local directory and select Open. A pop up message will indicate your data has loaded successfully.

  4. Follow the guidelines in the Template Builder online help (from the Oracle BI Publisher menu) to insert data fields and design your template using features such as tables, charts, graphics, and crosstabs. Use Microsoft Word to apply formatting to fonts and other objects in your template.

    For more advanced template options, use the guidelines in Creating an RTF Template.

  5. Upload your layout template file.

    Return to your report definition in the BI Publisher Report Editor. Select Layouts.

    Use the Browse button to locate it in your local file system, then select Upload. The template will now appear in the Manage Template Files region. You can upload as many templates as you want to make available to this report.

    the picture is described in the document text

  6. Select the New icon to create the definition for the new template.

    the picture is described in the document text

Adding a PDF Template to Your Report

Typically, the source for a PDF template is a predefined form from a third party, such as the government. If form fields have already been defined in the PDF, then you have two options for associating the XML data to the PDF form fields:

If the predefined PDF does not have form fields defined, or if you wish to design your own PDF template, then you must use Adobe Acrobat Professional to insert the form fields. You can then either name the fields according to the data source (no mapping will be required) or use BI Publisher's PDF mapping tool. For information on designing a PDF template and inserting form fields, see Creating a PDF Template.

Determining If a PDF Has Form Fields Defined

If you have the full version of Adobe Acrobat 5.0 or later:

  1. Open the file in Adobe Acrobat.

  2. Select the Text Field Tool (Adobe Acrobat Professional 6.0 users) or the Form Tool (Adobe Acrobat 5.0 users). This will highlight text fields that have already been defined. If no fields are highlighted then you must add the fields to the PDF. See Adding Markup to the Template Layout for instructions on inserting PDF form fields.

    The following figure shows a sample PDF form opened in Adobe Acrobat Professional 6.0. The Text Field Tool has been selected to display all the available form fields.

    the picture is described in the document text

If you do not have the full version of Adobe Acrobat 5.0 or later:

  1. Follow the instructions in Adding a Predefined PDF Form as a Template.

  2. If no highlighted fields display for mapping, or you cannot select a field, then you must add them before you can use BI Publisher's mapping tool. Adding form fields requires Adobe Acrobat 5.0 or later, or Adobe Acrobat Professional 6.0 or later. For more information, see Creating a PDF Template.

Adding a Predefined PDF Form as a Template

Prerequisites:

  1. From the Report Editor, select Layouts.

  2. Upload the PDF template file.

    From the Manage Template Files region, select Browse to locate the PDF file, and then select Upload.

    the picture is described in the document text

  3. Generate a sample data file.

    From the Report Editor or from the Reports page, select View. If no layouts are defined for your report, then the output type will default to xml, otherwise, choose data for the output type. Select Export. Save the results as an XML file to a local directory.

  4. Upload the sample data file.

    From the Report Editor, Layouts pane, in the Sample Data region, browse for and upload your sample data file.

  5. Map the PDF form fields.

    Once you have uploaded your template and sample data, the Map Form Fields button will become enabled.

    the picture is described in the document text

  6. Select Map Form Fields.

    The BI Publisher mapping tool will launch in a separate browser window.

    Note that as you mouse over the fields, the name of the field in the PDF form will display.

    the picture is described in the document text

  7. Click in the field on the PDF form that you want to map data to.

    A second window will launch, displaying the field names from the sample data that you loaded. Note that the form field selected is shown at the top of the dialog. If the field is already mapped, the dialog will display the name of the data field that it is currently mapped to. In the figure below, ANNUAL_SALARY is the name of the selected form field. It is shown as being mapped to ANNUAL_SALARY in the data (ANNUAL_SALARY = ANNUAL_SALARY).

    the picture is described in the document text

  8. Select the field from the Form Field Mapping dialog and then click Select. This will complete the mapping for the field.

  9. Repeat the selection process for each field that you want to map from the PDF template.

  10. To see a preview of your template with the sample data mapped to the fields, select Show Preview.

  11. When you have mapped all fields, select Submit to save your mapping file.

    Note that the PDF mapping file is saved in the report definition as a .map file.

  12. Select the New icon to create the definition for the new template.

Enabling Bursting

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:

Enabling a Report for Bursting

Prerequisite: A report defined in BI Publisher. The report data must contain an element by which the report will be split and an element by which the report will be delivered.

Enabling a report for bursting consists of the following steps:

Defining the Delivery Data Set

Based on the SQL query that you provide on the Bursting criteria page of the Report Editor, BI Publisher will build the delivery XML data set. The delivery XML data set contains the information to deliver your burst report appropriately to each recipient. The delivery data in this XML document is used as a mapping table for each Deliver By element. The structure of the delivery XML is as follows:

<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

Parameter Mapping

Channel Parameter 1 Parameter 2 Parameter 3 Parameter 4 Parameter 5 Parameter 6 Parameter 7
Email Email address cc From Subject Message Body Attachment (true/false)
Note that if your output format is pdf, you must set this parameter to "true" to attach the pdf to the email. 		Reply-To
Printer Printer Group Printer Number of copies Sides Tray    
Fax Fax server Name Fax Number          
WEBDAV Server Name Username Password Remote Directory Remote File Name    
File Directory File Name          
FTP Server Name Username Password Remote Directory Remote File Name    

Bursting Example

The following example shows bursting enabled for a report based on the Split By key CUSTOMER_NAME and the Deliver By key CUSTOMER_ID.

the picture is described in the document text

The report will be burst and delivered via e-mail. The template, template format, locale, output format, delivery channel, and customer e-mail address are all specified in elements from the delivery data source and will be returned by the query. The SQL to generate the delivery XML for this example is as follows:

select distinct
CUSTOMER_ID KEY,
CST_TEMPLATE TEMPLATE,
TMPL_TYPE TEMPLATE_FORMAT,
CST_LOCALE LOCALE,
CST_FORMAT OUTPUT_FORMAT,
CST_DEL_CHAN DEL_CHANNEL,
CST_EMAIL 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@nowhere.com' PARAMETER7
from customers

For information on running the report, see Scheduling a Report to Be Burst.

Accessing Reports via a URL

This section describes how to call a BI Publisher report via a URL from another application, for example from a portal or from an Application Express application.

Security Considerations

In the BI Publisher security model, reports are placed in folders and those folders are then secured to a role and a role assigned to a user. For a user to successfully access the report, you must ensure that the user is credentialed within BI Publisher to see it. There are two options for this:

Building the URL

The basic URL for a report is as follows: 

http://<server:port>/xmlpserver/<ReportDirectory>/<ReportName>.xdo

where

server:port - is the name of the server and port number where BI Publisher is running

xmlpserver - is a required string (the name of the application)

ReportDirectory - is the folder path to the report

Important: On the BI Publisher server, a report resides in a folder named for the report. For example, assume you have a report called Salary Report. On your BI Publisher desktop it is located in a folder of reports called Executive. Within Executive, it is located in a folder called Private. The path to this report would therefore be

Executive/Private/Salary+Report

Note that you must replace a space in the folder or report name with the + character.

ReportName.xdo - is the name of the report with the .xdo extension.

This will render the complete report inside the BI Publisher page with all the report controls. The default template, output and parameters will be used to render the report. For example:

http://xdopf.us.oracle.com:9999/xmlpserver/Executive/Salary+Report/Salary+Report.xdo

server:port - xdopf.us.oracle.com:9999

xmlpserver

ReportDirectory - Executive/Salary+Report

ReportName.xdo - Salary+Report.xdo

Specifying Parameters in the URL

If you want to specify parameters for your output report, such as the template, the output format, and any parameters defined for the report, you can add name/value pairs to the URL. The easiest way to generate the URL is to use the Export function from the BI Publisher View Report page. The URL generated will look similar to the basic URL described above, but the name/value pairs will be added.

For example:

http://xdopf.us.oracle.com:9999/xmlpserver/Executive/Employee+Salary+Report/Employee+Salary+Report.xdo?_xpf=&_xpt=1&_xdo=%2FExecutive%2FEmployee+Salary+Report%2FEmployee+Salary+Report.xdo&dept=10&_xt=Standard&_xf=html

The URL components through the report name are described in the previous section. The URL after the report name consists of: 

?_xpf=&_xpt=1&_xdo=%2FExecutive%2FEmployee+Salary+Report%2FEmployee+Salary+Report.xdo&dept=10=*&_xt=Standard&_xf=html

Note the following standard URL syntax:

? - denotes the first parameter

& - denotes each additional parameter

The BI Publisher parameters are as follows:

_xpf - required string for internal use

_xpt - defines whether to render the report in the full BI Publisher window (as above), or to render just the report document. Valid values are

_xdo - (optional) provides the path to the current report

dept - this is a parameter specific to the report as defined in the report definition. In this case the department for the data. Notice it takes the department ID. The parameter definition is to show the user the department name and then pass the ID to the query. You can have multiple parameters and their values in the URL.

_xt - this controls the template to be used. This is the template name, not the template file name. In this case, the template name is "Standard".

_xf - this controls the format of the output to be generated. Valid values are same as for the report: pdf, html, excel, rtf, or data.

Contents   |   Previous   |   Top of Page   |   Next

Oracle Logo
Copyright © 2003, 2008, Oracle and/or its affiliates. All rights reserved.