1

Oracle Application Express:

Application Development
Foundations
3-2
Developing Reports

Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

2
 Lesson Objectives
• After completing this lesson, you should be able to:
−List the type of reports that you can create in Application
Express
−Describe the different ways of creating reports in Application
Express
−Create an interactive report
−Create an interactive grid
−Create a classic report
−Create a report with form
−Create a faceted search page

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 3

In this lesson, you learn the different types of reports that you can create in Application Express. You
learn how to use the Create Application and Create Page wizards to create different types of reports.
This lesson also includes how to use the context menus and drag and drop feature in page designer to
create report regions on an existing page in a database application.

3
 Understanding Different Report Types
• A report is the formatted result of a SQL query
Interactive Report Report with Form

Classic Report
Interactive Grid

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 4

In Oracle Application Express, a report is the formatted result of a SQL query. You can generate the SQL
query by selecting a table or view in a wizard, or by defining the SQL query manually.
The slide shows examples four basic report types:
• Interactive Report
• Interactive Grid
• Report with Form
• Classic Report
The main difference between these different report types is the extent and way in which end users can
customize the appearance of the data through searching, filtering, sorting, column selection,
highlighting, and other data manipulations. You learn more about each of these report types in this
lesson.

4
 Selecting an Appropriate Report Type

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 5

The slide shows three different report types: Interactive Report, Interactive Grid, and Classic Report.
• Interactive Report: When viewing an interactive report, end users can customize how and what
data displays. By default, interactive reports include a search bar, an Actions menu, column heading
menus, and Edit icons in the first column of each row. Using options on the Actions menu, users can
alter the report layout by hiding or exposing specific columns and applying filters, highlighting, and
sorting. You choose an interactive report if your requirement is end user customization.
• Interactive Grid: An interactive grid presents users a set of data in a searchable, customizable
report. In an editable interactive grid, users can also add to, modify, and refresh the data set directly
on the page. Functionally, an interactive grid includes most customization capabilities available in
interactive reports plus the ability to rearrange the report interactively using the mouse. You choose
an interactive grid if your requirement is to allow end user editing and refreshing directly on the
report page along with end user customization of the report.
• Classic Report: If you do not need any of the customization, or built-in search bar and drill-down
capabilities, then you choose a Classic Report.

5
 Understanding Different Ways of Creating Reports
• Create a report when you create a new database
application
• Create a report as a new page in an application
• Create a report region on a page in an application
Creating a report when you create a new app

Creating a report as a new page in an app

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 6

Application Express allows you to create reports by running a wizard, or by dragging and dropping the
region type in Page Designer. App Builder includes built-in wizards that enable you to generate reports.
Using these wizards, you can create a report when you create the following:
• A database application
• A new page in a database application
When you use the Create Application Wizard and add a report page, supported reports include cards,
faceted search, interactive report, classic report, multiple reports, and interactive grid.
Use the Create Page Wizard to create a wide variety of reports on a local database or on an external
data source.When you create a page using the Create Page Wizard, supported reports include
interactive report, interactive grid, faceted search, classic report, report with form, list view, column
toggle report, or reflow report.
The slide shows the Create Application wizard in which you click the Add Page button to add a report
page.
The slide also shows the Create Page wizard in which you select the Report component.
See the next slide for more information about how to create a report region on a page.

6
 Understanding Different Ways of Creating Reports
• Creating a report region on a page in an application

Drag and drop from the gallery

Using the
context menu

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 7

You can also create a report as a region on an existing page in an application. To do this, you view the
page definition in page designer. Then, you create a report region by using any of the following options:
• Drag and drop a report region from the gallery
• Use context menu under the Rendering tab. Click the Rendering tab on the left-pane, and then
right-click Regions to view the context menu. Select Create Region
• Use gallery context menu. Right-click a report region in the gallery to view a context menu. From
the Add To option, select the location where you want to add the report region on the page.

7
 Creating a Report Using the Create Application Wizard

1 3

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 8

By running the Create Application Wizard, you can create a new application that contains one or multiple report pages. Supported report
pages using the Create Application Wizard include cards, faceted search, interactive grid, interactive report, and classic report The create
application wizard has already been covered in detail in the lesson titled Creating a Database Application.
To create an interactive report or a classic report using the create application wizard, perform the following steps:
1. On your Workspace home page, click App Builder and then click Create. Select New Application and click Next. Enter a name for
your application. Accept the remaining defaults and to add a page, click Add Page.
2. In the Add Page dialog, select a report of your choice and follow the on-screen instructions.
3. For example, in the slide, select Interactive Report. In the Add Report Page dialog, perform the following steps:
a) Enter a name for the report
b) Select an icon to display in the navigation menu for this page.
c) Select the source for the report: Table or View or SQL Query. What you select determines what displays next. In the slide
example, you select Table or View.
d) Now, select the type of report page you want to add: Interactive Report or Classic Report. In the slide example, you accept
the default selection of Interactive Report.
e) Select the Table or View. In the slide example, you select EBA_DEMO_IR_EMP.
f) Optionally select the Include Form check box to include a linked form page to enable users to maintain the table values.
g) Expand Lookup Columns to see attributes for defining a lookup to another table. Use Lookup Columns to replace identifiers
with a display column, such as showing the department name instead of the department number.
Note: Lookup Columns only appears if the current page is based on a table or view and has foreign key constraints to
another table.
h) Follow the on-screen instructions and then click Add Page.
i) Accept the defaults and click Create Application.

You learn how to create an interactive grid by using the Create Application Wizard later in this lesson.

8
 Creating a Report Using the Create Page Wizard
1
2

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 9

In an existing application, you use the Create Page Wizard, to create a wide variety of reports. You can create reports on a local
database or on an external data source. The Create Page Wizard supports reports such as interactive report, interactive grid,
classic report, faceted search, report with form on table, list view, column toggle report, reflow report, or report on a web
service.
To create a report as a new page, perform the following steps:
1. Navigate to your application home page. Click Create Page
2. Select Report for page type and click Next
3. Select a report type from the following options:
• Interactive Report
• Interactive Grid
• Faceted Search
• Classic Report
• Report with Form
• List View (optimized for mobile apps)
• Column Toggle Report (optimized for mobile apps)
• Reflow Report (optimized for mobile apps)
Click Next. Follow the on-screen instructions.

Note: The slide describes how to create a report by running the Create Page Wizard from the Application home page. You can
also run this wizard in Page Designer by clicking the Create menu and selecting Page.

9
 Creating a Report Region in Page Designer

Using context menu

Using gallery context menu

Dragging and dropping from the gallery

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 10

You can create a new report region on an existing page in an application. When you create a new report region on a page, you have the
following two options in page designer:
• Drag and drop region from the gallery
• Use the context menu
To drag and drop a report region, perform the following steps:
1. On your application home page, click a page
2. View the page definition in page designer.
a) In the gallery at the bottom of the central pane, locate the report region you want to add. The gallery lists all controls or
components you can add to a page. Passing the cursor over a control or component displays a tooltip that describes it.
b) Click and hold the mouse on the region and drag it to the desired location on the Layout tab. When the mouse is over the
appropriate location, the Layout tab displays as a darkened yellow tile. Release the mouse to drop the region.
c) Page Designer indicates what actions are required next. The Messages tab displays a red or yellow badge indicating the
number of messages you need to address. Edit the appropriate attributes in Property Editor.
d) Then, click Save or Save and Run Page
To use the context menu, perform the following steps:
1. On your application home page, click a page
2. View the page definition in page designer.
a) In the left pane, select Rendering tab.
b) Right-click the Regions node and select Create Region.
c) Edit the appropriate attributes in the Property Editor.
d) Then, click Save or Save and Run Page.
Note: Apart from dragging and dropping from the gallery, you can also use the gallery context menus.

10
 What is an Interactive Report?

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 11

An interactive report is a formatted result of a SQL query. Both the Create Application Wizard and
Create Page Wizard support the creation of interactive reports. You choose a table on which to build a
report, or provide a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT
statement. Interactive reports are only supported for Desktop applications. End users can customize the
report layout and data displayed by selecting options on the Actions menu.
You can restrict the capabilities of interactive reports available to end users (such as disabling download
or support for hiding column).
When viewing an interactive report, end users can customize how and what data displays. By default,
interactive reports include a search bar, an Actions menu, column heading menus, and Edit icons in the
first column of each row. Using options on the Actions menu, users can alter the report layout by hiding
or exposing specific columns and applying filters, highlighting, and sorting. They can also define breaks,
aggregations, charts, group bys, and add their own computations. Once customized, the report can be
saved as either a private or public report.
When defining an interactive report you can:
• Include multiple interactive reports per page
• Display 100 columns using report columns.
• Include 999 rows per column heading filter (if no custom LOV is specified in the column attributes)

11
 Creating an Interactive Report Region: Example

Using context menu

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 12

To use the context menu, perform the following steps:

1. On your application home page, click a page
2. View the page definition in page designer.
a) In the left pane, select Rendering tab.
b) Right-click the Regions node and select Create Region.
3. Edit the appropriate attributes in the Property Editor.
• Under Identification, enter the title of the report and for Type, select Interactive Report
• Under Source, for Type, select Table / View and then choose a table name for Table
Name. Alternatively, if you select SQL Query for Type, then you click the Code Editor and
enter a SELECT statement. In the slide example, you select EBA_DEMO_IR_PROJECTS for
Table Name.
4. Then, click Save or Save and Run Page.

12
 What is an Interactive Grid?

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 13

An interactive grid presents users a set of data in a searchable, customizable report. In an editable
interactive grid, users can also add to, modify, and refresh the data set directly on the
page. Functionally, an interactive grid includes most customization capabilities available in interactive
reports plus the ability to rearrange the report interactively using the mouse.
Users can lock, hide, filter, freeze, highlight, and sort individual columns with the Actions and Column
Heading menus. Advanced users can also define breaks, aggregations, and computations against
columns. Users can also directly customize the appearance of an interactive grid. Users can use the
mouse to resize the width of a column and drag and drop columns into different places in the grid. Both
the Create Application wizard and Create Page wizard support the creation of interactive grids.
Note: Interactive Report generates all the HTML markup on the server as part of the APEX page. The
data returned from the SQL query is rendered along with all the other markup that makes up the UI; the
toolbar, the dialogs, and everything. The client side behaviors are implemented in a single monolithic
JavaScript module. In contrast, Interactive grid does all the HTML rendering on the client. The server
provides the data in JSON format. Edits in an interactive grid are saved to the server in JSON format as
well.

13
 Understanding Interactive Grid: Key Features
• Modern, rich, and interactive multi-row editing
component
• JSON-based, lightweight AJAX communications
• Extensible and customizable
• Accessible, key board support, right-to-left support
• Upgrade utility for tabular forms
• Master Detail / Detail

Master Detail Layout – Any Combination

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 14

Interactive grid is a rich, client-side region type that allows rapid editing of multiple rows of data in a
dynamic, JSON-enabled grid. You can use multiple interactive grids on a single page. Interactive grid
includes all the features you expect for powerful reporting, including fixed headers, frozen columns,
scroll pagination, multiple filters, sorting, aggregates, computations, and more. You can easily edit your
text, numerical data, list of values, and much more.
Built using modern guidelines and techniques, this component of Application Express provides
outstanding keyboard support and usability.
Interactive grid is designed to support all item types and item type plug-ins. With interactive grids, you
can easily create master-detail relationships, and go any number of levels deep and across. You can
create all types of master-detail-detail screens with ease. The slide graphic shows Master Detail Layout
of any combination

14
 Creating an Interactive Grid: Example 1

Using the Create Application Wizard

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 15

The slide example shows how to create an interactive grid using the Create Application Wizard. Perform the
following steps:
1. On your Workspace home page, click App Builder and then click Create. Select New Application and click
Next. Enter a name for your application. Accept the remaining defaults and to add a page, click Add Page.
2. In the Add Page dialog, click Interactive Grid.
3. In the Add Interactive Grid Page dialog:
a) Enter a name for the page
b) Select an icon to display in the navigation menu for this page.
c) Select the source for the report: Table or View or SQL Query. What you select determines what
displays next. In the slide example, you select Table or View.
d) Now, select the grid type you want to add: Allow Editing or Read Only. In the slide example, you select
Read Only.
e) Select the Table or View. In the slide example, you select EBA_DEMO_IG_EMP.
f) Follow the on-screen instructions and then click Add Page.
g) Accept the defaults, and click Create Application.

Note: If you select a Query Type of SQL Query plus a Grid Type of Allow Editing and your query joins two or more
tables, then the interactive grid generated will fail when users try and update records. Also, if you select Grid Type
of Allow Editing and the query (or selected Table or View) uses a view that is not updateable, then the Interactive
Grid generated will fail when users try and update records.

15
 Creating an Interactive Grid: Example 2
2
1

Using the Create Page Wizard

3
ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 16

The slide example shows how to create an interactive grid using the create page wizard. Using the
create page wizard, you can create interactive grids on local database on an external data source. To
create an interactive grid, perform the following steps:
1. Navigate to your application home page. Click Create Page. Select Report for page type and click
Next.
2. Select Interactive Grid for report type and click Next.
3. For Page Attributes, enter a name for Page Name, accept the remaining defaults and click Next.
In this step, you can specify if you want to use a breadcrumb navigation control on your page.

16
 Creating an Interactive Grid: Example 2
4

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 17

4. Select how you want this page to be integrated into the navigation menu and click Next. In the slide
example, you select / enter:
• Create new navigation menu entry for Navigation Preference
• Projects Grid for New Navigation Menu Entry
5. For Report Source:
• Select if your data source is Local Database, REST Enabled SQL Service or Web Source. In the slide
example, the Data Source is Local Database.
• Select whether to enable editing for this Interactive Grid. The default selection is No.
• Specify the source type to be either a table or a SQL query:
➢ If you select SQL Query source type, enter the SQL and click Create.
➢ If you select Table source type, select the table name, select the columns to be displayed in the
report and click Create.
Note:
Data Sources that you can use for APEX components are:
• Local Database
Data is sourced from the local database.
• REST Enabled SQL Service
Data is sourced from a remote database, where the connection is defined using REST Enabled SQL. To
create or maintain REST Enabled SQL references, navigate to Shared Components > REST Enabled SQL.
• Web Source
Data is sourced from a RESTful web service defined using Web Source Modules. To create or maintain Web
Source Modules, navigate to Shared Components > Web Source Modules.

17
 What is a Classic Report?

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 18

A classic report is the formatted result of a SQL query. Developers choose a table on which to build a
report, or provide a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT
statement. With the exception of sorting and simple filtering, end users cannot customize a classic
report.

18
 Creating a Classic Report in Page Designer: Example

Dragging and dropping from the gallery

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 19

The slide example shows creating a Classic Report by dragging the report region from the gallery and
dropping it in the CONTENT BODY section of the page.
To drag and drop a classic report region, perform the following steps:
1. On your application home page, click a page
2. View the page definition in page designer.
a) In the gallery at the bottom of the central pane, select Classic Report.
b) Click and hold the mouse on Classic Report and drag it to the desired location on the
Layout tab. When the mouse is over the appropriate location, the Layout tab displays as a
darkened yellow tile. Release the mouse to drop the region.
c) Page Designer indicates what actions are required next. The Messages tab displays a red
or yellow badge indicating the number of messages you need to address. Edit the
appropriate attributes in Property Editor. In the slide example, under Source, for Type,
you select SQL Query. Then, click the Code Editor icon and enter the SQL source for the
report
d) Then, click Save or Save and Run Page

19
 Creating a Report with Form

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 20

The slide example shows a report and form created on a table. In the report page, each row provides a
link (Edit icon) to the second page (form) to enable users to update each record.
Report with Form creates a report (that is, an interactive grid, an interactive report, or a classic report)
and form on a table or by providing a SQL query.
Using the Create Application and Create Page wizards you can create a report and form combination.
The main difference between these two wizards is that the Create Page Wizard offers more
customization and data source options. Using the Create Page Wizard, you select the report type (that
is, interactive grid, interactive report, or classic report) and then a data source (that is, Local Database,
REST Enabled SQL Service, or Web Source Module). Additionally, you can also specify whether to include
and configure breadcrumbs or a navigation menu and select the columns and the order in which they
display.

20
 Creating a Report with Form: Example
1

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 21

The slide example shows creating a report with Form by using the Create Page wizard. Perform the
following steps:
1. Navigate to your application home page. Click Create Page. Select Report for page type and click
Next. Select Report with Form and click Next.
2. Specify the page attributes and click Next.
• Report Page Name: Enter the title of the page that will contain the report. In the slide
example, enter Projects Report
• Form Page Name: Enter the title of the page that will contain the form. In the slide example,
enter Project Details.
• Form Page Mode: Select the page mode to be either Normal or Modal Dialog. You already
learned about page modes.
• Breadcrumb: Select whether you want to use a breadcrumb navigation control on your
page. Select a page for Parent Entry and specify the breadcrumb name for Entry Name

21
 Creating a Report with Form: Example

4
ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 22

3. Specify your preferences for navigation menu and click Next

4. Select the Data Source type for the Report with Form. Local Database is selected by default.
Select the table or view on which the report will be based. In the slide example, you select
EBA_DEMO_IR_PROJECTS table. Select column(s) that you want to be shown in the report and
click Next
Note:
Data Sources that you can use for APEX components are:
• Local Database
Data is sourced from the local database.
• REST Enabled SQL Service
Data is sourced from a remote database, where the connection is defined using REST Enabled SQL.
To create or maintain REST Enabled SQL references, navigate to Shared Components > REST Enabled
SQL.
• Web Source
Data is sourced from a RESTful web service defined using Web Source Modules. To create or
maintain Web Source Modules, navigate to Shared Components > Web Source Modules.

22
 Creating a Report with Form: Example

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 23

5. Select your choice for Primary Key Type. Choose Managed by Database (ROWID) to have the
form use the ROWID pseudo column to identify rows to update and delete. Choose Select
Primary Key Column(s) to use the source table's primary key column(s).
Select column(s) to be included in the form and click Create.
To view the report and form that you just created, click Save and Run Page.

23
 Creating a Faceted Search Page

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 24

Faceted Search is seen often on the internet typically on shop or sales web sites. The end
user can set filters using Facets on the left or upper side of the screen. A facet shows
possible values together with the occurrence count within the result set. After the end user
changed a facet, results, dependent facets and occurrence counts refresh immediately.

A faceted search page features a faceted search region and report. The faceted search region
displays on the left side of the page and enables users to narrow down the search result by
selecting facet values. Narrowing the search result, makes it easier for users to find the data
they want. The right side of the results region is a classic report which displays in report or
card view. Both the Create Application Wizard and Create Page Wizard support the creation
of faceted search pages. The slide shows an example of the faceted search page created on
the OEHR_EMPLOYEES table.

24
 Creating a Faced Search Page: Example

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 25

The slide shows an example of creating a faceted search page on the OEHR_EMPLOYEES
table. In the example, you use the Create Page Wizard.
To add a new faceted search page to an existing application, perform the following steps:
1. On your application home page, click Create Page.
2. Select Report and then select Faceted Search.
3. Specify the page attributes and click Next.
4. Select your navigation preference and click Next
5. Select a data source for the page. You can create a Faceted Search page for tables
and SQL queries; for the local database, REST Enabled SQL services and REST services.
For local tables, APEX maintains a dictionary cache, containing information about
table columns, foreign keys, and actual data,. Based on this cache, APEX can propose
appropriate facets and create them automatically.
6. Click Create.

In the slide example, based on the dictionary cache, APEX is able to follow the foreign key
relationship to the OEHR_DEPARTMENTS table, and to create the facet with appropriate
display values.

25
 Summary
• In this lesson, you learned how to:
−List the type of reports that you can create in Application
Express
−Describe the different ways of creating reports in Application
Express
−Create an interactive report
−Create an interactive grid
−Create a classic report
−Create a report with form
−Create a Faceted Search

ADFo 3-2
Developing Reports Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 26

In this lesson, you learned how to create different types of reports in Oracle Application Express.

26
Developing Reports
Go to Practice Activities

Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

27
28