How-To Document

Create a Master-Detail PDF Report

Date: 16-May-2007
This document serves to showcase Advanced PDF printing in Application Express 3.0. The sections include:

Introduction

Application Express 3.0 introduces the ability to export a report region to PDF - essentially, printing a report.  This is defined declaratively and is available for all report regions.  Application Express users can now view and print reports with page headings that repeat on each page and content that properly conforms to various page sizes.  To accomplish this, report data is transformed to PDF format using an externally defined report server.

In addition to enabling printing for report regions, output can also be defined using Report Queries and Report Layouts and then linked into an application.  It is this feature that is explained in this How To.  The discussion walks you through the process of creating a Master-Detail Form, creating a Report Query and RTF template, and then creating a button to expose the new report to the users.

Please note that Advanced PDF Printing requires Application Express 3.0 and a valid license of Oracle BI Publisher.  If your Application Express instance is not currently configured to use BI Publisher, you can get details about installation and configuration in PDF Printing in Application Express 3.0.

Import and Install Base Application

For this exercise, a packaged application will be used to create the objects you will need.  The application also contains the results of this How-To.  This way, you can use that code as reference.  The application is also hosted on apex.oracle.com so that you can run the application to see the end results.  Follow the steps below to download, import and install this application into your own workspace.

  1. Download md_pdf_howto.zip.
  2. Unzip and extract all files.
  3. Access your target Workspace.
  4. Select the Application Builder.
  5. Click [Import >].
  6. Browse and locate the installer file, md_pdf_howto_installer.sql.
  7. When prompted, select to Install Supporting Objects.
  8. After the process completes, select to Edit the Application.

This How-To will use two tables, MD_PDF_ORDERS and MD_PDF_ORDER_ITEMS.  To preview the results of this How To, you can view and run the application.  Pages 6 and 7 are very similar to what you will create.  They have only been modified to change some display attributes and to remove the ability to change the data in the tables.

Creating a Master-Detail Form

The instructions below walk through the process of creating a Master-Detail form on a new page.  For more information, see chapter 5, "Building an Application", in the Oracle Database Application Express User's Guide.

To create the Master-Detail form:
  1. From the Application Home page, click Create Page.
  2. Select Form.
  3. Select Master-Detail Form.
  4. Define Master Table:
    • For Table/View Name, select MD_PDF_ORDERS.
    • Select all the columns and shift them into Displayed Columns.
    • Click Next >.
  5. Define Detail Table:
    • For Table/View Name, select MD_PDF_ORDER_ITEMS.
    • Select all the columns and shift then into Displayed Columns.
    • Click Next >.
  6. For Master Table Primary Key Source, keep the default of Existing trigger.
  7. For Detail Table Primary Key Source, keep the default of Existing trigger.
  8. The rest of the defaults are fine so you can click Finish and then Create on the Confirmation page.

The result is 2 pages added to your application.  A report on the master table and a master-detail form referencing both tables.  You can run the page and this point.  You will first see a report listing all orders and then you will select to edit an order and will see the master-detail form that was created.

Creating the Report Query

The form you just created displays the Order Items for each Order.  Using standard PDF printing, you could enable region printing on the tabular form.  This would result in an output that would only display the Order Items records and only for the selected master.  To create a report that incorporates 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.

Follow the steps outlined below:
  1. Click on the Shared Components icon.
  2. Under Reports, click Report Queries.
  3. Click Create >.
  4. For Name, enter order_details.
  5. For SQL Query, enter: 
                                             
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 = :Px_ORDER_ID
    Note: Where 'x' is the page number of your Master-Detail page.
  6. For XML to be generated for a query, the query must retrieve at least one record.  Your query contains a bind variable so you must enter a valid value to be used when your XML is generated.  To ensure that records are returned, it is best to test your query.  Enter 2359 for :Px_ORDER_ID and click Test Query.
  7. Review the data displayed in the popuop window (5 records should be displayed for that Order ID) and click Close.
  8. Click Next >.
  9. You can include items from Session State in your report.  Use the pop-up lov to select Px_CUSTOMER_NAME and then click Add to include (where 'x' is the page number of your Master-Detail page).
  10. Continue using the pop-up to also include:
    • Px_ORDER_DATE
    • Px_ORDER_ID
    • Px_ORDER_MODE
    • Px_ORDER_STATUS
    • Px_ORDER_TOTAL
    • Px_SALES_REP.
  11. For XML Structure, select Advanced (include session state information).
  12. Click Download XML.
    This downloads an XML file with the name of the report query, order_details.xml.
  13. Save the resulting file.
    At this point, you will need to create an RTF template using this XML file and then upload it.  You can do that now and stay on this screen or you can complete this process to create the Report Query using a generic report layout.  You would then edit the report query to reference the new report layout once it is created and uploaded.  The latter is the process that you will follow.
  14. Click Next >.
  15. Notice the URL presented on the final page of the wizard.  This is the URL that will be used to call this report from within your application.  Click Finish.
Creating the RTF Template

To edit the XML produced from your Report Query, you will need the Oracle BI Publisher Desktop. If this is properly loaded, when you open MS Word, you will see 'Oracle BI Publisher' as a menu choice. If you do not have the Desktop loaded, you can download it here (select Desktop).

First, you will load the XML Details generated by your Report Query.
  1. Open Microsoft Word.
  2. Select the Oracle BI Publisher tab/drop down.
  3. Select Data.
  4. Select Load Sample XML Data.
  5. Select the file generated when creating the Report Query, order_details.xml.
  6. You should see the message 'Data loaded successfully'. Click OK.
    You will not see anything on the page.
Next, you will include the order details.
  1. Select Oracle BI Publisher.
  2. Select Insert.
  3. Select Field.
  4. Select Px Customer Name and click Insert.
  5. Continue to insert each of the order columns.
  6. Click Close.
  7. Each of the order items should display within your document but they are in one long string. Edit the document to put each item on its own line and prefix each with a descriptive label.  It should look like the following: 
                                             
Customer: Px_CUSTOMER_NAME
Order Date: Px_ORDER_DATE
Order ID: Px_ORDER_ID
Order Mode: Px_ORDER_MODE
Order Status: Px_ORDER_STATUS
Order Total: Px_ORDER_TOTAL
Sales Rep: Px_SALES_REP
Now, you will include the line items.
  1. Insert a few blank lines below your order details.
  2. Select Oracle BI Publisher.
  3. Select Insert.
  4. Select Table Wizard.
  5. Keep the default of Table and click Next >.
  6. For Grouping Field, select DOCUMENT/REGION/ROWSET/ROW and click Next >.
  7. Select all the columns and click Next >.
  8. You do not need to make any Grouping selections because your report will select just one order.  Click Next >.
  9. For Sort By, select Line Item Id and click Next >.
  10. For Labels:
    • Change Line Item Id to Line Item.
    • Change Order Item Id to Order Item.
  11. Click Finish
At this point, your basic framework has been created.  You can now include a page header and footer.  This is done just the same way you would in any Word document (using View > Header and Footer).  The header could contain the name of the report (e.g. My Order Report) and the footer could contain page numbers or the date the report was executed.  You can also customize the font sizes and types just as you would with any text.  You can also include variables that will display that date the report was executed and the user that executed the report.  These are included in the footer of the example RTF and are <?DATE?> and <?USER_NAME?> respectively. Once you complete any formatting you want to add, you need to save your file.
  1. Select File.
  2. Select Save As.
  3. For Save as type, select Rich Text Format (*.rtf).
  4. For File Name, enter order_details.rtf.
  5. You can now close Microsoft Word.
You can reference the BI Publisher documentation for detailed instructions on RTF templates. Creating the Report Layout

Now that you have your template, you need to upload it as a Report Layout and associate it with the Report Query that created.

  1. Navigate to Shared Components.
  2. Under Reports, click Report Layouts.
  3. Click Create >.
  4. For Report Layout Type, select Named Columns (RTF).
  5. For Layout Name, enter order details.
  6. For Report Layout File, use the Browse and select your order_details.rtf.
  7. Click Create Layout.

  8. Click the Shared Components breadcrumb.
  9. Under Reports, click Report Queries.
  10. Click to edit the order details query.
  11. For Report Layout, select order details.
  12. Copy the URL.  It should be f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=order_details.
  13. Click Apply Changes.
Linking the new PDF Report into the Application

All that is left is linking your new report into your application.

  1. Click the Edit Page x icon in the upper right.
  2. Navigate to your Master-Detail page.
  3. Under Buttons, click the Create icon.
  4. For Region, select Edit Md Pdf Orders.  This will put the button in the top or master region.
  5. For Position, keep the default of Create a button in a region position and click Next >.
  6. For Button Name, enter PRINT.
  7. For Label, enter Print PDF of Order.
  8. For Action, select Submit Page and Redirect to URL and click Next >.  The page needs to be submitted so that the details of the order will be written to session state and then available to your report.
  9. Keep the default for Button Template and click Next >.
  10. For Position, select Region Template Position #CREATE#.  This will place the button to the right of the Apply Changes button.
  11. Leave Branch to Page(when button pressed) blank and click Next >.  You leave this blank because you will manually create a branch with the proper attributes to call your report.
  12. For Condition Type, select Value of Item in Express 1 Is NOT NULL.
  13. For Expression 1, enter Px_ORDER_ID.  This will make it so that the print button will not be displayed when you are creating a new order.  It would not be appropriate then because there would be no details.
  14. Click Create Button.

  15. Under Branches, click the create icon.
  16. Keep the defaults and click Next >.
  17. For Branch Target, select URL.
  18. For URL Target, paste in the URL that you copied from the Report Query.  This should be f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=order_details.  Alternatively, you could branch to page 0 with a Request of PRINT_REPORT=order_details.
  19. Click Next >.
  20. For Sequence, enter .5.  The idea is to have it evaluated as the first possible branch.
  21. For When Button Pressed, select PRINT (Print PDF of Order).
  22. Click Create Branch.

Now, run your page.  You can click Cancel to get to your report and select an order.  Try out your new PDF Report by clicking the Print PDF of Order button.

Additional Comments

This exercise created a report that was specific to one order.  You could also create the same report without passing in the Order ID and create one PDF that would contain all orders.  To do this, you would create one Report Query that returns all the columns you need but there is no need to include session state variables.  You would then load the resulting XML into Microsoft Word just as you did above and use the Table Wizard.  When you get to the Groupings selection, you would select the columns that you want to use as your Master.  The resulting display will show the grouped columns above a table that will contain your detail records.  You can then add labels and customize any way you need.   When the report is run, each Master value will appear on a new page.

We hope this discussion has shown you how powerful and useful the combination of Oracle Application Express and Oracle BI Publisher can be.