BI Publisher 10g Bursting
This article gives a complete example of BI Publisher Bursting. This example doesn’t use a control file for report bursting, but a SQL Query which provides all the necessary parameters required for bursting a report.
I am going to start with Installation of BI Publisher Enterprise 10.1.3.4.2 on Windows-7 machine. Trying to install this version (bipublisher_desktop_windows_x86_101342.zip) on Windows-7 machine will give you this error.
Checking installer requirements…
Checking operating system version: must be 4.0, 5.0, 5.1, 5.2 or 6.0. Actual 6.1 Failed
One of the ways to get around this is, edit install\oraparam.ini file and change [Certified Versions] section and make it look like this. With this change, installation should work fine.
#You can customise error message shown for failure,
provide value for CERTIFIED_VERSION_FAILURE_MESSAGE
Create a schema/user called bischeduler on the database.
CREATE USER bischeduler IDENTIFIED BY bischeduler DEFAULT TABLESPACE USERS;
GRANT CONNECT, RESOURCE, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE,
CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM,
CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, CREATE SESSION To bischeduler;
After you login to the BI publisher, go to Admin->Scheduler Configuration. Give connection details of the database and user you just created and do “Test Connection” and if it succeeds, do “Install Schema”. This will create the necessary database object in the schema needed by Quartz Scheduler used by BI Publisher engine.
You will have to restart BI Publisher in order for this connection to take effect.
Assuming that you have installed BI Publisher under D:\BIPublisherHome, this is how you can restart BI Publisher.
Make sure “demo” database is correctly configured under Admin->JDBC. Point to any Oracle database which comes with HR and OE schemas. Most of the reports which come pre-packaged with BI Publisher fetch data from OE schema. Click on “demo” hyperlink shown below
And set it properties as shown belowAnd set it properties as shown below
Report which we are going to use for Bursting is going to be fetching data from HR schema.
Will start with the report creation.
Under “Data Model” for the report, create a new Data Model of type “SQL Query”. Choose data source as “demo” and use following SQL Query. Attach this Data Model with the report.
select EMPLOYEES.EMPLOYEE_ID as EMPLOYEE_ID,
EMPLOYEES.FIRST_NAME as FIRST_NAME,
EMPLOYEES.LAST_NAME as LAST_NAME,
EMPLOYEES.EMAIL as EMAIL,
EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER,
EMPLOYEES.HIRE_DATE as HIRE_DATE,
EMPLOYEES.JOB_ID as JOB_ID,
EMPLOYEES.SALARY as SALARY,
EMPLOYEES.COMMISSION_PCT as COMMISSION_PCT,
EMPLOYEES.MANAGER_ID as MANAGER_ID,
EMPLOYEES.DEPARTMENT_ID as DEPARTMENT_ID,
JOBS.JOB_TITLE as JOB_TITLE,
DEPARTMENTS.DEPARTMENT_NAME as DEPARTMENT_NAME
from HR.JOBS JOBS,
order by DEPARTMENTS.DEPARTMENT_ID
Under “Layout”, create a new layout called “Employee Template 1” and upload the rtf template file for the report. Figure below shows contents of layout file. This was created in Microsoft Word.
|Emp ID||First Name||Phone Number||Department||Salary|
| <?SALARY?> <?xdoxslt:set_variable
($_XDOCTX, ’RTotalSalary’, xdoxslt:get_variable
iWareLogic Technologies Pvt. Ltd.
Baner, Pune 411045
Once this is done, you can view the report, this is how it will look like.
The report generated list of employees and “sum of salary” as the last row.
Before we start with bursting configuration, let us say that we want this employee report to be generated on per department basis, i.e. there should be as many reports as there are department and each report should show list of employees only in that department along with sum of their salary. For Bursting to work, scheduler has to be correctly configured which we already covered in section “Scheduler Configuration”. Now go to Bursting section of the report and configure it as shown below.
“Split by” is set as “/ROWSET/ROW/DEPARTMENT_ID” which is XPath expression for choosing DEPARTMENT_ID from the XML. In Order to see how this XML data, which is fed to Layout Template, just go to View report, choose “data” and click on view.
“Deliver by” is also set to “/ROWSET/ROW/DEPARTMENT_ID”. Value chosen by this XPath expression acts as a co-relation KEY between the report data and the SQL Query which we are going to be using for specifying bursting delivery details.
SELECT distinct(department_id) KEY,
‘Employee Template 1’ TEMPLATE,
(select department_name from hr.departments d
where d.department_id = e.department_id) || ‘.pdf’ PARAMETER2
FROM hr.employees e
where e.department_id is not null
If you run this query, you will see output like.
This query specifies all the parameters necessary for bursting the report. Column names of this query have to be “KEY”, “TEMPLATE”, “DEL_CHANNEL” etc. DEL_CHANNEL stands for Delivery Channel. Following table lists all possible delivery channels and additional parameters which you have to specify depending upon the delivery channel you choose. Example above is using “FILE” delivery channel and it needs Parameter1 to be Directory where reports will be saved and Parameter2 will be name of the report.
|Email address||CC||From||Subject||Message body|
|Printer||Printer Group||Printer||Number of copies||Sides||Tray|
|Fax||Fax Server Name||Fax Server Number|
|WEBDAV||Server Name||Username||Password||Remote Directory||Remote File Name|
|FTP||Server Name||Username||Password||Remote Directory||Remote File Name|
Parameter6 and Parameter7 are applicable only when delivery channel is EMAIL and their values mean,
“Attachment true/false(For PDF, always set this parameter to true)” and “Reply-To” respectively.
Once Bursting parameters are set, click on “Schedule” for the report and choose following options
Important options are “Run Immediately” and “Burst Report”, click on Submit. Once you submit this schedule, you will contents of folder c:\temp\yogesh\publisher. In my case, I saw 11 reports generated as there were 11 distinct departments. Please note, if you don’t put where e.department_id is not null in the SQL Query given for bursting, scheduler will not run and you won’t get the reports. In the HR.EMPLOYEES table there is one employee with NULL DEPARTMENT_ID and bursting SQL query does not like NULL to be present in the KEY column.
And see below contents of Accounting.pdf. As you can see this report only contains employees belonging to accounting department.
For your reference, you can download this zip file which has
- The report xdo file (Employee_Report.xdo)
- Layout template file (employee-template.rtf)
- XML data of the report (employee_query.xml)
- All the pdf files generated after bursting
Know more about : BI Publisher 11g Bursting.