Go back to view the From BI Server report , and click Analyzer for Excel.

You will be prompted to save or open the <report name>.xls file.
Click Open to open the file, and select Enable Macros from the Microsoft Excel dialog box.

Note: You must enable macros to use the Analyzer for Excel.

The report data is rendered in the MS Excel application window and the Oracle BI Publisher menu appears on the Excel menu bar.
Note that the data is the result of the report query with no template and with default filtering applied.

You can now manipulate the data as you want in Excel.

Note: If the report has parameters, the parameter names appear at the top of the worksheet, but you must log in to apply new parameter values.

You must log in to enable all the menu options in the BI Publisher menu.
Log in as Administrator from the BI Publisher menu in MS Excel.

Note: The first time you use the Analyzer for Excel, or if you do not have the latest version of Analyzer for Excel, you will be prompted to install the latest version. Follow the instructions and install the Analyzer. Also, If you do not have Microsoft .NET Framework 2.0 installed on your computer, you will be prompted to download it.

After logging in to BI Publisher, you can see that the various options in the BI Publisher menu are active. Using these options, you can perform tasks such as viewing the report online, seeing the parameters, updating a template, uploading a new template, and so on.
Select the Oracle BI Publisher> Show Report Parameters menu option. This displays the available templates and updatable parameters (if the report has parameters) for the report in a toolbar.

From the template toolbar in Excel, select Template1 and click Refresh Formatted Data.

You can see that a worksheet is added with the name <report name>.html that shows the data of the report using the template selected.

You can make modifications in the Excel template to update the template or add it as a new Excel template.
For example, you can add a chart in the From BI Server. html worksheet, and add it as a new template.
Select the Insert > Chart menu option in Excel.

In step 1, select Pie as the chart type and select the subtype as shown:

Accept the default options in step 2.

In step 3, change the chart title to Product Sales Revenues.

In step 4, select the option to represent the chart in the From BI Server.html sheet (as shown below) , and click Finish.

Note that the chart is added in the sheet. Adjust the position of the chart so that it is placed at the top of the table in the From BI Server.html Excel sheet. The sheet looks like this:

Now you can add this as a new Excel template for the BI Publisher report.
Select the Oracle BI Publisher > Add as New Excel Template menu option.

Enter New Excel Template1 as the name and click OK.

The following message is displayed:

You can save this file with an appropriate name on your file system. Close the Excel application.

(If not logged in) Log in to BI Publisher as Administrator, and open the From BI Server report from
My Folders > Learn in view mode.
You can see that the Excel template you have uploaded is displayed in the Template drop-down list.
Select the New Excel Template1 from the drop-down list. Click View.

Select the option to enable macros in MS Excel to open the report.
Observe that the From BI Server.html sheet consists of the chart you added to the template before.

Similarly, you can add multiple Excel templates in different formats or update an existing template format too. Note that you can also perform tasks such as viewing a report online, browsing for reports online, and so on by using the BI Publisher menu in Excel.

Go back to the View mode of the From BI Server report.
Click Configure.

This opens the Runtime Configuration page, observe the settings on this page.
In the PDF Output section of the page, do the following:
Set True as the value for the Enable PDF Security option.
Enter Oracle as the password for both Open Document password and Modify permission password.
Set True as the value for both Disable document modification and Disable Printing options. (See the screenshot below.)
Click Apply to apply the settings.

Scroll down and see other settings. Also, note that font settings can also be modified from the Font Mappings tabbed page.

Now go back to the View mode of the From BI Server report.
Select Template1 and PDF as the report viewing options. Click View.

You are prompted for a password to open the PDF document.
Enter Oracle as the password and click OK.

The report is now displayed as PDF:

If you have time, try out the other configuration settings.

Log in to BI Publisher as Administrator (if not logged in), and click the Admin tab to open the Admin page.

Click Delivery Configuration. This opens the Delivery Configuration page. Observe the various delivery media that can be configured for the reports.
Click the Email tab.

On the Email configuration page, click Add Server.

On the Add Server page, enter Email Server as the Server Name; enter your host name (where you have installed the ArGo Soft mail server)—for example, myMailServer.myCompany.com; enter the port number for the mail server; and enter the username and password details.
After entering all the appropriate details, click Apply.

After the server is added, it appears on the Email page of Delivery Configuration.

Note: You can add multiple email servers, and set one of them as the default mail server by selecting the Set as Default option. Also, you can configure FTP server, printer, and so on.

(If not logged in) Log in to BI Publisher as Administrator, and open the Category Sales and Profits for last 12 months report in View mode from My Folders > Learn.
(Select Template1 and PDF as the viewing options.)
The report with the chart and table is displayed. Click Schedule to schedule this report.

On the Schedule Report page:
Select Template1 as the template and PDF as the Format.
Enter Category Sales and Profits for last 12 months -Scheduled as the Job Name.

Scroll down to see other settings.
In the Notification section, enter the mail ID of the user for whom you have configured the mail server (for example:user@myMailServer.myCompany.com), select the option to Notify when Report Completed,
select the Run Immediately option, and click Add Destination in the Delivery section to add email as the destination.

This displays all the destinations configured. Select Email option. This displays the screen with the email options with text fields to enter data such as To, CC, Subject, and Body of the email.
Enter the appropriate data, and click Submit.

This scheduled job is displayed on the Schedule page.

Click the Schedules tab and click History to see whether the report is scheduled and delivered successfully.

Now open Outlook Express (or the mail client that you have configured) and log in to the mail as the user configured.

When the report is successfully completed and delivered to the Inbox, you can see both the notification mail and the scheduled report.

Open the attachment in the Category Sales and Profits mail to see the scheduled report.

(If not logged in) Log in to BI Publisher as Administrator.
Navigate to My Folders > Learn, and click Create a new report.
Enter Bursting to File as the name of the report, and click Create.

The report appears on the BI Publisher page. Click the Edit link found below the name of the report to edit the report.

The report is displayed in Edit mode. Click the Data Model node and then click New to define the data model for the report.

On the Data Set screen that appears on the right, ensure that SQL Query is selected as Type, and demo is selected as Data Source.
Select the Cache Results check box, copy and paste the following query in the SQL Query section:

select e.first_name || ' ' || e.last_name name, e.first_name, e.last_name, e.salary, e.salary*12 ANNUAL_SALARY, e.salary*12*0.28 FED_WITHHELD, j.job_title, d.department_name, m.first_name || ' ' || m.last_name manager from employees e, employees m, departments d, jobs j
where e.department_id = d.department_id
and j.job_id = e.job_id
and e.manager_id = m.employee_id
and e.department_id = nvl(:dept,e.department_id)
and e.employee_id = nvl(:emp,e.employee_id)
order by
e.department_id, e.manager_id

Click Save to save the report.
The screen should look like this:

Note: You can click View to see if the query is returning valid XML data.

Now open the Salary Expenses by Manager. RTF template file provided with this OBE.
Save it on your local system with the same name, and open it in MS Word.
The template file looks like the screenshot below:

Now log in to BI Publisher from MS Word as Administrator.

In the Open Template window that appears, navigate to My Folders > Learn and open the Bursting to File report that you created. (Ensure that BI Publisher is selected as the Workspace.)

Select Preview > PDF to view the report in PDF format:

Now select the Publish Template As... option from the Oracle BI Publisher menu in MS Word.

Enter Template1 as the name and click OK.

This uploads the template for the report, and displays the following message:

Now go back to BI Publisher and open the report , and open the Bursting to File report in Edit mode.

On the Report Properties screen, click the Bursting node in the Report section on the left, to define bursting properties for the report.

This displays the the Bursting Properties screen on the right.
In the General Properties section, select the Enable Bursting option, and then select ROWSET/ROW/DEPARTMENT_NAME from the Split By drop-down list and ROWSET/ROW/DEPARTMENT_NAME from the Deliver By drop-down list.

In the Delivery Data Source section, select demo as the Data Source, and add the following query in the SQL Query section:
select d.department_name KEY, 'Template1' TEMPLATE, 'RTF' TEMPLATE_FORMAT,
'en-US' LOCALE, 'PDF' OUTPUT_FORMAT, 'FILE' DEL_CHANNEL, 'C:\BIP' PARAMETER1,
d.department_name || '.pdf' PARAMETER2
from departments d

Click Save to save the changes you made to the report.
The screen is shown below:

Important Notes: Based on the SQL Query that you provide in the Delivery Data Source section on the Bursting Properties page, BI Publisher will build the delivery XML data set.

For example, in the above query:
department_name is the delivery key, (this should match your Split By field of the Bursting Properties)
'Template1' is the template to be used with the report, 'RTF' the template source format,
'en-US' - (US English) is the Locale, 'PDF' is the output format for the report, 'File' is the delivery channel with the path: C:\BIP as Parameter1. (This should be a folder on your local file system .Create a folder BIP in C:\ , or you can use any of the existing folders including the Temp folder too.)
Parameter2 defines the file name format (this should match your Deliver By field, the delivery is done to files with names <department_name>.pdf).

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.

On the Schedule Report page that appears, define the following:
In the Job Properties section, select the Burst Report option, and in the Notification section you can optionally provide the email ID of the user to be notified when the report completes or fails (use the mail ID of the user that you have already configured—for example, user@myMailServer.myCompany.com).

Scroll down to define the other options.
Select the Run immediately option in the Time section and click Submit.

This displays the message saying the job is successfully created.

Click the Schedules tab and then click History to see whether the schedule was run successfully.

Now go to the C:\BIP folder (the delivery destination folder you provided in the query). You can see that the PDF files, which have <Department Name>. PDF as the titles, are bursted to this file location.

Open one of the files to see the report. For example, Sales. PDF report file looks like this:

Note: You can also connect to the mail server as the configured user and you can see the notification sent when the job is scheduled successfully.

The first step is to copy and rename the Bursting to File report.
Ensure that you are logged in to BI Publisher as Administrator, and navigate to My Folders > Learn.
Click the report icon beside the Bursting to File report.

Using the commands in the Folder and Report Tasks section, copy and paste the Bursting to File report.

Rename the copied report as Bursting to Email.

Click the Edit link below the name of the report to edit this report.

On the Report Properties screen, click the Bursting node in the Report section on the left, to define bursting properties for the report.

This displays the Bursting Properties screen on the right.
In the General Properties section, select the Enable Bursting option, and then select ROWSET/ROW/DEPARTMENT_NAME from the Split By drop-down list and ROWSET/ROW/DEPARTMENT_NAME from the Deliver By drop-down list.

In the Delivery Data Source section, select demo as the Data Source, and add the following query in the SQL Query section:

select d.department_name KEY,
'Template1' TEMPLATE,
'RTF' TEMPLATE_FORMAT,
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT,
'EMAIL' DEL_CHANNEL,
'user@myMailServer.myCompany.com' PARAMETER1,
'user1@myMailServer.myCompany.com' PARAMETER2,
'bipublisher-report@oracle.com' PARAMETER3,
'SUBJECT: Bursting to Email Test' PARAMETER4,
'BODY: Test Report Body' PARAMETER5,
'true' PARAMETER6,
'replyto@nowhere.com' PARAMETER7
from departments d

(See the screen below)

Click Save to save the changes to the report.

The parameter mapping for bursting to email is shown below:

Note: Parameter 6 must be set to true if the delivery format selected is PDF.

On the Schedule Report page, define the following:
In the Job Properties section, select the Burst Report option, and in the Notification section you can optionally provide the email ID of the user to be notified.

Scroll down to define the other options.
Select the Run immediately option in the Time section and click Submit.

This displays the message saying the job is successfully created.

Click the Schedules tab and click History to see whether the schedule was run successfully.
Click the Bursting to Email job to see the details.

You can also check the mail for the notification and files.

Ensure that you are logged in to BI Publisher as Administrator, and navigate to My Folders > Learn.
Click Create a new report.

Enter Based on Data Template as the name of the report and click Create.

Click the Edit link below the name of the report created to edit the report.

On the Report Properties page that appears, click the Data Model node on the left and click New to define the data model for the report.

On the Data Set screen that appears on the right, select Data Template from the Type drop-down list in the General Settings section.

This displays the basic structure for the data template. Copy this code in the Data Template section:

<dataTemplate name="firstDT" dataSourceRef="demo">

<dataQuery>
<sqlStatement name="Q_1">
<![CDATA[ SELECT * FROM EMPLOYEES]]>
</sqlStatement>
</dataQuery>

</dataTemplate>

(See the screenshot below.)

Click Save to save the report.

Note: The data source reference defined in the code should point to your database connection. In this case, it is demo, the connection to the OE/HR schemas of the database.

Click View to see the XML data to check whether the data is rendered to the report.
It should look like this:

Now structure the XML in the data template that you have defined.
Click Edit to edit the data model, and paste the following code in the Data Template section.

<dataTemplate name="firstDT" dataSourceRef="demo">

<properties> <property name="xml_tag_case" value="upper"/> </properties>

<parameters/> <lexicals> </lexicals>

<dataQuery> <sqlStatement name="Q_1">

<![CDATA[ SELECT DEPARTMENT_ID, HIRE_DATE, EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT, JOB_ID, EMAIL, PHONE_NUMBER, DEPARTMENT_ID, MANAGER_ID FROM EMPLOYEES]]>

</sqlStatement>

</dataQuery>

<dataStructure>

<group name="G_EMP" source="Q_1"> <element name="EMPLOYEE_ID" value="EMPLOYEE_ID"/> <element name="FIRST_NAME" value="FIRST_NAME"/> <element name="LAST_NAME" value="LAST_NAME"/>
<group name="G_EMPINFO" source="Q_1"> <element name="JOB_ID" value="JOB_ID"/> <element name="HIRE_DATE" value="HIRE_DATE"/> <element name="EMAIL" value="EMAIL"/> <element name="PHONE" value="PHONE_NUMBER"/> <element name="SALARY" value="SALARY"/> <element name="COMMISSION_PCT" value="COMMISSION_PCT"/> <element name="DEPARTMENT_ID" value="DEPARTMENT_ID"/> <element name="MANAGER_ID" value="MANAGER_ID"/> </group> </group>

</dataStructure>

</dataTemplate>

See the screenshot below:

Do not forget to save the changes each time you modify a report. Click Save to save the changes.

View the XML data of the report, and observe the changes in the structure.

Like other BI Publisher reports, you can also create the RTF template file in MS Word for the Based on Data Template report.
However, Data Template.rtf is provided with this OBE to be used with this report. Open this template file in MS Word.

Log in to BI Publisher as Administrator from MS Word, and open the Based on Data Template report.

Select Preview > PDF to preview the report in PDF.

Publish the template as Template1 for the report.

Note: A simple data template report is created in this exercise, but you can define a complex data template with parameters and triggers.

Click the report icon beside the Based on Data Template report in My Folders > Learn.

Using the commands in the Folder and Report Tasks section, copy the Based on Data Template report.

Rename the copied report as Based on Multiple Data Sources.

Click Edit to edit the Data Model for the report.
Click Data Model in the Report section on the left and click New to add another data source to the report (note that the report already has a data set defined, which is based on the data template.)

Click New Data Set2 (created above); this opens the Data Set page on the right.
Select Web Service from the Type drop-down list.
Enter http://www.webservicex.net/stockquote.asmx?WSDL in the WSDL URL field, and GetQuote as the method name.

Click the Save icon to save the report.

Create a parameter Quote with the following properties:

• Enter Quote as the Identifier.
• Select String from the Data Type drop-down list.
• Enter orcl as the Default Value, and Text as the Parameter Type.
• Type Enter Stock Symbol: in the Display Label field.

Also, click the New Data Set2 data model, and add the Quote parameter that you have created to this data set.
Click the Save icon to save the report.

The screen looks like this:

Click the Data Model node, and select Concatenated SQL Data Source from the Main Data Set drop-down list.

Select the Make row names unique check box, and click Save .

Click View to see whether the XML data is rendered to the report.
Scroll down to see the entire XML data. Note that Oracle Corporation's stock quote from the Web Service data source is displayed at the end after the employee data.

Note: Like other BI Publisher reports, you can create and publish the templates for this report in MS Word by using BI Publisher Desktop. A sample template is shown below: