BI Publisher 11g
Oracle Business Intelligence Publisher is a reporting and publishing application that enables you to extract data from multiple data sources, create layouts for report data, and publish the report to numerous output formats. BI Publisher also enables you to schedule reports and deliver the reports to multiple delivery destinations required by your business.
Logging Into BI Publisher:
Enter the URL for BI Publisher in the browser window, which is of the format-
http://<hostname>: <port >/xmlpserver/
This opens the Oracle BI Publisher Enterprise login page-
We can open the BI Publisher through User Interface also.
Go to Administration tabàBI PublisheràManage BI Publisher.
Login as a user with BI Administrator privileges.
Go to Data SourcesàJDBC Connection and create a new Data Source.
Unlike 10g, where Report is a single entity and data sets are the part of Report definition, in 11g, Data Sets (which termed as Data Model in 11g) and Report definition are two separate entities.
BI Publisher report consists of three things:
ü The data query, which can typically be an SQL query, an MDX query, a web service call an HTTP call
ü One or more data templates, which lay out the results of the query on the page, and
ü The report definition (stored as an .XDO file), which references the query and the templates together with parameters, LOVs etc
Create a New Data Model.
Data Model Editor enables you to perform the following tasks:
ü Create Data Sets.
ü Query data.
ü Structure data.
ü Aggregate data.
ü Customize data.
ü Create calculations.
Click on Create New Data Model.
Select SQL query as the type of data set.
Check the Data Source which you created and write the SQL Query or Click Query Builder.
Click the tables on which you want to get the data and give the relationship between two tables by checking with the same column (by selecting the check boxes beside the column names).We can find a line at the top representing the join.
Check all the columns names which you want to display.
Go to Conditions tab and give the condition for the Prompt.
Check the SQL and Results tab and save the report.
It will ask the value for Prompt, give the value and click on save.
We can rename the column, assign null values to the columns in properties tab of the specified column.
Go to List Of Values and create a New List Of Value.
Write the query for the prompt.
Go to parameters and create a New Parameter.
After saving the Data Model click on XML symbol on the top right then you can get the XM L generated for the query.
Creating BI Publisher Template:
Click on New and create a new Report . Choose the existing Data Model which we created.
At the top we can see the name of the Data Model which we created.
Click on Basic TemplatesàBlank.
We will get a Layout editor with a Blank page.
Select and drag the elements from the left side of the screen.
We can create Data Table,Charts,Guages,Pivot Table in the Blank editor.
Save the report, Click on Return.
The saved report can be seen here.
Scheduling a Report:
In the Administration page, Goto System Maintenance à Scheduler Configuration.
In the Scheduler Configuration page that appears, click Test Connection in the Database Connection section to check if the scheduler is configured properly.
This should display the following message indicating the connection was successful.
In the Administration page, Goto DeliveryàEmail.
Click on Add Server.
Select the report to schedule and click on Schedule.
We will get the General Schedule screen.
In Output tab give the mail address of the recipient to whom you want to send the report.
Ensure that the Save Output check box is selected.
Ensure that the Make Output Public option is not selected (if you select this option, all users can see the output of your scheduled report) , and Save Data for Republishing option is selected ( so that you can later republish the report with a different layout and output types).
You can also add a delivery destination for the scheduled report. In the Destination section, select Email as theDestination Type and click Add Destination.
The schedule tab define the scheduling options.
You can also add Email Notification options, (as you have already defined Email delivery destination).
A message is displayed indicating that the job is submitted successfully. Click OK.