3 How to Create a Parameterized Report

This tutorial provides an introduction to Application Express report types, illustrates how to create a parameterized report, and demonstrates how the user can dynamically manipulate the interactive report format.

This tutorial contains the following topics:

In an Oracle Application Express application, a report is the formatted result of an SQL query. A parameterized report is a dynamic report based on input from the application user or another component in the application. The application user enters search criteria which is used to generate the report. For example, the user may want to see all issues assigned to a particular person. The user inputs the person's name into the Search Bar and requests the report. The report is generated based on the name provided by the user.

In this tutorial, you create an interactive report page based on an SQL query of a table of issues. A form page is also created and is used to modify, remove or create issues. The interactive report features allow the user to customize the focus and formatting of the report.

Introducing Application Express Reports

This section describes the basic Application Express report types, and explains how to know which one is best for your application.

This section includes these topics:

Overview of Report Types

There are two basic types of reports, an interactive report and a classic report.

The interactive report is the default report type when creating an application, converting forms, and creating pages. Interactive reports enable the user to perform a variety of report customizations. Unless disabled by the developer, an interactive report includes the ability to perform searching, filtering, sorting, column selection, highlighting, and other data manipulations. For a complete list of capabilities and how to use them, see "Using an Interactive Report". See Figure 3-1 for an example of an interactive report that queries the IT_PEOPLE table and was created using the Create Page Wizard.

Figure 3-1 Interactive Report

Description of Figure 3-1 follows
Description of "Figure 3-1 Interactive Report"

Notice the automatically built in Search Bar, Column Heading Menu links, and Link Column icons in the first column of each row. For a complete description of each of these components, see Introducing Interactive Report Components.

A classic report does not by default include any of the interactive report customization features. See Figure 3-2 for an example of a classic report that was built with the Create Page Wizard and queries the same columns in the IT_PEOPLE table as the interactive report in Figure 3-1 queries.

Figure 3-2 Classic Report

Description of Figure 3-2 follows
Description of "Figure 3-2 Classic Report"

Notice there is no search bar, no column heading links and no drill down capability.

Selecting the Appropriate Report Type

If you want user customization capability such as search, filtering and sort controls, select an interactive report. You can tailor the customization options available to the user by enabling and disabling the individual options. If your report does not need such controls you should consider a classic report. Classic reports also have integrated tabular form capabilities that allow declarative grid update.

Creating an Application with Interactive Reports

In this section, you create an application using the Create Application Wizard and then run the application. When you finish this section, your application will have a Home page, Issues report page, and a Issue Details form page similar to Figure 3-3, "Home Page", Figure 3-4, "Issues Report", and Figure 3-5, "Issue Details Form".

Figure 3-3 Home Page

Description of Figure 3-3 follows
Description of "Figure 3-3 Home Page"

Figure 3-4 Issues Report

Description of Figure 3-4 follows
Description of "Figure 3-4 Issues Report"

Figure 3-5 Issue Details Form

Description of Figure 3-5 follows
Description of "Figure 3-5 Issue Details Form "

This section contains the following topics:

Create and Populate the Database Objects

Before you begin creating the application, you need to create the appropriate sample objects within your workspace and load them with demonstration data.

To install the Issue Tracker sample objects:

  1. Create the Issue Tracker data objects. Follow instructions outlined in "Create and Run a Script to Build Database Objects" .

  2. Load objects with demonstration data. Follow instructions described in "Loading Demonstration Data" .

    These sample objects are copies of the Issue Tracker application objects. For a complete description of the Issue Tracker application see Chapter 14, "How to Design an Issue Tracking Application" and Chapter 15, "How to Build and Deploy an Issue Tracking Application".

Create the Application

The Create Application Wizard creates the Home page, Issues report page, and Issue Details page. Because you are creating an interactive report, the wizard automatically includes a Search Bar, Actions Menu, Column Heading Menu, and a link from the Issues report page to the Issue Details form.

See Also:

For detailed information on how to use these interactive report components refer to "Using an Interactive Report". For information on how to configure these components, see "Editing Interactive Reports" in the Oracle Database Application Express User's Guide, "Building Your Application" in the Oracle Database 2 Day + Application Express Developer's Guide,

To create an application using the Create Application Wizard:

  1. On the Workspace home page, click the Application Builder icon.

    The Application Builder home page appears.

  2. Click Create.

  3. Select Create Application and click Next.

  4. For Name, specify the following:

    1. Name - Enter Parameterized Report.

    2. Application - Accept the default.

    3. Create Application - Select From scratch.

    4. Schema - Select the schema where you installed the OEHR sample objects.

    5. Click Next.

      Next, add a blank page.

  5. Under Add Page, specify the following:

    1. Select Page Type - Select Blank.

    2. Page Name - Enter Home.

    3. Click Add Page.

      The new page appears in the list at the top of the page.

  6. Under Add Page:

    1. Select Page Type - Select Report and Form.

    2. Subordinate to Page - Select Home (1).

    3. Table Name - Select IT_ISSUES.

    4. Implementation - Accept default of Interactive.

    5. Click Add Page.

      The It_Issues Report link and It_Issues Form link appear in the Create Application region.

      Next, you change the name of the report and form from the default names.

  7. Click the It_Issues link next to Report at the top of the page.

  8. Under Page Definition, enter Issues for Page Name.

  9. Under Report Columns, for Show select No for these columns:

    1. ISSUE_DESCRIPTION

    2. RESOLUTION_SUMMARY

    3. CREATED_ON

    4. CREATED_BY

    5. MODIFIED_ON

    6. MODIFIED_BY

  10. Click Apply Changes.

  11. Click It_Issues link next to Form.

  12. Under Page Definition, enter Issue Details for Page Name.

  13. Click Apply Changes.

    The Issues Report link and Issue Details Form link appear in the Create Application region.

  14. Click Next.

  15. For Tabs, accept the default, One Level of Tabs, and click Next.

  16. For Copy Shared Components from Another Application, accept the default, No, and click Next.

  17. For Attributes, accept all defaults and click Next.

  18. For User Interface, select Theme 18 and click Next.

    A theme is collection of templates that define the layout and style of an application. You can change a theme at any time.

    See Also:

    "Managing Themes" in Oracle Database Application Express User's Guide

  19. Review your selections and click Create.

    The Application home page appears.

Figure 3-6 Application Home Page

Description of Figure 3-6 follows
Description of "Figure 3-6 Application Home Page"

Preview the Application

Next, you run the application and navigate from the Home page to the Issues report and then to the Issue Details form.

To run the application:

  1. Click the Run Application icon.

  2. Enter your workspace login information.

    The Home page appears.

  3. Click the Issues link.

    The Issues Report displays.

    Figure 3-7 Issues Report

    Description of Figure 3-7 follows
    Description of "Figure 3-7 Issues Report"

  4. For the first row, click the Edit icon.

    The Issue Details form appears as shown in Figure 3-8.

    Figure 3-8 Issue Details Form

    Description of Figure 3-8 follows
    Description of "Figure 3-8 Issue Details Form"

    The Create Application Wizard configured the Issues interactive report Link Column to target the Issue Details form. This is the default when the wizard creates a page type of Report and Form.

    Tip:

    The Link Column can be reconfigured by going to the Report Attributes page and changing the Link Column setting. See Introducing Interactive Report Components.

  5. Click Cancel to go back to the Issue Report.

  6. Click the Create button.

    An empty Issue Details form appears. This form enables you to add an issue to the IT_ISSUES table. At this point, do not create a new issue.

  7. Click the Application ID link on the Developer toolbar at the bottom of the page.

Add Columns of Data from Other Tables

Next, you add columns of data from the IT_PEOPLE and IT_PROJECTS tables. When you created the Issues report page with the Create Application Wizard, only data from the IT_ISSUES table was included in the SQL query. To add data in this report from tables other tables, you modify the Issues report SQL query and then use the Actions menu to include the added columns in the report.

To change the SQL query:

  1. Click the 2 - Issues page.

  2. Under Regions, click Issues.

  3. For Source, replace with the following SQL:

    SELECT     "IT_ISSUES"."ISSUE_ID" as "ISSUE_ID",
    "IT_ISSUES"."ISSUE_SUMMARY" as "ISSUE_SUMMARY",
            "IT_PEOPLE"."PERSON_NAME" as "IDENTIFIED_BY",
            "IT_ISSUES"."IDENTIFIED_DATE" as "IDENTIFIED_DATE",
            "IT_PROJECTS"."PROJECT_NAME" as "PROJECT_NAME",
            decode("IT_PEOPLE_1"."PERSON_NAME",NULL,'Unassigned',
    "IT_PEOPLE_1"."PERSON_NAME") as "ASSIGNED_TO",
            "IT_ISSUES"."STATUS" as "STATUS",
            "IT_ISSUES"."PRIORITY" as "PRIORITY",
         "IT_ISSUES"."TARGET_RESOLUTION_DATE" as "TARGET_RESOLUTION_DATE",
            "IT_ISSUES"."PROGRESS" as "PROGRESS",
            "IT_ISSUES"."ACTUAL_RESOLUTION_DATE" as "ACTUAL_RESOLUTION_DATE" 
FROM "IT_PEOPLE" "IT_PEOPLE_1",
            "IT_PROJECTS" "IT_PROJECTS",
            "IT_PEOPLE" "IT_PEOPLE",
            "IT_ISSUES" "IT_ISSUES" 
WHERE "IT_ISSUES"."IDENTIFIED_BY_PERSON_ID"="IT_PEOPLE"."PERSON_ID"
AND "IT_ISSUES"."ASSIGNED_TO_PERSON_ID"="IT_PEOPLE_1"."PERSON_ID"(+)
AND      "IT_ISSUES"."RELATED_PROJECT_ID"="IT_PROJECTS"."PROJECT_ID"

  4. Click Apply Changes.

  5. Click Apply Changes to Confirm.

To display the new columns:

  1. Click the Run Page 2 icon.

  2. Click the Actions menu as shown in Figure 3-9.

    Figure 3-9 Actions Menu Icon

    Description of Figure 3-9 follows
    Description of "Figure 3-9 Actions Menu Icon"

  3. Select the Select Columns option.

    The Select Columns options appear showing the Identified By, Project Name and Assigned To columns in the Do Note Display box. When you modify the query and columns are added, they do not appear in the report automatically and need to be added.

    Figure 3-10 Select Columns Options

    Description of Figure 3-10 follows
    Description of "Figure 3-10 Select Columns Options"

  4. Use the Move All arrows (>>) to move all columns from the Do Not Display box to the Display in Report box.

  5. Select the Issue Id column in the Display in Report box and click the Remove arrow (<) to move it to the Do Not Display box.

  6. Using the up and down arrows to the right of the Display in Report box, reorder the columns as follows:

    1. Issue Summary

    2. Identified By

    3. Identified Date

    4. Project Name

    5. Assigned To

    6. Status

    7. Priority

    8. Target Resolution Date

    9. Progress

    10. Actual Resolution Date

  7. Click Apply.

    The Issue report appears with the new columns.

    Figure 3-11 Issues Report with Additional Columns

    Description of Figure 3-11 follows
    Description of "Figure 3-11 Issues Report with Additional Columns"

Using an Interactive Report

This section introduces the basic interactive report components then teaches you how an application user can manipulate these components to customize an interactive report. Only some of the most common customizations are shown in this section. For a detailed description of how to use each feature, see "Editing Interactive Reports" in the Oracle Database Application Express User's Guide .

Topics included in this section:

Introducing Interactive Report Components

This section provides a basic understanding of interactive report components and terminology.

In Figure 3-12, the basic interactive report components are identified in red font:

Figure 3-12 Interactive Report Components

Description of Figure 3-12 follows
Description of "Figure 3-12 Interactive Report Components"

Unless disabled by the developer, the following components are by default included on an interactive report page:

  • Search Bar - Displays at the top of each interactive report page. Provides the following features:

    • Select columns icon looks like a magnifying glass and enables you to identify which column to search (or all).

    • Text area enables you to enter case insensitive search criteria (wild card characters are implied).

    • Rows selects the number of records to display per page.

    • Go button executes the search.

    • Actions Menu icon displays the actions menu.

  • Actions Menu - Used to customize the display of your interactive report. Provides the following options:

    • Select Columns specifies which columns to display and in what order.

    • Filter focuses the report by adding or modifying the WHERE clause on the query.

    • Sort specifies which columns to sort on and whether the order is ascending or descending.

    • Control Break creates a break group on one or several columns.

    • Highlight enables you to define a filter and highlight the rows that meet the filter criteria.

    • Compute enables you to add computed columns to your report.

    • Aggregate enables users to perform mathematical computations against a column.

    • Chart adds a chart to your interactive report.

    • Flashback performs a flashback query enabling you to view the data as it existed at a previous point in time.

    • Save Report saves the current customized report settings so they can be used in the future.

    • Reset enables you to reset the report back to the default report settings.

    • Help provides detailed descriptions of how to use the interactive report components to customize your reports.

    • Download enables the current result set to be downloaded.

  • Column Heading Menu - Clicking on any column heading exposes a column heading menu that enables you to change the sort order, hide columns, create break groups on a column, view help text about the column and create a filter.

  • Report Settings - If you customize your interactive report, the report settings are displayed below the Search Bar and above the report. If you save customized reports, they are shown as tabs.

  • Link Column - This column links to a Single Row View or to a Custom Target. In this particular interactive report, shown in Figure 3-12, "Interactive Report Components", the link column is a Custom Target, displayed as an Edit icon, that links to the Issue Details form enabling the user to view and modify the selected issue information.

    The Link Column is specified by the interactive report's Link Column setting on the Report Attributes page. There are three possible selections for this setting:

    • Link to Single Row: This option enables the user to view details for a single row. The Single Row View enables the user to view data, not modify it. Link to Single Row is the default setting when creating an interactive report of page type Report. See the Issues interactive report page created in Chapter 4, "Using Advanced Report Techniques" for an example.

    • Link to Custom Target: This option enables the user to go to another page in the application or to a URL, depending on which target was specified by the developer. Link to Custom Target is the default setting when creating an interactive report of page type Report and Form. The target defaults to the form page where the user can modify data for that row. The Issues interactive report in this application is an example of this type of link.

    • Exclude Link Column: This option removes the Link Column from the interactive report.

Next, you examine some of these features to customize the appearance of your report and specify what data to include.

Adding a Filter to a Report

In this section, you add a filter to the report from the Search Bar. The filter displays all issues assigned to Carla. Keep in mind, however, that there are other ways to add a filter to your report. You can add a filter by using the:

  • Search Bar

  • Column Heading Menu

  • Actions Menu

To add a filter to the report from the Search Bar:

  1. Make sure page 2 is still running and click the Select Columns icon in the Search Bar (the icon looks like a magnifying glass).

    A list of report columns appears.

    Figure 3-13 List of Columns for Search

    Description of Figure 3-13 follows
    Description of "Figure 3-13 List of Columns for Search"

  2. Select Assigned To.

    The Assigned To column appears in the Search Bar.

    Figure 3-14 Assigned To in the Search Bar

    Description of Figure 3-14 follows
    Description of "Figure 3-14 Assigned To in the Search Bar"

  3. Enter carla in the text area and click Go.

    The Issues report is displayed showing issues assigned to anyone with "carla" in their first or last name. The search criteria is not case sensitive.

    Figure 3-15 Filter for Issues Assigned to Carla

    Description of Figure 3-15 follows
    Description of "Figure 3-15 Filter for Issues Assigned to Carla"

    Notice the filter Assigned To contains 'carla' has been added to the Report Settings area above the report. You can edit, disable or delete the filter.

    Next, you disable then enable the filter.

  4. Click the Enable/Disable check box next to Assigned To contains 'carla' to disable the filter (the check box is now unchecked).

    The report is displayed showing all issues.

  5. Click the Enable/Disable check box next to Assigned To contains 'carla' to once again enable the filter (the check box is now checked).

    The report is displayed showing issues assigned to Carla.

  6. Click the Assigned To contains 'carla' link.

    The Filter edit options are displayed.

    Figure 3-16 Filter Edit Options

    Description of Figure 3-16 follows
    Description of "Figure 3-16 Filter Edit Options"

  7. For Operator, select does not contain.

  8. Click Apply.

    The name of the filter is changed and the report shows issues assigned to everyone with the exception of those assigned to Carla.

  9. Next delete the filter. Click the Filter Delete icon to the right of the Enable/Disable checkbox (it looks like a filter with an X over it).

    The filter is removed from the report settings area and the report appears with all issues displayed.

Saving Report Settings and Selecting Columns

In this exercise, you save the current report settings as the default and customize the report to only show a few columns. You save the customized report settings for future use.

To save the report settings as the default:

  1. Click the Actions menu and select Save Report.

    The Save Report options region appears.

  2. For Save, select As Default Report Settings.

  3. Click Apply.

    The current report format is now the default. When the user resets the report, these settings are applied. For reset instructions, see Resetting to the Default Report Settings.

To customize the report to include only a few columns:

  1. Click the Actions menu and select Select Columns.

    The Select Columns options region appears.

  2. In the Display in Report box hold down the control key and select Issue Summary, Status, Priority, Target Resolution Date and Actual Resolution Date.

  3. Click the Remove arrow (<) to add the selected columns to the Do Not Display box.

  4. Click Apply.

    The report displays with only 5 columns.

    Figure 3-17 Customized Report

    Description of Figure 3-17 follows
    Description of "Figure 3-17 Customized Report"

To save customized report settings:

  1. Click the Actions menu and select Save Report.

    The Save Report options region appears.

  2. For the Save Report options, make these changes:

    1. Save - Select As Named Report

    2. Name - Enter Report 1

    3. Description - Enter Only show 5 columns.

  3. Click Apply.

    The report appears with report tabs at the top and Saved Report = "Report 1" in the Report Settings area.

    Figure 3-18 Save Customized Settings as Report 1

    Description of Figure 3-18 follows
    Description of "Figure 3-18 Save Customized Settings as Report 1"

    Notice there are two tabs, the Working Report and Report 1 tabs. The Report 1 tab highlighted in orange is the current report being displayed. You can customize this report further and save your changes, as shown in the proceeding steps, or you can select the Working Report tab and make changes there. Changes made to the Working Report do not affect any of the saved reports.

    You can edit the saved report tab by clicking the Saved Report = "Report 1" link in the report settings area and making changes to the Save Report options.

    Note:

    Because the Named Report is only for the current user who is logged in, the capability to create a Named Report is only available if the application or page is not publilc and has some sort of authentication. See "Editing Interactive Reports" in the Oracle Database Application Express User's Guide for further information.

You can also remove the saved report. Follow these steps to delete the saved report:

  1. Click the Delete Report icon (it looks like a report with an X over it) next to the Saved Report = "Report 1" link in the report settings area.

  2. Click Apply.

    The customized report with 5 columns appears with the saved report tabs removed.

Resetting to the Default Report Settings

In this section, you reset the report back to the default settings saved at the beginning of "Saving Report Settings and Selecting Columns" .

To reset the report:

  1. Click the Actions menu and select Reset.

  2. Click Apply.

    The report appears with the default report settings applied.

    Figure 3-19 Reset Report

    Description of Figure 3-19 follows
    Description of "Figure 3-19 Reset Report"

Congratulations, you now understand the Application Express reporting basics and are ready to try Chapter 4, "Using Advanced Report Techniques".

Related Documentation

For additional information on this and related topics:

For additional application examples, go to the Learning Library. Search for free online training content, including Oracle by Example (OBE), demos, and tutorials. To access the Oracle Learning Library, go to:

http://www.oracle.com/technetwork/tutorials/index.html