Creating High Fidelity PDF Reports with Oracle Application Express

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial shows you how to create a PDF report that contains a table and graph utilizing two report queries.

Time to Complete

Approximately 1 hour

Creating a PDF Report with Multiple Queries

A Report Query can contain more than one query. This allows you to easily create a PDF file that contains the results of multiple queries. In this tutorial, you will create a Report Query with two queries. The Report Layout consists of a table and a graph in one report layout. When the report is executed, the PDF file will contain two reports in the one document.

Creating a Report Query With Multiple Queries

In this section, you create a Report Query with multiple queries. Perform the following steps:

.

Enter the following URL to log in to Oracle Application Express.
http://<hostname>:8080/apex
To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>

 

.

On the Workspace home page, click Application Builder.

 

.

Select Printing Application.

 

.

Click Shared Components.

 

 

.

Under Reports, click Report Queries.

 

.

Click Create.

 

.

Enter multiquery for the Name and click Next.

 

.

Click Query Builder to create the query.

 

.

From the list of queries, select OEHR_EMPLOYEES.

 

.

Select the check box in front of each of the following columns and click Return.

FIRST_NAME
LAST_NAME
EMAIL
HIRE_DATE
JOB_ID
SALARY 

 

.

Your SQL Query is shown. Click Next.

 

.

You want to add another query, click Add Query.

 

.

Click Query Builder again.

 

.

Select OEHR_DEPARTMENTS and OEHR_EMPLOYEES.

 

.

To create the join condition, drag OEHR_DEPARTMENTS.MANAGER_ID over OEHR_EMPLOYEES.EMPLOYEE_ID.

 

.

Select the check box in front of each of the following columns and click Return.

OEHR_DEPARTMENTS.DEPARTMENT_NAME           
OEHR_DEPARTMENTS.MANAGER_ID
OEHR_EMPLOYEES.FIRST_NAME 
OEHR_EMPLOYEES.LAST_NAME
OEHR_EMPLOYEES.SALARY           

 

.

To create the query, click Create.

 

.

The queries have been defined. In the next section of this tutorial, you create the report layout using Oracle BI Publisher Desktop.

 

Creating a Report Layout Using Oracle BI Publisher Desktop

In this section, you create a Report Layout based on the multiple queries using Oracle BI Publisher Desktop. Perform the following steps

.

From the Source Queries page in the Report Query wizard, click Download.

 

.

Click Save and save the file as the default name multiquery.xml. Then close the download window.

 

.

Open Microsoft Word. Select Oracle BI Publisher > Data > Load Sample XML Data...

 

.

Select multiquery.xml from the apex directory and click Open.

 

.

The data was loaded successfully. click OK.

 

.

Select Oracle BI Publisher > Insert > Table/Form...

 

.

You want all the columns under Rowset1Row to appear in the table. Drag Rowset1 Row to the Template area.

 

.

Select Drop All Nodes.

 

.

To create the table, click OK.

 

.

The table is shown.

 

.

Resize the columns and add a Title.

 

.

You now want to create a chart using the second query. Select Oracle BI Publisher > Insert > Chart...

 

.

Under Rowset2 Row, drag Salary to Values.

 

.

Drag Department Name to Labels.

 

.

Select Pie Chart for Type. Scroll down to the Title field and enter Total Salary Percentage by Department. Click OK.

 

.

The chart is displayed.

 

.

You need to save your layout as an RTF file. Select File > Save As... Select Rich Text Format (*.rtf) for Save as Type, enter multiquery.rtf for File Name, place the file in the apex directory and click Save.

 

.

Switch back to Application Express and click Next.

 

.

Click Browse... to select the RTF file you just created. Select the multiquery.rtf file from the apex directory and click Open.

 

.

Click Next.

 

.

In order to test your report, you need to first start BI Publisher. Double-click the Start BI Publisher icon on your desktop.

 

.

To test your report, click Test Report.

 

.

When the dialogue appears, click Open.

 

.

The table is populated with data, scroll down to see the chart.

 

.

Close the PDF report. On the Create Shared Query page, click Finish.

 

.

Your Report Query and Report Layout were created successfully. In the next section, you create a button on a page that invokes the report.

 

Invoking the Report from an Application Page

In this section, you invoke the report query from a page in your application. Perform the following steps:

.

Navigate to your Printing Application page. Click the Home page.

 

.

Click the Create Button icon.


.

Select the Home region and click Next.


.

Accept the default and click Next.


.

Enter Print Report for Button Name and select Download printable report query for Action and click Next.


.

Accept the default and click Next.


.

Select [Top] from the list of quick links or select Top of Region from the drop down list and select Left for Alignment and click Next.


.

Select multiquery for the Report Query and click Create Button.


.

Your button was created successfully. Click Run.


.

You may be asked to login to the application. Enter obe/obe and click Login.


.

Click Print Report.


.

Click Open to open the PDF report.


.

The report is displayed.


Including Dynamic Images in Your Report

You can dynamically include images in your application. In this section of the tutorial, you create a report query and layout that dynamically includes images in a PDF report.

Adding and Loading a BLOB Column

In this section, you alter the OEHR_EMPLOYEES table and add a BLOB column and load it with data. Perform the following steps:

.

Open a DOS command prompt and execute the addblobdata.sql script.

cd apex
sqlplus obe/obe
@addblobdata

 

.

To view the data in the OEHR_EMPLOYEES table, Enter the following URL to log in to Oracle Application Express.

http://localhost:8080/apex

 

.

Select the Home region and click Next.


.

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>


.

On the Workspace home page, click SQL Workshop.


.

Click Object Browser.


.

Select OEHR_EMPLOYEES from the list of tables.

Notice that the additional BLOB columns are now listed. Select the Data tab.


.

Scroll left to see the data for employees 101 and 103.


.

Notice that there is data for the FILENAME and MIMETYPE columns.


Creating a BLOB2CLOB Function

In order to include the BLOB in your PDF report, the data needs to be converted to CLOB base 64 encoded format. You will create a function to perform the conversion. Perform the following steps:

.

Select SQL Scripts from the list of Components.

 

.

Click Upload.

 

.

Click Browse... and select the blob2clobase64.sql file and click Open.


.

Click Upload.


.

Click the script icon.


.

Click Run.


.

Click Run to confirm .


.

Click the View Results icon.


.

The blob2clobase64 function was created successfully.


Creating a Report Query and Report Layout

In this section, you create a Report Query and a Report Layout. Perform the following steps:

.

Navigate to the Application Builder. Select the Printing Application.

 

.

Click Shared Components.

 

.

Under Reports, click Report Queries.

 

.

Click Create.

 

.

Enter blobquery for the Name and click Next.

 

.

Enter the following query and click Next. Notice that the SQL statement invokes the function you created in the previous section to convert the BLOB column PHOTO to CLOB based 64 encoded format.

select
employee_id,
first_name||' '||last_name name,
filename,
mimetype,
blob2clobase64(photo) photo
from oehr_employees
where photo is not null

 

.

Your query was created. Click Next.

 

.

Click Browse... and select the bloblist2.rtf file and click Open.

 

.

Click Next.

 

.

If you haven't done so already, you need to first start BI Publisher. Double-click the Start BI Publisher icon on your desktop.

 

.

Click Test Report.

 

.

Click Open.

 

.

The report is shown. When done viewing the report, close the window.

 

.

Click Finish.

 

.

Your Report Query and Report Layout were created successfully. In the next section, you create a button on your page to run the report. Select your Application breadcrumb.

 

Invoking the Report from an Application Page

In this section, you invoke the report query from a page in your application. Perform the following steps:

.

Navigate to your Printing Application page. Click the Home page.

 

.

Click the Create Button icon.

 

.

Select the Home region and click Next.

 

.

Accept the default and click Next.

 

.

Enter Print Report with Images for Button Name and select Download printable report query for Action and click Next.

 

.

Accept the default and click Next.

 

.

Select [Top] from the list of quick links or select Top of Region from the drop down list and select Left for Alignment and click Next.

 

.

Select blobquery for the Report Query and click Create Button.

 

.

Your button was created successfully. Click Run.

 

.

You may be asked to login to the application. Enter obe/obe and click Login.

 

.

Click Print Report With Images.

 

.

Click Open to open the PDF report.

 

.

The report is displayed.

 

Storing and Accessing Reports in the Database

You can store a report in a BLOB column in your database. In this section of the tutorial, you will create a new table to store the reports that are generated and then create a report of the contents of the table.

Creating a Table to Store Your Reports

In this section, you upload and run a script that creates a table to store the reports that you generate. Perform the following steps:

.

Open your browser and enter the following URL to log in to Oracle Application Express.

http://localhost:8080/apex

 

.

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>

 

.

Click the SQL Workshop tab. Then click SQL Scripts.

 

.

Click Upload.

 

.

Click Browse... and select the report_archive_tbl.sql file and click Open.

 

.

Click Upload.

 

.

Click the report_archive_tbl.sql script icon.

 

.

Click Run.

 

.

Click Run to confirm.

 

.

Click the View Results icon.

 

.

The report_archive table was created successfully.

 

 

Generating and Storing a Report in the Database

In this section, you create a process that stores the report in the database. Perform the following steps:

.

Navigate to the Application Builder. Select the Printing Application.

 

.

Click the Home page.

 

.

You need to create a process that will store the report in the database. Under Processes, click the Create icon.

 

.

Select PL/SQL and click Next.

 

.

Enter store_blobquery_report for the Name and click Next.

 

.

Enter the following code in the PL/SQL Page Process area and click Next.

declare
   l_report blob;
 
begin
   l_report := apex_util.get_print_document (
     p_application_id      => :APP_ID,
     p_report_query_name   => 'blobquery',
     p_report_layout_name  => 'blobquery',
     p_report_layout_type  => 'rtf',
     p_document_format     => 'pdf'
   );
   insert into report_archive (
    filename,
    mimetype,
    report,
    created_date,
    created_by
   ) values (
    'BLOB Query Search Results ('||to_char(sysdate,'DDMonYYYY')||')',
    'application/pdf',
   l_report,
   sysdate,
   :USER
  );
 end;           

 

.

Click Next.

 

.

Select PRINT_REPORT_WITH_IMAGES for When Button Pressed and click Create Process.

 

.

You also want to create a process to store the Multi Query report. Under Processes, click the Create icon.

 

.

Select PL/SQL and click Next.

 

.

Enter store_multiquery_report for the Name and click Next.

 

.

Enter the following code in the PL/SQL Page Process area and click Next.

declare
l_report blob;
begin
l_report := apex_util.get_print_document (
p_application_id => :APP_ID,
p_report_query_name => 'multiquery',
p_report_layout_name => 'multiquery',
p_report_layout_type => 'rtf',
p_document_format => 'pdf'
);
insert into report_archive (
filename,
mimetype,
report,
created_date,
created_by
) values (
'Multi Query Search Results ('||to_char(sysdate,'DDMonYYYY')||')',
'application/pdf',
l_report,
sysdate,
:USER
);
end;
 

 

 

.

Click Next.

 

.

Select PRINT_REPORT for When Button Pressed and click Create Process.

 

.

Click Run.

 

.

Click Print Report With Images. When the dialogue to open or save the report appears, you can click Cancel.

 

.

Click Print Report. When the dialogue to open or save the report appears, you can click Cancel.

Note: Two rows were inserted into the database for each report you ran.

 

Accessing the Stored Report in the Database

In this section, you create a report of the data in the report_archive table in the database. Perform the following steps:

.

Navigate to your Printing Application page. Click the Home page.

 

.

Under Regions, click the Create icon.

 

.

Select Report and click Next.

 

.

Make sure SQL Report is selected and click Next.

 

.

Enter List of Stored Reports for Title and click Next.

 

.

Enter the following SQL in the SQL Query area and click Create Region.

SELECT id,
       filename,
       mimetype,
       created_date,
       created_by,
       dbms_lob.getlength(report) report_length
 FROM  report_archive
 

 

.

The report was created. You need to make a few changes to the Report Attributes. Under Regions, select the Report link.

 

.

Under Headings Type, click Custom. Change the name of the Report Length header to Report. Deselect the Show check box for MIMETYPE. You also want to create a link to download the report in the Report Length column. Select the edit icon for Report Length.

 

.

Under Column Formatting, enter the following string in the Number / Date Format field and click Apply Changes.

DOWNLOAD:REPORT_ARCHIVE:REPORT:ID::MIMETYPE:FILENAME:::inline:[Download]

Note this string contains the following parameters:

<Format Mask>:<BLOB Table>:<BLOB Column>:<Primary Key Column 1>::<MIME Type Column>: <Filename Column>:::<Content Disposition>:<Download Link Text>

 

 

.

Click Run Page.

 

.

The list of reports that you ran in the previous section of this tutorial are in the list. To view a report, click the [Download] link.

 

.

The report is displayed successfully.

 

 

 

Creating Reports With Dynamic Layout Selection

You can create multiple report layouts that an end-user can select from when executing the report. In this section of the tutorial, you create two Report Layouts. From a page in your application, you create a radio group of the Report Layouts available. When the page is executed, you select which layout you want to use when the report is generated.

Producing Multiple Report Layout

In this section, you create multiple report layouts. Perform the following steps:

.

Open your browser and enter the following URL to log in to Oracle Application Express

.http://localhost:8080/apex

 

.

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>

 

.

On the Workspace home page, click Application Builder.

 

.

Select Printing Application.

 

.

Click Shared Components.

 

.

Under Reports, click Report Layouts.

 

.

Click Create.

 

.

Select Named Columns (RTF) and click Next.

 

.

Enter DynamicLayout1 for the Name and click Browse...

 

.

Select Dynamic_Layout1.rtf from the apex directory and click Open.

 

.

Click Create Layout.

 

.

You want to create another report layout. click Create.

 

.

Select Named Columns (RTF) and click Next.

 

 

.

Enter DynamicLayout2 for the Name and Click Browse...

 

 

.

Select Dynamic_Layout2.rtf from the apex directory and click Open.

 

.

Click Create Layout.

 

.

You also need to create a report query that the report layouts will be based on. Select the Shared Components breadcrumb.

 

.

Under Reports, select Report Queries.

 

.

Click Create.

 

.

Enter DynamicQuery for the Name and click Next.

 

.

Enter the following query and click Next.

SELECT first_name,
       last_name,
       email,
       hire_date, salary
 FROM oehr_employees 

 

.

Click Next.

 

.

Select Use generic report layout for Report Layout Source and click Next.

 

.

Click Finish.

 

.

Your report query was created successfully. In the next section, you create the items the user selects that dynamically generate the report.

 

Creating Selection Criteria Items and Processes for Report Generation

In this section, you create multiple report layouts. Perform the following steps:

.

Navigate to the Print Application Application Page. Select the Home page.

 

.

Under Regions, select the Create icon.

 

.

Select HTML and click Next.

 

.

Select HTML again and click Next.

 

.

Enter Dynamic Report Generation for the Title and click Next.

 

.

You now need to create a few items that will be used to generate the report. The first item is a hidden and protected item to store the report query. Under Items, click the Create icon.

 

.

Select the Hidden item type and click Next.

 

.

Select Hidden and Protected and click Next.

 

.

Enter P1_REPORT_QUERY for the Item Name, select Dynamic Report Generation for the Region and click Next.

 

.

Click Next.

 

.

Enter DynamicQuery for Item Source Query and click Create Item.

 

.

The next item you will create is a select list that allows the user to select how the report should be displayed, in the browser or as an attachment file that can be displayed in Acrobat Reader. Under Items, click the Create icon.

 

.

Select the Select List item type and click Next.

 

 

.

Select Select List again and click Next.

 

 

.

Enter P1_CONTENT_DISP for the Item Name, select Dynamic Report Generation for the Region and click Next.

 

.

Select No for Display Null Option and select the Create or edit static List of Values.

 

.

Enter the following values and click Apply.

Display Value
In Browser
Save / Open in separate Window

 

 

.

Click Next.

 

.

Enter Display for the Label and click Next.

 

.

Click Create Item.

 

.

The last item you create is a radio group that shows the list of report layouts to choose from. Under Items, select the Create icon.

 

.

Select Radio and click Next.

 

.

Select Radio Group and click Next.

 

.

Enter P1_REPORT_LAYOUT for the Item Name, select Dynamic Report Generation for the Region and click Next.

 

.

Select No for Display Null Option and select the Create or edit static List of Values.

 

.

Enter the following values and click Apply.

Display Value
Dynamic Layout 1
Dynamic Layout 2

 

.

Click Next.

 

.

Accept the defaults and click Next.

 

.

Enter DynamicLayout1 for the Default and click Create Item.

 

.

You now need to create a button the will invoke a process (that you will create after the button is created) to generate the report. Under Buttons, click the Create icon.

 

.

Select the Dynamic Report Generation region and click Next.

 

.

Accept the default and click Next.

 

.

Enter Print for the Button Name and click Next.

 

.

Accept the default and click Next.

 

.

Select [Top] from the list of quick links or select Top of Region from the drop down list and select Left for Alignment and click Next.

 

.

Click Create Button.

 

.

The last component you need to create is the process that is invoked when the Print button is pressed to generate the report. Under Processes, click the Create icon.

 

.

Select PL/SQL and click Next.

 

.

Enter print for the Name and click Next.

 

.

Enter the following code and click Next. Notice that this code executes the download_print_document API to generate the report. The report will be generated based on the values specified from the user for the items on the page.

apex_util.download_print_document (
    p_file_name           => 'myreport123',             
    p_content_disposition => :P1_CONTENT_DISP,             
    p_application_id      => :APP_ID,             
    p_report_query_name   => :P1_REPORT_QUERY,
    p_report_layout_name  => :P1_REPORT_LAYOUT,
    p_report_layout_type  => 'rtf',      
    p_document_format     => 'pdf'
);           

 

.

Click Next.

 

.

Select Print for When Button Pressed and click Create Process.

In the next section, you run the page and generate the report.

 

Running a Report Dynamically

In this section, you run the page to generate the report. Perform the following steps:

.

Click Run.

 

.

Select Save / Open in Separate Window and click Print.

 

.

The report was generated. Click Open to view the report.

 

.

The report is displayed with Dynamic Layout 1. Close the report.

 

.

Select In Browser for Display, select Dynamic Layout 2 and click Print.

 

.

This time, your report was displayed within the browser with Dynamic Layout 2.

 

 

Summary

In this tutorial, you should have learned how to:

Hardware and Software Engineered to Work Together About Oracle |Oracle and Sun | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights