Creating High Fidelity PDF Reports with Oracle Application Express

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.

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.

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.

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.

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.

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.

Your query was created. Click Next.

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.

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.

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

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.

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.

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.

Note this string contains the following parameters:

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.

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.

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.

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.

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.

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.