3 Create a Report

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

3.1 Selecting the Data

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

3.1.1 Before You Begin

  • Start the application server.

  • Start the BI Publisher Enterprise server and sign in.

To select the data

  1. On the Reports tab on Oracle BI Publisher Enterprise, click My Folders.

    Figure 3-1 Reports Tab on Oracle BI Publisher Enterprise

    Description of Figure 3-1 follows
    Description of "Figure 3-1 Reports Tab on Oracle BI Publisher Enterprise"

  2. Click Create a new report.

    Figure 3-2 Reports Tab Showing the Create a New Report Link

    Description of Figure 3-2 follows
    Description of "Figure 3-2 Reports Tab Showing the Create a New Report Link"

  3. Complete the following field:

    • Enter Report Name

    Figure 3-3 Enter Report Name Field

    Description of Figure 3-3 follows
    Description of "Figure 3-3 Enter Report Name Field"

  4. Click Create.

  5. Locate the report and click Edit.

    Figure 3-4 Report Edit Link

    Description of Figure 3-4 follows
    Description of "Figure 3-4 Report Edit Link"

  6. Click Data Model and then click New.

    Figure 3-5 Locations for the Data Model Menu and New Button on the Reports Tab

    Description of Figure 3-5 follows
    Description of "Figure 3-5 Locations for the Data Model Menu and New Button on the Reports Tab"

  7. Complete the Name field under General Settings or accept the default value of New Data Set 1.

  8. Choose SQL Query for the Type field.

  9. Choose a Data Source under Details.

  10. 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.

  11. Click Save.

    Figure 3-6 Example of Complete SQL Query Field

    Description of Figure 3-6 follows
    Description of "Figure 3-6 Example of Complete SQL Query Field"

  12. Click View.

    Figure 3-7 Location of the View Link on the Reports Tab

    Description of Figure 3-7 follows
    Description of "Figure 3-7 Location of the View Link on the Reports Tab"

  13. 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

    Description of Figure 3-8 follows
    Description of "Figure 3-8 Example of Search Query Results in XML"

  14. Click Export to export the XML data to a PC.

  15. On the File Download window, click Save.

  16. On the Save As window, ensure the file extension is .xml and then navigate to the folder where you want to store the file.

  17. Click Save.

    If you want to add a template to the report, do not close the report.

3.2 Formatting 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.

3.2.1 Before You Begin

  • Open Microsoft Word and open a new blank document.

To format the report

  1. 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

    Description of Figure 3-9 follows
    Description of "Figure 3-9 Microsoft Word Menu Navigation to "Load Sample XML Data" Command"

  2. Locate the report file in the XML format and then click Open.

    A window displays a message that the data loaded successfully.

  3. Click OK.

  4. 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.

  5. Select Row at the top of the list.

  6. Drag Row and drop it in the Template column.

    When you drop Row in the Template column, a small menu displays.

  7. Click Drop Single Node.

    Figure 3-10 The Drop Single Node Menu

    Description of Figure 3-10 follows
    Description of "Figure 3-10 The Drop Single Node Menu"

  8. 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

    Description of Figure 3-11 follows
    Description of "Figure 3-11 Example of Insert Table Form screen"

  9. Continue to select fields in the Data Source column, drag them over Row and drop them in the Template column.

  10. 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

    Description of Figure 3-12 follows
    Description of "Figure 3-12 Example of Document with Table Header Rows"

  11. Edit the field names in the header row to be more descriptive.

  12. 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.

  13. 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.

    Figure 3-13 Save Prompt Window

    Description of Figure 3-13 follows
    Description of "Figure 3-13 Save Prompt Window"

  14. 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.

  15. Click Save.

    The report displays in PDF format.

  16. Click File, and then Exit to close the PDF version of the report.

  17. Click File and then Exit to exit Microsoft Word.

3.3 Adding a Template to the Report

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

  1. 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

    Description of Figure 3-14 follows
    Description of "Figure 3-14 Location of Edit Link on Report"

  2. Click Layouts.

  3. Under Manage Template Files, click Browse.

    Figure 3-15 Location of Browse Button

    Description of Figure 3-15 follows
    Description of "Figure 3-15 Location of Browse Button"

  4. On the Look in window, locate the template with the .rtf file extension.

  5. Select the template and click Open.

  6. On Oracle BI Publisher Enterprise, click Upload.

    Figure 3-16 Location of Upload Button

    Description of Figure 3-16 follows
    Description of "Figure 3-16 Location of Upload Button"

  7. Click View.

    The report processes and then displays in HTML format because HTML displays at the top of the format list.

  8. From the Format list, choose the format you want and click View.

  9. The report processes and displays in the format and template you choose.

  10. Click Sign out when you finish.