This chapter contains these topics:
Using Oracle BI Publisher, you can create, format, manage, and deliver all types of documents, which eliminates the need for costly print solutions. You can easily design the layout of a report using familiar desktop tools, which dramatically reduces the time and costs to develop and maintain reports.
Creating a report includes the following tasks:
Selecting the Data
Formatting the Report
Adding a Template to the Report
To create a report from an SQL statement, you must choose the data that you want in the report. After you choose the data, you can then perform the following:
View the results in the report
Edit the data for the report
Export the data
Start the application server.
Start the BI Publisher Enterprise server and sign in.
On the Reports tab on Oracle BI Publisher Enterprise, click My Folders.
Figure 3-1 Reports Tab on Oracle BI Publisher Enterprise
Click Create a new report.
Figure 3-2 Reports Tab Showing the Create a New Report Link
Complete the following field:
Enter Report Name
Click Create.
Locate the report and click Edit.
Click Data Model and then click New.
Figure 3-5 Locations for the Data Model Menu and New Button on the Reports Tab
Complete the Name field under General Settings or accept the default value of New Data Set 1.
Choose SQL Query for the Type field.
Choose a Data Source under Details.
Complete the SQL Query field.
For example, you might enter Select * from F0101 fetch first 10 rows only. This query selects all of the fields in the Address Book Master table (F0101) and the first 10 rows or records of data.
Click Save.
Figure 3-6 Example of Complete SQL Query Field
Click View.
Figure 3-7 Location of the View Link on the Reports Tab
Click View to review the results of the query.
The data displays in XML, which is the format the Microsoft Word add-on uses to create report templates.
Figure 3-8 Example of Search Query Results in XML
On the File Download window, click Save.
On the Save As window, ensure the file extension is .xml and then navigate to the folder where you want to store the file.
Click Save.
If you want to add a template to the report, do not close the report.
After you create the report, you can import the XML data into Microsoft Word and use the formatting features for Oracle BI Publisher.
Open Microsoft Word and open a new blank document.
On a new blank document, click Oracle BI Publisher, Data, and then Load Sample XML Data from the menu.
Figure 3-9 Microsoft Word Menu Navigation to "Load Sample XML Data" Command
Locate the report file in the XML format and then click Open.
A window displays a message that the data loaded successfully.
Click OK.
Click Oracle BI Publisher, Insert, and then Table/Form from the menu.
The Insert Table/Form window displays. The Data Source column contains the fields from the file.
Select Row at the top of the list.
Drag Row and drop it in the Template column.
When you drop Row in the Template column, a small menu displays.
Click Drop Single Node.
Select a field in the Data Source column, drag it over Row, and drop it in the Template column.
For example, select the Aban8 Address Number field and drag it on top of Row. Release the mouse button. Aban8 Address Number displays under Row in the Template column.
Figure 3-11 Example of Insert Table Form screen
Continue to select fields in the Data Source column, drag them over Row and drop them in the Template column.
Click OK.
The new blank document contains a table with these fields in the header row of the template.
Figure 3-12 Example of Document with Table Header Rows
Edit the field names in the header row to be more descriptive.
Change any of the formatting for the report for it to display as you want.
Use the Microsoft Word features to change the font type, size, color, shading, borders, and so forth.
Click the Oracle BI Publisher menu, Preview Template, and then PDF to view the report template with your data.
The system prompts you to save the template.
Click OK.
On the Save As window, ensure the file extension is .rtf (Rich Text Format) and then navigate to the folder where you want to store the template.
Click Save.
The report displays in PDF format.
Click File, and then Exit to close the PDF version of the report.
Click File and then Exit to exit Microsoft Word.
After you create the template in Microsoft Word, you upload the template to the report in BI Publisher. You then modify the SQL statement to select and sort the data that displays on the report.
To add a template to the report
Return to BI Publisher Enterprise and perform one of the following:
If the report is open, click Edit.
If the report is not open, access My Folders, locate the report and then click Edit.
Figure 3-14 Location of Edit Link on Report
Click Layouts.
Under Manage Template Files, click Browse.
On the Look in window, locate the template with the .rtf file extension.
Select the template and click Open.
On Oracle BI Publisher Enterprise, click Upload.
The report processes and then displays in HTML format because HTML displays at the top of the format list.
From the Format list, choose the format you want and click View.
The report processes and displays in the format and template you choose.
Click Sign out when you finish.