Oracle BI Publisher For Beginners
Oracle BI Publisher For Beginners
Oracle BI Publisher For Beginners
1. INTRODUCTION 6
3. GETTING STARTED 7
1
Oracle BI Publisher for Beginners
2
Oracle BI Publisher for Beginners
1. Introduction
This document describes how to generate various types of reports using Oracle BI Publisher. The
output format for the reports can be designed using Microsoft word or Adobe Acrobat tools. Oracle BI
Publisher allows creating reports from various data sources. The detailed steps for creating various reports
using Oracle BI Publisher are discussed in detail in the forthcoming sections.
Before starting, we need to have access to or must have installed the following:
Oracle Database 10g
Oracle BI Publisher 10.1.3.3 or above
Oracle BI Publisher Desktop
3
Oracle BI Publisher for Beginners
Oracle BI Publisher Desktop can be installed later by clicking the Template Builder Link in BI Publisher
after logging into BI Publisher.
After successful installation, the BI Publisher menu and BI Publisher toolbars are displayed in the MS
Word.
3. Getting Started
Logon to BI Publisher
If you have installed BI Publisher along with Oracle BI Enterprise Edition, then you can select All
Programs > Oracle Business Intelligence > BI Publisher from the Start menu
4
Oracle BI Publisher for Beginners
Otherwise use the URL for BI Publisher in a browser window which is of the format
http://<hostname>:<port>/xmlpserver/
(For example, http://chnmct139034d:9704/xmlpserver/)
Logon to BI Publisher with username and password credentials.
5
Oracle BI Publisher for Beginners
Note: As you have logged in as the Administrator, the welcome page displays the Reports, Schedules,
and Admin tabs. For the users who do not have administrator privileges, the Admin tab will not be
displayed.
Click the Admin tab. Click JDBC Connection found under the Data Sources section of the page.
6
Oracle BI Publisher for Beginners
In the JDBC Data Sources screen that is displayed click the Add Data Source to create a new data
source.
7
Oracle BI Publisher for Beginners
Before you create a query based on Oracle Database, you need to define the connection as explained
above.
You can also add a new data source and define the connection details.
To create a BI Publisher report based on a query directed to Oracle Database, perform the following
steps:
In this section, we can learn how to view the reports in XML format. In the forthcoming sections we can learn
how to view the reports in various formats.
8
Oracle BI Publisher for Beginners
Enter the data model name select the type as SQL Query
Choose the data source
9
Oracle BI Publisher for Beginners
The output could be viewed only in the data format, as we have not created any template for the report.
Click the view button to view the output. The output generated will be in the form of XML.
10
Oracle BI Publisher for Beginners
You can also edit the report by clicking the Edit link.
After editing, Save the report and view it as we have discussed already.
Here we can view the above created report in various formats using Template.
Select the Layouts, and click the Generate button to create a new RTF template based on the default
data model.
A default template (New_Template.rtf) will be generated.
11
Oracle BI Publisher for Beginners
By clicking view one can see the output using default template.
Similarly we can view the report in various formats by selecting the desired output format.
12
Oracle BI Publisher for Beginners
Open a word document and select Add-Ins->Oracle BI Publisher and log into by giving username and
password.
13
Oracle BI Publisher for Beginners
14
Oracle BI Publisher for Beginners
15
Oracle BI Publisher for Beginners
If you don’t want to display a field, move them to left and you can also sort them by clicking up or down
button.
If one needs Group by function then you can select the column to be grouped.
Click next.
If one need to sort a column then select the column to be sorted .Click next.
16
Oracle BI Publisher for Beginners
If you need to change the labels of a field, it can be done here. Click Finish and save the template.
The heading for the report can be given in the Header and page number in the footer.
17
Oracle BI Publisher for Beginners
Double click on Ename and go to Properties tab and click on Word Properties.
18
Oracle BI Publisher for Beginners
In word properties, select the text format. Also the Maximum length can be specified, if needed.
Similarly double click on date and select the date type and date format if needed.
19
Oracle BI Publisher for Beginners
Select the row and right click →Borders and Shading→ Shading.
Select the color, go to apply to column and select cell and give ok.
20
Oracle BI Publisher for Beginners
Now upload the created template by using the option ‘Browse and upload’ in BI Publisher.
21
Oracle BI Publisher for Beginners
Similarly the report can be viewed in various other formats available in the dropdown list.
22
Oracle BI Publisher for Beginners
23
Oracle BI Publisher for Beginners
Now upload the created template by using the option ‘Browse and upload’ in BI Publisher.
24
Oracle BI Publisher for Beginners
Select the table ‘EMP’ and ‘DEPT’ and check the required fields.
Define a join between these two tables as follows:
Click the box beside the DEPTNO column in the DEPT table. (See the following screens.)
Similarly click the box beside the DEPTNO column in the EMP table.
These boxes when marked for joins turn to light gray.
Also note that a fine line joining the tables appears in the Model canvas.
25
Oracle BI Publisher for Beginners
Create a new template from MS word (Refer section 4.3 for detailed steps)
From MS Word application, log in to BI Publisher.
26
Oracle BI Publisher for Beginners
This displays the Open Template window. Ensure that BI Publisher is selected from the Workspace
drop-down list
Navigate and select the Employee_Location_Details report that you have created from My Folders>
BI_Publisher_LabGuide. Click Open Report.
Navigate and select the Employee_Location_Details report that you have created from My Folders>
In the Word document that loads this report, select Insert> Table Wizard on the BI Publisher
toolbar to define a
format for the table data in the query.
Create a template as given below:
Change the format for the Hire Date data field to ‘Date’ type and format to ‘d-MMM-yy’
Insert header and footer for the created report.
The template designed should look like as shown below.
27
Oracle BI Publisher for Beginners
After making the necessary changes, upload the template by selecting Add-Ins->Oracle BI Publisher-
>Upload Template As
A dialog box opens prompting to save the template in RTF. Click OK.
28
Oracle BI Publisher for Beginners
A dialog box pops, asking for the Template name. Enter the Template name. The same name will be
displayed in the BI Publisher screen.
After successfully uploading, a dialog box pops stating that the template has been added to the
report. Click OK.
29
Oracle BI Publisher for Beginners
Save the report and view the output. You can see the template name as the name uploaded from MS
Word. View the output in different format.
Here we create a new template for creating chart and upload it in the same
Employee_Location_Details report.
30
Oracle BI Publisher for Beginners
Drag and drop the Ename for Values and Loc for Labels. Give an appropriate title.
There are various types of charts available like Bar graph, Pie Chart, Line Graph, Area Graph, Ring
Graph, Pareto Graph, Radar Graph etc. Select the one needed.
After choosing the type, select the style.
31
Oracle BI Publisher for Beginners
32
Oracle BI Publisher for Beginners
Template can also be uploaded from the BI Publisher. Browse the RTF template that has been saved
and upload it into the BI Report by clicking the upload button as shown below.
33
Oracle BI Publisher for Beginners
Enter the layout name and select the template, template type and output format.
Save the report and view the output. You can see the template name as the name uploaded from MS
Word.
34
Oracle BI Publisher for Beginners
Here we create a new template for building Pivot table and upload it in the same
Employee_Location_Details report.
Drag and drop the Row values into the Layout region as shown below and click OK.
35
Oracle BI Publisher for Beginners
Change the word properties for all fields by double clicking on the field.
Change the aggregate function in the code from sum to count.
Click on the Properties tab and change the format and click OK.
36
Oracle BI Publisher for Beginners
Create a new report. (Refer section 4 for detailed steps to create a new report)
Click on Edit link to modify the report.
Create a new Datamodel.
37
Oracle BI Publisher for Beginners
Enter the data model name select the type as SQL Query
Start the MS Word application from the program menu. From the Oracle BI Publisher menu, select
Log On.
38
Oracle BI Publisher for Beginners
In the Login screen that appears, enter Administrator as the Username and Password, and click
Login.
The first time when you connect, you may be asked for the report server URL, enter the URL which is
of the format: http://<Host>:9704/xmlpserver
This displays the Open Template window. Ensure that BI Publisher is selected from the Workspace
drop-down list.
Navigate and select the Employee_Details report that you have created from My Folders>
BI_Publisher_LabGuide. Click Open Report.
39
Oracle BI Publisher for Beginners
Insert header and footer for the created template by selecting the Insert->Header and by selecting
Insert->Footer as shown below.
40
Oracle BI Publisher for Beginners
Save the report and view the output. You can see the template name as the name uploaded from MS
Word. View the output in different format.
41
Oracle BI Publisher for Beginners
Enter the data model name choose the type as SQL query
42
Oracle BI Publisher for Beginners
We need to create a parameter in detail report to fetch the value from Employee Details report.
Click on Parameters New
Choose appropriate data type and parameter type based on your need.
43
Oracle BI Publisher for Beginners
Create a new template design (Refer Section 4.3 for detailed steps for creating the template)
This displays the Open Template window. Ensure that BI Publisher is selected from the Workspace
drop-down list.
Navigate and select the Employee_InDetail report that you have created from My Folders>
BI_Publisher_LabGuide. Click Open Report.
In the Word document that loads this report, select Insert> Table Wizard on the BI Publisher
toolbar to define a format for the table data in the query.
This displays the Table Wizard. Select Form and click Next.
In the next step, ensure that ROWSET/ROW is selected as the Grouping Field, and click Next.
In the next step, click to include all the available columns in the table. Click Next.
In the next step, select any field from the Group by drop-down list if needed else click Next.
In the next step, select any field from the Sort By drop-down list if needed.
In the next step you can edit the labels of the columns as required. Click Finish.
The template you created looks like this:
44
Oracle BI Publisher for Beginners
To change the format for the HireDate data field, double click on the field and select the formatting
type as Date and format as shown below.
Insert header and footer for the created template by selecting the Insert->Header and by selecting
Insert->Footer as shown below.
45
Oracle BI Publisher for Beginners
After making the necessary changes, upload the template by selecting Add-Ins->Oracle BI Publisher-
>Upload Template as
A dialog box opens prompting to save the template in RTF. Click OK.
46
Oracle BI Publisher for Beginners
A dialog box pops, asking for the Template name. Enter the Template name. The same name will be
displayed in the BI Publisher screen.
After successfully uploading, a dialog box pops stating that the template has been added to the
report. Click OK.
47
Oracle BI Publisher for Beginners
Save the report and view the output. You can see the template name as the name uploaded from MS
Word. View the output in different format.
Select Layouts Browse the template which you have saved and upload it.
Click View
48
Oracle BI Publisher for Beginners
No rows will be displayed in the output because there are no values assigned for the Hidden
parameter.
Copy the path of the report as shown below:
Note: If you want to check the output, set the default value of the parameter to some existing
Empno and view the output later delete the default parameter.
49
Oracle BI Publisher for Beginners
Parameter has to be passed on to the detail report by appending the highlighted syntax at the end of
the detail report URL.
At the end of the detail report URL include the following syntax
http://chnmct139034d:9704/xmlpserver/~administrator/BI_Publisher_LabGuide/
Employee_InDetail/Employee_InDetail.xdo?EMPNO= {EMPNO}
Where EMPNO (on the left hand side) is the parameter name created in the detail report and EMPNO
(on the right hand side) is the alias name for the column “EMPNO” in the Employee Details summary
report.
Save the template.
The template needs to be refreshed after placing the FORM field. Go to BI_Publisher_LabGuide
Employee_Details browse the template upload
Save the report View.
The output is shown below
50
Oracle BI Publisher for Beginners
51
Oracle BI Publisher for Beginners
Enter the data model name select the type as SQL Query
Choose the data sources and click the Query Builder button.
52
Oracle BI Publisher for Beginners
Select the table ‘EMP’ and ‘DEPT’ and check the required fields.
Define a join between these two tables as follows:
Click the box beside the DEPTNO column in the DEPT table. (See the following screens.)
Similarly click the box beside the DEPTNO column in the EMP table.
These boxes when marked for joins turn to light gray.
Also note that a fine line joining the tables appears in the Model canvas.
Click Conditions, and move the DEPTNO, to appear first in the list (as shown in the screen below).
53
Oracle BI Publisher for Beginners
You can use these icons beside each column to move the columns up or down.
54
Oracle BI Publisher for Beginners
Create a new template using Microsoft word. (Refer section 4.3 for detailed steps for creating the
template)
Navigate and select the Based on Oracle DB report that you have created from My Folders>
BI_Publisher_LabGuide. Click Open Report.
Using the table wizard create a template as shown below:
55
Oracle BI Publisher for Beginners
To change the format for the HireDate data field, double click on the field and select the formatting
type as Date and format. For example, d-MMM-yy and click OK.
Similarly, to change the format for the Salary, double click on the field and select the Type as
Number and Number format to any of the format as needed. For example, $#,##0.00 to display the
salary in dollar format and click OK.
56
Oracle BI Publisher for Beginners
Similarly, change the format for commission field and click OK.
Left align the Salary and Commission fields.
Insert header and footer for the created template by selecting the Insert->Header and by selecting
Insert->Footer.
The template designed should look like as shown below.
57
Oracle BI Publisher for Beginners
After making the necessary changes, upload the template by selecting Add-Ins->Oracle BI Publisher-
>Upload Template As
A dialog box opens prompting to save the template in RTF. Click OK.
58
Oracle BI Publisher for Beginners
A dialog box pops, asking for the Template name. Enter the Template name. The same name will be
displayed in the BI Publisher.
After successfully upload a dialog box pops stating that the template has been added to the report.
Click OK.
59
Oracle BI Publisher for Beginners
Save the report and view the output. You can see the template name as the name uploaded from MS
Word. View the output in different format.
60
Oracle BI Publisher for Beginners
Create a new report ‘Emp_GrpBy_Function’. (Refer Section 4 for detailed steps for creating the
Report)
Using Query builder generate the below query.
61
Oracle BI Publisher for Beginners
62
Oracle BI Publisher for Beginners
Create a new template using Microsoft word (Refer section 4.3 for detailed steps for creating the
Template).
Select the table for which the template is going to created i.e. ‘Emp_GrpBy_Function’.
Using Table Wizard create a template as shown below.
After formatting the template as explained in section 4.3 , the final template will look as below.
63
Oracle BI Publisher for Beginners
Now upload the template created by using the option ‘Browse and upload’ in BI Publisher.
64
Oracle BI Publisher for Beginners
Click on Reports tab and create a new report ‘Simple_Parameter’ .(Refer section 4 for detailed steps for
creating the report.
Using Query Builder generate the below query:.
65
Oracle BI Publisher for Beginners
66
Oracle BI Publisher for Beginners
Edit the query to give the bind variable, i.e. the name of the parameter.
67
Oracle BI Publisher for Beginners
If you want to give a default value give them and select the parameter type.
68
Oracle BI Publisher for Beginners
The default value will appear in the text box and by clicking ‘view’ the output is generated.
69
Oracle BI Publisher for Beginners
Edit the query to give the bind variable, i.e. the name of the parameter.
70
Oracle BI Publisher for Beginners
71
Oracle BI Publisher for Beginners
Enter the Employee Numbers separated by comma and Click ‘View’ to see the output..
Give the name for the parameter and select the data type as ‘Date’
72
Oracle BI Publisher for Beginners
Edit the query to give the bind variable, i.e. the name of the parameter.
73
Oracle BI Publisher for Beginners
If you want give default ‘date’ then enter the date in Default value column.
Give the format for date and also enter the starting and ending Date.
74
Oracle BI Publisher for Beginners
If a default date is given in default value column, then the date will appear in the prompt box.
Click ‘View’ to see the output.
If default date is not given then click ‘View’ and enter the date in the prompt box.
75
Oracle BI Publisher for Beginners
Edit the query to give the bind variable, i.e. the name of the parameter.
76
Oracle BI Publisher for Beginners
Now enter the hidden parameter value in the default value box.
77
Oracle BI Publisher for Beginners
By clicking ‘View’ the output is generated for the hidden parameter value.
Click on List Of Values and click ‘New’ to create a new List of Values.
78
Oracle BI Publisher for Beginners
79
Oracle BI Publisher for Beginners
80
Oracle BI Publisher for Beginners
Edit the query to give the bind variable, i.e. the name of the parameter.
81
Oracle BI Publisher for Beginners
82
Oracle BI Publisher for Beginners
To select more than one value from the List of Values, check the option ‘Multiple Selection’.
To select All values from the LOV, then check the option ‘can select all’.
To pass null values check that field or to pass all values check that field.
83
Oracle BI Publisher for Beginners
If more than one parameter is created and if one depends on the other then use the last option to refresh the
parameters.
84
Oracle BI Publisher for Beginners
85
Oracle BI Publisher for Beginners
Scheduling is a very important feature for any reporting system, which enables you to schedule
long-running reports, to be run at a specified time and date. This enables you to select a time and
date at which the load on the system is low, to run your daily/weekly and/or monthly reports. In this
topic, you configure the BI Publisher scheduler to schedule the reports and view the report history
and saved output.
On the Admin tabbed page, click the Scheduler Configuration link in the
System Maintenance section.
86
Oracle BI Publisher for Beginners
On the Scheduler Configuration page: Select Oracle 10g from the Database
Type drop-down list. Enter Connection String in the format:
jdbc:oracle:thin:@myhost:1521:orcl
Enter the Username and Password credentials (this is the scheduler user
that you must have created as per step 4 of the prerequisites).
Ensure that oracle.jdbc.driver.OracleDriver is selected as Database Driver
Class.
Click Test Connection.
87
Oracle BI Publisher for Beginners
After the tables are installed in the database, the below message is displayed:
Logon to BI Publisher (if not logged) and open the Report tab.
Select the Schedule link displayed below the report which you need to schedule.
88
Oracle BI Publisher for Beginners
On the schedule report page, select the template type and the format for the report.
Enter Employee_Location_Details-Scheduled as the Job Name, and select appropriate language and
time options as per your requirements. Also, ensure that the Save Data for Republish and Save Output
options are selected.
In the Time section of the scheduled report page, select any one of the four option to run the
scheduled report as shown below:
Run Immediately – Runs the scheduled report immediately after scheduling the report.
Run Once – Runs the scheduled report once at the selected date and time after scheduling the report.
89
Oracle BI Publisher for Beginners
Run Daily/Weekly – Runs the scheduled report weekly once or daily once at the selected day of
week and time. Also select the Active Start Date and End Date.
Run Monthly – Runs the scheduled report monthly once at the selected month, day and time. Also
select the Active Start Date and End Date.
After selecting the option click submit which is found on the top and bottom right corner of the
scheduled report page.
After submitting the scheduling process, a confirmation message can be seen along with the Report
Schedules.
90
Oracle BI Publisher for Beginners
Click the History tab to view the report history. Note that the Data XML and Document columns are
shown in the history table.
Here you can see the status of the report as ‘Running’. Once the report has run the status
automatically changes to ‘Success’(Keep refreshing the page)
Click the icon in the Document column to see the saved report.
91
Oracle BI Publisher for Beginners
92
Oracle BI Publisher for Beginners
Logon to BI Publisher (if not logged) and open the Report tab.
Select the Schedule link displayed below the report which you need to schedule.
On the schedule report page, select the template type and the format for the report.
Select appropriate language and time options as per your requirements. Also, ensure that the Save Data for
Republish and Save Output options are selected.
In the Time section of the scheduled report page, select any one of the four option to run the scheduled
report as shown below:
93
Oracle BI Publisher for Beginners
Run Immediately – Runs the scheduled report immediately after scheduling the report.
Now click the radio button for scheduling the reports to Email.
Enter the Mail Id, Subject and Mail body details.
If you need to send to more than one person then click on ‘Add Destination’.
After selecting the option click submit which is found on the top and bottom right corner of the scheduled
report page.
After submitting the scheduling process, a confirmation message can be seen along with the Report
Schedules.
94
Oracle BI Publisher for Beginners
Click the History tab to view the report history. Note that the Data XML and Document columns are shown
in the history table.
Here you can see the status of the report as ‘Running’. Once the report has run the status automatically
changes to ‘Success’(Keep refreshing the page)
Click the icon in the Document column to see the saved report.
95
Oracle BI Publisher for Beginners
Bursting is an important and useful feature of BI Publisher, which enables you to split a single report
based on a key .You, can apply a different template, output format, delivery method, and locale to
each split segment of your report.
Following are some example scenarios where bursting can be implemented: Generation of pay slips to
all employees based on one extract and delivered via e-mail
On the Report Properties screen, click the Bursting node in the Report section on the left, to define
bursting properties for the report.
Bursting_Query
Before doing the bursting process, create a folder for e.g. D:\Salary.
96
Oracle BI Publisher for Beginners
Mention the path of the created share folder in the Bursting query as shown below:
Ensure that you saved the report after defining the Bursting Properties, and click Schedule to
schedule this report to burst to the file location defined.
This takes you to the Schedule page. Click Schedule a New Job to define the scheduling job.
97
Oracle BI Publisher for Beginners
Click the Schedules tab and then click History to see whether the schedule was run successfully.
(Click the refresh icon )
98
Oracle BI Publisher for Beginners
Now go to the D:\Salary folder (the delivery destination folder you provided in the query). You can
see that the PDF files, which have <DName>. PDF as the titles, are bursted to this file location.
99
Oracle BI Publisher for Beginners
Now select the dummy column in the split by and deliver by column, so that the file will save as a single
file.
100
Oracle BI Publisher for Beginners
101
Oracle BI Publisher for Beginners
102
Oracle BI Publisher for Beginners
12 Sample Templates.
103