| How-To Document
Create a Master-Detail PDF Report
This document serves to showcase Advanced PDF printing in Application Express 3.0. The sections include:
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.
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:
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:
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.
Now that you have your template, you need to upload it as a Report Layout and associate it with the Report Query that created.
All that is left is linking your new report into your application.
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.