Fusion Apps Custom BI Report Creation Part III

Till now I have completed how to create the data model of your report. I have covered that in the last two posts

1. Fusion Apps Custom BI Report Creation Part I
2. Fusion Apps Custom BI Report Creation part II

So now after completing the above two you have XML data file which you saved in your local machine. The next step is to Create Custom Reports.

Create a custom report when the reports delivered with your Oracle Fusion application do not provide the data you need; or, if you want to use a predefined data model and change other properties of the report.

Here we go

Click on the New Option and Choose “Report” and in the following screen do select the data model

As you have already created the data model, select Use existing data model

Browse through catalog and select the data model you have already created.

Select Use Report Editor to build your own report layout and click finish.

Specify the report name. This is the name by which it will be called from ESS if you want to run it from there.

Once done, click on upload the report template. In this example I have create a simple RTF template using MS Word with add on for XML Publisher.

Now if you want to add parameters create them in this step. Some parameters may be derived and some may be provided by users. So while creating parameters Check Show for those which you want users to enter.

Now specify the report properties. Those who are from EBS background may recall that this is almost same like Concurrent Program Creation Screen. Here you specify different controls about of the report.

Click on view report button to get the sample output.

Bursting is a process of splitting data into blocks, generating documents for each block,and delivering the documents to one or more destinations. The data for the report is generated by executing a query once and then splitting the data based on a "Key" value. For each block of the data, a separate document is generated and delivered. Using BI Publisher's bursting feature you can split a single report based on an element in the data model and deliver the report based on a second element in the data model. Driven by the delivery element, you can apply a different template, output format, delivery method, and locale to each split segment of 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, splitting out individual cost center reports to the appropriate manager

People who are from eBusiness Background may recall that we used to do this by writing a Burst Control File and attach it with the Data Definitions in XML Publisher Administrator Responsibility.
The burst control file looks like

What is the Bursting Definition?
A bursting definition is a component of the data model. After you have defined the data sets for the data model, you can set up one or more bursting definitions. When you set up a bursting definition, you define the following:
• The Split By element is an element from the data that will govern how the data is split. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_ID. The data set must be sorted or grouped by this element.
• The Deliver By element is the element from the data that will govern how formatting and delivery options are applied. In the invoice example, it is likely that
each invoice will have delivery criteria determined by customer, therefore the Deliver By element may also be CUSTOMER_ID.
• The Delivery Query is a SQL query that you define for BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details.

The bursting query is a SQL query that you define to provide BI Publisher with the required information to format and deliver the report. BI Publisher uses the results from the bursting query to create the delivery XML. The BI Publisher bursting engine uses the delivery XML as a mapping table for each Deliver By element. The structure of the delivery XML required by BI Publisher is as follows:

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 to apply. Note that the value is the Layout
name (for example, 'Customer Invoice'), not the template file name (for example,
invoice.rtf).
• LOCALE - is the template locale, for example, 'en-US'.
• OUTPUT_FORMAT - is the output format.
 Valid values are:

• HTML
• PDFZ - zipped PDF
• PDF
• RTF
• EXCEL
• EXCEL2000
• MHTML
• PPT
• PPTX - PowerPoint 2007
• XSLFO
• XML
• CSV
• ETEXT

SAVE_OUTPUT - indicates whether to save the output documents to BI Publisher
history tables that the output can be viewed and downloaded from the Report Job
History page.
Valid values are 'true' (default) and 'false'. If this property is not set, the output will
be saved.
DEL_CHANNEL - is the delivery method. Valid values are:

• EMAIL
• FAX
• FILE
• FTP
• PRINT
• WEBDAV

OUTPUT_NAME - is the name that will be assigned to the output file in the report job history.

Delivery parameters by channel. The values required for the parameters depend on the delivery method chosen. The parameter values mappings for each method are shown in the following table. Not all delivery channels use all the parameters.

Delivery Channel                                PARAMETER Values
Email                                                  PARAMETER1: Email address
                                                          PARAMETER2: cc
                                                          PARAMETER3: From
                                                          PARAMETER4: Subject
                                                          PARAMETER5: Message body
                                                          PARAMETER6: Attachment value ('true' or 'false'). If your output               format is PDF, you must set this parameter to "true" to attach the PDF to the e-mail.
                                                          PARAMETER7: Reply-To
                                                          PARAMETER8: Bcc
                                                          (PARAMETER 9-10 are not used)

I have used only Email. All of the above values are set using the query dynamically. So if the Key element is having same value as specified by Deliver By 

SELECT department_id AS "KEY“  ---- As Department ID is our Deliver By element

      ,'DemoReport' AS TEMPLATE ---- Report Name

      ,'en-US' AS LOCALE  ---- Locale

      ,'PDF' AS OUTPUT_FORMAT ---- Output file will be in PDF format

      ,'EMAIL' AS DEL_CHANNEL ---- Sending bursting Output Via EMAIL

      ,‘to_mail_id@abc.com' AS PARAMETER1 ---- TO

      ,‘cc_mail_id@abc.com‘ AS PARAMETER2 ---- CC

      ,' from_mail_id@abc.com ' AS PARAMETER3 ---- FROM

      ,'You Have got a Mail' AS PARAMETER4 ---- Subject

      ,'Hey!! Bursting Successfull!!' AS PARAMETER5 ---- Body

      ,'true' AS PARAMETER6 ---- We want to attach PDF output in mail

      ,' reply_to_mail_id@abc.com ' AS PARAMETER7 ---- REPLY TO

FROM   xxfusion.departments

Select Report Properties and enable the bursting and select bursting definition.

This completes report creation and associating bursting definition with it. In the next series of this topic I will cover how to create Enterprise Scheduler Services Job to run this report and schedule it.......

Cheers.....................