Creating Reports With Dynamic Layout Selection

Purpose

This tutorial shows you how to create a report by dynamically selecting a report layout and display format.

Time to Complete

Approximately 15 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Producing Multiple Report Layouts
 Creating Selection Criteria Items and Processes for Report Generation
 Running a Report Dynamically
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Beginning with Oracle application Express 3.1, you can create multiple report layouts that an end-user can select from when executing the report. In this 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.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Perform the Creating a PDF Report with Multiple Queries tutorial.

2.

Perform the Including Dynamic Images in Your Report tutorial.

3.

Perform the Storing and Accessing Reports in the Database tutorial.

Back to Topic List

Producing Multiple Report Layouts

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

1.

Open your browser and enter the following URL to log in to Oracle Application Express (change the <hostname> to localhost, your specific hostname or apex.oracle.com).

http://<hostname>:8080/apex

 

2.

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

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

 

3.

On the Workspace home page, click Application Builder .

 

4.

Select Printing Application.

 

5. Click Shared Components.

 

6.

Under Reports, click Report Layouts.

 

7.

Click Create.

 

8.

Select Named Columns (RTF) and click Next.

 

9.

Enter DynamicLayout1 for the Name and click Browse...

 

10. Select Dynamic_Layout1.rtf and click Open.

 

11.

Click Create Layout.

 

12.

You want to create another report layout. click Create.

 

13.

Select Named Columns (RTF) and click Next.

 

14.

Enter DynamicLayout2 for the Name and Click Browse...

 

15.

Select d:\hol08\Dynamic_Layout2.rtf and click Open.

 

16.

Click Create Layout.

 

17.

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

 

18.

Under Reports, select Report Queries.

 

19.

Click Create.

 

20.

Enter DynamicQuery for the Name and click Next.

 

21.

Enter the following query and click Next.

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

 

22.

Click Next.

                               
                               
                            

 

23.

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

                               
                               
                            

 

24.

Click Finish.

                               
                               
                            

 

25.

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

                               
                               
                            

 

Back to Topic List

Creating Selection Criteria Items and Processes for Report Generation

In this section, you create the items the user will select to dynamically generate the report. Perform the following steps:

1.

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

 

2.

Under Regions, select the Create icon.

 

3.

Select HTML and click Next.

 

4.

Select HTML again and click Next.

 

5.

Enter Dynamic Report Generation for the Title and click Next.

 

6.

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.

 

7.

Select the Hidden item type and click Next.

 

8.

Select Hidden and Protected and click Next.

 

9.

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

 

10.

Click Next.

 

11.

Enter DynamicQuery for Item Source Query and click Create Item.

 

12.

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.

 

13.

Select the Select List item type and click Next.

 

14.

Select Select List again and click Next.

 

15.

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

 

16.

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

 

17.

Enter the following values and click Apply.

Display Value Return Value
In Browser Inline
Save / Open in separate Window attachment

 

18.

Click Next.

 

19.

Enter Display for the Label and click Next.

 

20.

Click Create Item.

 

21.

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.

 

22.

Select Radio and click Next.

 

23.

Select Radio Group and click Next.

 

24 .

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

 

25 .

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

 

26.

Enter the following values and click Apply.

Display Value Return Value
Dynamic Layout 1 DynamicLayout1
Dynamic Layout 2 DynamicLayout2

 

27.

Click Next.

 

28.

Accept the defaults and click Next.

 

29.

Enter DynamicLayout1 for the Default and click Create Item.

 

30.

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.

 

31 .

Select the Dynamic Report Generation region and click Next.

 

32.

Accept the default and click Next.

 

33.

Enter Print for the Button Name and click Next.

 

34.

Accept the default and click Next.

 

35.

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.

 

36.

Click Create Button.

 

37.

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.

 

38.

Select PL/SQL and click Next.

 

39.

Enter print for the Name and click Next.

 

40.

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'
 );    
                            

 

41.

Click Next.

 

42.

Select Print for When Button Pressed and click Create Process.

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

 

Back to Topic List

Running a Report Dynamically

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

1.

Click Run.

 

2.

Select Save / Open in Separate Window and click Print.

 

3.

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

 

4.

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

 

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

 

6.

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

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Produce multiple report layouts
 Create selection criteria items and processes for report generation
 Run a report dynamically

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

Left Curve
Popular Downloads
Right Curve
Untitled Document