13 How to Create a Master Detail PDF Report

Oracle Application Express supports the ability to print a report by exporting a report region to PDF. Defined declaratively, report printing enables users to view and print reports that include page headings and that properly conform to specified page sizes. When users print a report, the report data is transformed to a PDF format using an externally defined report server.

In addition to enabling printing for report regions, you can also define output using report queries and report layouts that are linked to an application.

This tutorial explains how to build a master detail form, define a report query and RTF template, and then create a button to expose the new report.

This section contains the following topics:

For additional examples on this topic, please visit the following Oracle by Examples (OBEs):

See Also:

PDF Printing in Application Express 3.0 and "Configuring Printing for Reports" in Oracle Database Application Express User's Guide

About Oracle BI Publisher Licensing Requirements

Advanced PDF Printing requires Oracle Application Express release 3.0 or higher and a valid license of Oracle BI Publisher. If your Oracle Application Express instance is not currently configured to use BI Publisher, you can learn more about installing and configuring PDF Printing in PDF Printing in Application Express 3.0.

Viewing a Hosted Version of the Application

If you do not have a valid Oracle BI Publisher license, or have not yet installed or configured Oracle BI Publisher, you can follow the exercises in this tutorial and view a completed version of the application on apex.oracle.com.

To view hosted version of the application:

  1. In your Web browser go to:

    http://apex.oracle.com/pls/otn/f?p=15610

    The hosted application appears.

  2. Click the Edit icon adjacent to a customer.

  3. On the Order Details page, click Print PDF of Order.

  4. Save the PDF to your hard drive so you can view it.

Loading the Required Objects

In order to complete this tutorial, you must download and install the packaged application How To Create a Master-Detail PDF Report from Oracle Technology Network (OTN). Importing and installing this packaged application creates the required objects needed to complete this tutorial. Additionally, it also contains the final version the application you will build. That way, you can refer to it as a code reference.

Topics in this section include:

Download the Packaged Application

To download the packaged application from OTN:

  1. Download the md_pdf_howto.zip file to your computer. In your Web browser go to:

    http://www.oracle.com/technology/products/database/application_express/packaged_apps/md_pdf_howto.zip

  2. Unzip and extract the md_pdf_howto.zip file:

    • Microsoft Windows - Double-click the md_pdf_howto.zip file

    • UNIX or Linux - Enter the following command:

      $ unzip md_pdf_howto.zip

  3. Review the md_pdf_howto_readme.txt file.

Import and Install the Packaged Application

To import and install the application, How To Create a Master-Detail PDF Report:

  1. Log in to Oracle Application Express. See "Logging In To Oracle Application Express" in Oracle Database Application Express User's Guide.

  2. On the Workspace home page, click Application Builder.

    The Application Builder home page appears.

  3. Click the Import button.

  4. For Specify File, specify the following:

    1. Import file - Click Browse and go to the md_pdf_howto_installer.sql file.

    2. File Type - Select Application, Page, or Component Export.

    3. Verify that File Character Set is correct.

    4. Click Next.

  5. For File Import Confirmation, click Next to install the imported file.

    The Install Application Wizard appears.

  6. In the Install Application Wizard, specify the following:

    1. Parsing Schema - Select the appropriate schema.

    2. Build Status - Select Run and Build Application.

    3. Install As Application - Select Auto Assign New Application ID.

    4. Click Install.

  7. For Supporting Objects, select Yes and click Next.

  8. Confirm your selections by clicking Install.

  9. Click the Home breadcrumb link at the top of the page.

    The Application Builder home page appears.

About Checking the Available Space in Your Workspace

If you experience problems installing the How To Create a Master-Detail PDF Report application, verify the available space in your workspace. You many need to request additional storage space.

If you are a workspace administrator, you can:

  1. Determine if you need additional storage space. See "Viewing the Workspace Overview Report" in Oracle Database Application Express User's Guide.

  2. Request additional storage space. See "Requesting Additional Storage" in Oracle Database Application Express User's Guide.

About Deleting the Packaged Application

Deleting the How To Create a Master-Detail PDF Report application and selecting to deinstall the supporting objects completely removes all associated objects and sample data.

To delete the How To Create a Master-Detail PDF Report application:

  1. Log in to Oracle Application Express.

  2. On the Workspace home page, click Application Builder.

    The Application Builder home page appears.

  3. Select the How To Create a Master-Detail PDF Report application.

    The Application home page appears.

  4. On the Tasks list, click Delete this Application.

    The Deinstall page appears.

  5. To remove all associated objects and sample data, select Remove Application Definition and Deinstall Supporting Objects.

  6. Click Deinstall.

Creating a Master Detail Form

To create a master detail form:

  1. On the Workspace home page, click the Application Builder icon.

  2. Select the How To Create a Master-Detail PDF Report application.

  3. Click Create Page.

  4. Select Form and click Next.

  5. Select Master Detail Form and click Next.

    The Master Detail Wizard appears.

  6. On Master Table, select the following:

    1. Table/View Owner - Select the appropriate schema.

    2. Table/View Name - Select MD_PDF_ORDERS.

      The columns in that object appear under Available Columns.

    3. Press the Shift key to select all the columns and then click the Add button to move them to Displayed Columns.

    4. Click Next.

  7. On Detail Table, specify the following:

    1. Show Only Related Tables - Accept the default, Yes.

    2. Table/View Owner - Select the appropriate schema.

    3. Table/View Name - Select MD_PDF_ORDER_ITEMS.

      The columns in that object appear under Available Columns.

    4. Press the Shift key to select all the columns and then click the Add button to move them to Displayed Columns.

    5. Click Next.

  8. On Primary Key Source:

    1. For the Primary Key Column ORDER_ID, accept the default, Existing Trigger and click Next.

    2. For the Primary Key Column ORDER_ITEM_ID, accept the default, Existing Trigger and click Next.

  9. To accept the remaining defaults, click Finish.

  10. Click Create.

    A Success page appears.

View the Report

To view the new pages:

  1. Click the Run Page icon.

    The PDF Orders report appears as shown in Figure 13-1.

    Figure 13-1 Md Pdf Orders Report

    Description of Figure 13-1 follows
    Description of "Figure 13-1 Md Pdf Orders Report"

    Note that the wizard added two pages to the application, a report on the master table and a master detail form that references both tables.

  2. To edit an order and view the master detail form, click the Edit icon.

    The Master Details page appears as shown in Figure 13-2.

    Figure 13-2 Master Details Page

    Description of Figure 13-2 follows
    Description of "Figure 13-2 Master Details Page"

Creating the Report Query

The form you created displays order items for five orders. Using standard PDF printing, you could enable region printing on the tabular form. This would result in an output that would display just the order items records and only for the selected master. To create a report that incorporates all the order information, along with the Order Items, you will create a report query. The order information will be retrieved from the session state for each order item.

To create a report query:

  1. Go to the Report Queries page:

    1. To return to the Application home page, click the Application link on the Developer toolbar.

    2. Click on the Shared Components icon.

    3. Under Reports, click Report Queries.

      The Report Queries page appears.

  2. Click Create.

  3. For Report Query Definition, enter the following:

    1. Report Query Name - Enter order_details.

    2. Session State - Select Include application and session information.

      The Select Items fields appear.

    3. Select Items - Select P3_CUSTOMER_NAME, and click Add.

      Repeat this procedure for:

      P3_ORDER_DATE

      P3_ORDER_ID

      P3_ORDER_MODE

      P3_ORDER_STATUS

      P3_ORDER_TOTAL

      P3_SALES_REP

    4. Accept remaining defaults and click Next.

  4. For Source Queries, enter the following:

    1. SQL Query - Enter the following code:

      SELECT LINE_ITEM_ID,
       QUANTITY,
       ORDER_ITEM_ID,
       PRODUCT_NAME,
       to_char(UNIT_PRICE,'$9,999.99') unit_price
  FROM MD_PDF_ORDER_ITEMS
 WHERE ORDER_ID = :P3_ORDER_ID

    2. Click Next.

  5. For Data Source for Report Layout, select XML Data and the click Download button.

    This step downloads an XML file with the name of the report query, order_details.xml.

  6. Save the resulting file to your hard drive.

    You will create an RTF template using this XML file and then upload it. First, however, you must complete the process of creating the report query using a generic report layout. You will then edit the report query to reference the new report layout once it is created and uploaded.

  7. Click Next.

  8. For Report Layout File, browse to and select the order_details.xml file you saved.

  9. Click Next.

    Notice the URL that displays. This is the URL that will be used to call this report from within your application.

  10. Click Finish.

    Next, you need to create an RTF template using this XML file.

Creating the RTF Template

To edit the XML produced from your report query, you need the Oracle BI Publisher Desktop. When properly loaded, Oracle BI Publisher Desktop adds a new menu option to Microsoft Word.

Topics in this section include:

See Also:

Oracle BI Publisher Desktop White Papers and Demonstrations

Download Oracle BI Publisher Desktop

If you do not have the Desktop loaded, you can download it here:

http://www.oracle.com/technology/software/htdocs/devlic.html?url=/technology/software/products/ias/htdocs/101320bi.html

See Also:

"About Oracle BI Publisher Licensing Requirements"

Load the XML Details

To load the XML generated by your report query:

  1. Open Microsoft Word.

  2. From the Oracle BI Publisher menu, select Data and then Load Sample XML Data.

  3. Select the file you generated when creating report query, order_details.xml.

    The following message appears:

    Data loaded successfully

  4. Click Ok.

    Note that nothing appears on the page.

Insert the Fields

To insert the order columns:

  1. From the Oracle BI Publisher menu, select Insert and then Field.

  2. Select P3 Customer Name and click Insert.

  3. Continue to insert each of the order columns.

  4. Exit the Field dialog box, click Close.

    Although each of the order items displays on the page, they are in one long string.

  5. Edit the page so that each item displays on a separate line and is prefaced by a descriptive label. Consider the following example:

    Customer: P3_CUSTOMER_NAME
Order Date: P3_ORDER_DATE
Order ID: P3_ORDER_ID
Order Mode: P3_ORDER_MODE
Order Status: P3_ORDER_STATUS
Order Total: P3_ORDER_TOTAL
Sales Rep: P3_SALES_REP

Include Line Items

To include line items:

  1. Insert a few blank lines below your order details.

  2. From the Oracle BI Publisher menu, select Insert and then Table Wizard.

  3. Accept the default, Table, and click Next.

  4. For Grouping Field, select DOCUMENT/REGION/ROWSET/ROW and click Next.

  5. Select each field and move it to the right column. Click Next.

    You do not need to make any grouping selections since your report will select just one order.

  6. Click Next.

  7. For Sort By, select Line Item Id and click Next.

  8. For Labels, edit the following:

    1. Line Item Id - Change to Line Item.

    2. Order Item Id - Change to Order Item.

  9. Click Finish.

    Your basic framework is created and should resemble Figure 13-3.

    Figure 13-3 Report Layout Template

    Description of Figure 13-3 follows
    Description of "Figure 13-3 Report Layout Template"

  10. To define a header and footer, from the View menu, select Header and Footer.

    For example, the header could contain the name of the report (for example, My Order Report) and the footer could contain page numbers or the date the report was executed. You can also customize the font family or font size.

  11. Save your changes:

    1. From the File menu, select Save As.

    2. For Save as type, select Rich Text Format (*.rtf).

    3. For File Name, enter order_details.rtf.

    4. Exit Microsoft Word.

About Including Variables in Your RTF

You can include variables in the header and footer of your RTF. For example, the RTF file used in the sample pages included in the How To Create a Master-Detail PDF Report application include the date the report was executed and the user that executed the report.

To download and view this RTF:

  1. Go to the Report Layouts page:

    1. Click on the Shared Components icon.

    2. Under Reports, click Report Layouts.

      The Report Queries page appears.

  2. Click order_details2.

  3. Click Download and save order_details2.rtf to your hard drive.

  4. Open order_details2.rtf in Microsoft Word and note the variables included in the footer.

Creating the Report Layout

Once you complete your report layout template, you need to upload it to Application Builder and associate it with your report query.

Topics in this section include:

Create a Report Layout

To create a report layout:

  1. Go to the Shared Components page:

    1. Click the Shared Components breadcrumb.

    2. Under Reports, click Report Layouts.

      The Report Layouts page appears.

  2. Click Create.

  3. For Layout Type, select Named Columns and click Next.

  4. For Layout Type, specify the following:

    1. For Layout Name, enter order details.

    2. For Report Layout File, click Browse and select order_details.rtf.

  5. Click Create Layout.

Associate the Report Layout and Report Query

To associate the report layout and report query:

  1. Go to the Shared Components page. Click the Shared Components breadcrumb.

  2. Under Reports, click Report Queries.

  3. Edit the query by clicking order_details.

  4. Under Report Query Attributes, for Report Layout, select order details.

  5. Click Apply Changes.

Linking the PDF Report to the Application

In this exercise, you link the PDF report to the application by creating a button.

Topics in this section include:

Create a Button

To create a button:

  1. Go to the Page Definition for the Master Detail page:

    1. Click the Application breadcrumb link.

    2. Select Master Detail.

  2. Under Buttons, click the Create icon.

    Figure 13-4 Create Icon

    Description of Figure 13-4 follows
    Description of "Figure 13-4 Create Icon"

  3. For Button Region, select Md Pdf Orders and click Next.

    This selection positions the button in the top (or master) region.

  4. For Button Position, select accept the default, Create a button in a region position, and click Next.

  5. For Button Attributes:

    1. Button Name - Enter PRINT.

    2. Label - Enter Print PDF of Order.

    3. For Button Type, accept the default, Template Driven.

    4. For Action, select Download printable report query.

    5. Click Next.

  6. For Button Template, accept the default and click Next.

  7. For Display Properties:

    1. Position - Select Region Template Position #CREATE#.

      This positions the button to the right of the Apply Changes button.

    2. Accept the remaining defaults and click Next.

  8. For Branching:

    1. Report Query - Select order_details.

    2. Click Next.

  9. For Conditional Display:

    1. Condition Type - Select Value of Item in Express 1 Is NOT NULL.

    2. Expression 1 - Enter P3_ORDER_ID.

      By creating this condition, the print button will not display when you are creating a new order.

  10. Click Create Button.

Run the Page

To run the page:

  1. Click the Run Page icon in the upper right corner as shown in Figure 13-5.

    Figure 13-5 Run Page Icon

    Description of Figure 13-5 follows
    Description of "Figure 13-5 Run Page Icon"

  2. When the report appears, click Cancel to return to the Md Pdf Order page.

  3. Click the Edit icon to select another order.

    Your Master Detail page should resemble Figure 13-6. Note the new Print PDF or Order button.

    Figure 13-6 Revised Master Detail Page

    Description of Figure 13-6 follows
    Description of "Figure 13-6 Revised Master Detail Page"

  4. If you do not have an Oracle BI Publisher license, or have not yet installed or configured Oracle BI Publisher, you will not be able to save the report as a PDF.

    To view a fully functional version of this application:

    • View the hosted version on apex.oracle.com. See "Viewing a Hosted Version of the Application".

    • View the example pages included with the demonstration application. Return to the Application home page, run the Orders page, click Edit to edit an order, and click Print PDF Order.

About Creating One PDF Containing All Orders

This tutorial created a report that was specific to one order. You could also create the same report without passing in the Order ID. This would create one PDF that would contain all orders. To accomplish this, you would:

  1. Create one report query that returns all the required columns. There would be no need to include session state variables.

  2. Load the resulting XML into Microsoft Word just as you did in this tutorial and use the Table Wizard.

  3. When prompted to specify Groupings, select the columns that you want to use as your master.

    The resulting display will include the grouped columns above a table that contains your detail records. You can then add labels and customize the display. When the report is run, each master value will display on a new page.