| Oracle® Database Application Express Advanced Tutorials Release 3.0 Part Number B28842-01 |
|
|
View PDF |
| Oracle® Database Application Express Advanced Tutorials Release 3.0 Part Number B28842-01 |
|
|
View PDF |
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 and 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:
See Also:
PDF Printing in Application Express 3.0 and "Configuring Printing for Reports" in Oracle Database Application Express User's GuideAdvanced 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.
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:
In your Web browser go to:
http://apex.oracle.com/pls/otn/f?p=15610
The hosted application appears.
Click the Edit icon adjacent to a customer.
On the Order Details page, click Print PDF of Order.
Save the PDF to your hard drive so you can view it.
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:
To download the packaged application from OTN:
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
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
Review the md_pdf_howto_readme.txt file.
To import and install the application, How To Create a Master-Detail PDF Report:
Log in to Oracle Application Express. See "Logging In To Oracle Application Express" in Oracle Database Application Express User's Guide.
On the Workspace home page, click Application Builder.
The Application Builder home page appears.
Click the Import button.
For Specify File, specify the following:
Import file - Click Browse and go to the md_pdf_howto_installer.sql file.
File Type - Select Application, Page, or Component Export.
Verify that File Character Set is correct.
Click Next.
For File Import Confirmation, click Next to install the imported file.
The Install Application Wizard appears.
In the Install Application Wizard, specify the following:
Parse As Schema - Select the appropriate schema.
Build Status - Select Run and Build Application.
Install As Application - Select Auto Assign New Application ID.
Click Install.
For Supporting Objects, select Yes and click Next.
Confirm your selections by clicking Install.
Click the Home breadcrumb link at the top of the page.
The Application Builder home page appears.
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:
Determine if you need additional storage space. See "Viewing the Workspace Overview Report" in Oracle Database Application Express User's Guide.
Request additional storage space. See "Requesting Additional Storage" in Oracle Database Application Express User's Guide.
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:
Log in to Oracle Application Express.
On the Workspace home page, click Application Builder.
The Application Builder home page appears.
Select the How To Create a Master-Detail PDF Report application.
The Application home page appears.
On the Tasks list, click Delete this Application.
The Deinstall page appears.
To remove all associated objects and sample data, select Remove Application Definition and Deinstall Supporting Objects.
Click Deinstall.
To create a master detail form:
On the Workspace home page, click the Application Builder icon.
Select the How To Create a Master-Detail PDF Report application.
Click Create Page.
Select Form and click Next.
Select Master Detail Form and click Next.
The Master Detail Wizard appears.
On Master Table, select the following:
Table/View Owner - Select the appropriate schema.
Table/View Name - Select MD_PDF_ORDERS.
The columns in that object appear under Available Columns.
Press the Shift key to select all the columns and then click the Add button to move them to Displayed Columns.
Click Next.
On Detail Table, specify the following:
Show Only Related Tables - Accept the default, Yes.
Table/View Owner - Select the appropriate schema.
Table/View Name - Select MD_PDF_ORDER_ITEMS.
The columns in that object appear under Available Columns.
Press the Shift key to select all the columns and then click the Add button to move them to Displayed Columns.
Click Next.
On Primary Key Source:
For the Primary Key Column ORDER_ID, accept the default, Existing Trigger and click Next.
For the Primary Key Column ORDER_ITEM_ID, accept the default, Existing Trigger and click Next.
To accept the remaining defaults, click Finish.
Click Create.
A Success page appears.
To view the new pages:
Click the Run Page icon.
The PDF Orders report appears as shown in Figure 13-1.
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.
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.
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:
Go to the Report Queries page:
To return to the Application home page, click the Application link on the Developer toolbar.
Click on the Shared Components icon.
Under Reports, click Report Queries.
The Report Queries page appears.
Click Create.
For Query, enter the following:
Name - Enter order_details.
For Query - Enter the following:
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
Click Next.
For XML to be generated by 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, you need to test your query.
For Test Query, enter 2359 for :P3_ORDER_ID and click Test Query.
Review the data that displays. Five records should display for that Order ID.
Click Close.
Click Next.
Next, you include items from Session State in your report.
For Include Session State, click the List of Values button, select P3_CUSTOMER_NAME, and click Add.
Repeat this procedure and add the following items:
P3_ORDER_DATE
P3_ORDER_ID
P3_ORDER_MODE
P3_ORDER_STATUS
P3_ORDER_TOTAL
P3_SALES_REP
Click Next.
For XML Structure, select Advanced (include session state information) and the click Download XML button.
This step downloads an XML file with the name of the report query, order_details.xml.
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.
Click Next.
Notice the URL that displays. This is the URL that will be used to call this report from within your application.
Click Finish.
Next, you need to create an RTF template using this XML file.
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:
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
To load the XML generated by your report query:
Open Microsoft Word.
From the Oracle BI Publisher menu, select Data and then Load Sample XML Data.
Select the file you generated when creating report query, order_details.xml.
The following message appears:
Data loaded successfully
Click Ok.
Note that nothing appears on the page.
To insert the order columns:
From the Oracle BI Publisher menu, select Insert and then Field.
Select P3 Customer Name and click Insert.
Continue to insert each of the order columns.
Exit the Field dialog box, click Close.
Although each of the order items displays on the page, they are in one long string.
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
To include line items:
Insert a few blank lines below your order details.
From the Oracle BI Publisher menu, select Insert and then Table Wizard.
Accept the default, Table, and click Next.
For Grouping Field, select DOCUMENT/REGION/ROWSET/ROW and click Next.
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.
Click Next.
For Sort By, select Line Item Id and click Next.
For Labels, edit the following:
Line Item Id - Change to Line Item.
Order Item Id - Change to Order Item.
Click Finish.
Your basic framework is created and should resemble Figure 13-3.
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.
Save your changes:
From the File menu, select Save As.
For Save as type, select Rich Text Format (*.rtf).
For File Name, enter order_details.rtf.
Exit Microsoft Word.
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:
Go to the Report Layouts page:
Click on the Shared Components icon.
Under Reports, click Report Layouts.
The Report Queries page appears.
Click order_details2.
Click Download and save order_details2.rtf to your hard drive.
Open order_details2.rtf in Microsoft Word and note the variables included in the footer.
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:
To create a report layout:
Go to the Shared Components page:
Click the Shared Components breadcrumb.
Under Reports, click Report Layouts.
The Report Layouts page appears.
Click Create.
For Layout Type, select Named Columns and click Next.
For Layout Type, specify the following:
For Layout Name, enter order details.
For Report Layout File, click Browse and select order_details.rtf.
Click Create Layout.
To associate the report layout and report query:
Go to the Shared Components page. Click the Shared Components breadcrumb.
Under Reports, click Report Queries.
Edit the query by clicking order_details.
Under Report Query Attributes, for Report Layout, select order details.
From URL, copy the following URL:
f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=order_details
Click Apply Changes.
In this exercise, you link the PDF report to the application by creating a button.
Topics in this section include:
To create a button:
Go to the Page Definition for the Master Detail page:
Click the Application breadcrumb link.
Select Master Detail.
Under Buttons, click the Create icon.
For Button Region, select Md Pdf Orders and click Next.
This selection positions the button in the top (or master) region.
For Button Position, select accept the default, Create a button in a region position, and click Next.
For Button Attributes:
Button Name - Enter PRINT.
Label - Enter Print PDF of Order.
For Button Type, accept the default, Template Driven.
For Action, select Submit Page and Redirect to URL.
The page needs to be submitted so that the details of the order are written to session state and so they will be available to your report.
Click Next.
For Button Template, accept the default and click Next.
For Display Properties:
Position - Select Region Template Position #CREATE#.
This positions the button to the right of the Apply Changes button.
Accept the remaining defaults and click Next.
For Branching, accept the default (leave this option blank) and click Next.
Tip:
In "Create a Branch" you create a branch to call the report.For Conditional Display:
Condition Type - Select Value of Item in Express 1 Is NOT NULL.
Expression 1 - Enter P3_ORDER_ID.
By creating this condition, the print button will not display when you are creating a new order.
Click Create Button.
To create a branch to link the button to the report:
Under Branches, click the Create icon.
For Branch Point and Branch Type, accept the defaults and click Next.
For Target, specify the following:
Branch Target - Select URL.
URL Target - Paste in the URL that you copied from the Report Query:
f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=order_details
Click Next.
For Branch Conditions, specify the following:
Sequence - Enter 5.
When Button Pressed - Select PRINT (Print PDF of Order).
Accept the remaining defaults.
Click Create Branch.
To run the page:
Click the Run Page icon in the upper right corner as shown in Figure 13-5.
When the report appears, click Cancel to return to the Md Pdf Order page.
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.
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.
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:
Create one report query that returns all the required columns. There would be no need to include session state variables.
Load the resulting XML into Microsoft Word just as you did in this tutorial and use the Table Wizard.
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.
