Creating a Classic Report Using the Create Page Wizard and Show Its Region Content Dynamically

On the Workspace home page, click the App Builder icon.

Select the application. In this case Demo Project

Click Create Page.

Create a Page appears and features three tabs: Component, Feature, and Legacy Pages.

Under Component, select Classic Report.

Under Page Definition:

Page Number – The page number is an integer value that identifies a page within an application. Here, we have entered Page Number 5

Name – Specify a text name for this page. Tip: This text is also used for page Title. After page creation, you can modify the Title in Page Designer. Here, we have set Name property to Employee details

Page Mode – Identify the page mode. In our case we have selected Normal page mode.

Include Form Page – Enable this flag to create a form page along with the report page. We do not want Form with this classic report so Include Form Page property set to disabled.

Under Data Source:

Data Source – Select a Data Source for the page:

• 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.
• REST Data Source – Data is sourced from a RESTful web service defined using REST Data Sources. To create or maintain REST Data Sources, navigate to Shared Components

In our case we have selected Local Database for Data Source property

Source Type – Specify the source of the new page. Options include:

• Table/View: The data is retrieved based on a table or view.
• SQL Type: The data is retrieved based on a SQL Query.

In our case we have chosen Table type for Source Type property.

Table / View Owner – Select the owner of the table on which you are building the page.

It will take by default user workspace name here workspace name is WKSP_TESTCOLLECTION

Table/View Name – Select the table or view on which the page will be based.

In our case we have entered table name EMP

Under Navigation:

Navigation – Expand the region and enable or disable navigation. If either flag is enabled, additional attributes appear.

Use Breadcrumb – Set this flag to On, if you want to create a breadcrumb entry for this page. By default, page name will be used as breadcrumb entry name.

Breadcrumb Parent Entry – Select parent breadcrumb entry for the current page, select “No Parent Entry” for top level page.

But in our case top level page entry Home (Page 1)

Use Navigation – Enable to create a navigation menu entry for this page. By default, the page name is used as navigation menu name.

In our case we have set Use Navigation flag enabled.

Navigation Preference Select how you want this page integrated into the Navigation Menu.
It has two options 1. Create a new entry 2. Map to an existing entry

• Create a new entry – Users will be able to navigate to the new page directly from the Navigation Menu. The new entry can be placed anywhere in the existing hierarchy. By default, page name will be used as list entry name.
• Map to an existing entry – Use this option when you want the new page to highlight an existing, top level, menu entry. Users will not be able to navigate to the new page directly from the Navigation Menu. Therefore, You will need to create or define a navigation control on the top level page selected, such as a button, or link in a report, to go to this new page.

In our case we have selected Create a new entry in the Navigation Menu.

Parent Navigation Menu Entry – Identify the parent for this list entry.

In our case we have set it as – No parent selected –

Icon – Identify a icon class for the icon associated with this list entry.

To pick an icon, use the built-in icon picker and select an icon from the popup window.

In our case we have selected fa-table icon which will display in Navigation Menu when you run the application.

Finally Click on Create Page button to create a page.

This is Page Designer UI in which we can see and set different properties of APEX page and components

Run the page

After run the page APEX will render the classic report and display as below

Changing the Classic Report Source Type:

Enter a SQL SELECT statement – Enter the SQL query.

In SQL Query we can write SQL statement for Classic report

When we change Type from Database Table to SQL query it is converted to SQL query from Table

We can update the SQL query here, after update the SQL query we can compile using tick mark checkbox

After compile the SQL query using Compile button you can run the query from Code Editor using Save and Run Page

Here, Classic report will display those employees who are working in department 10 and whose salary is greater than equal to 1500

Dynamic Classic Report and Referencing Page Items in SQL Queries of Classic Report

First add Filter region above the Employee details region and set Type = Statice Content

To add Static region on page, click right on Body component of a page and select Create Region option from the menu list

It will add a New region below the existing Employee details Classic Report region

Now, Give region name to added New region = Filter and set Type = Static Content (Default region type of APEX page region = Static Content

In Layout section set Sequence property = 10 to move Filter region above the Employee details Classic Report region

Then add Select List item P5_DEPTNO on this Filter region.

To add new page item on page region Filter, Right click on Filter region and select Create Page Item option from the Menu

It will add new P5_NEW item on Filter region as shown below, the page item default name will be P5_NEW and Type = Text Field

Now change default Name property P5_NEW to P5_DEPTNO and set Type = Select List and set Label = Departments

Select list item P5_DEPTNO type is Select list which needs List of Values

You can select Shared Component, SQL Query, Static Values, Function Body returning SQL Query

For now, select SQL Query option for List of Types Type and write SQL Query in select area as below

select dname as d, deptno as r from dept order by dname

Also set Display Extra Values property to Yes, Display Null Value property to Yes and Null Display Value to – Select department –

Now we will take the references of this item P5_DEPTNO in Employee details Classic Report region SQL query.

Now, Add P5_DEPTNO page item session state references into SQL query as follow; to do this add P5_DEPTNO in the Page Items to Submit property

Now save validate the SQL query and run the page it will display page UI as follow.

Here, Employee details region will show no data found message on report.

When you select department from the Departments item but still Employee Details report will not show any data

To show data in Employee details region dynamically

First, we will add Refresh button in Filter region as below.

Go to the Filter region and right click on it

When we right click on Filter region it will display menu from which we will select Create Button option

It will add New button with Button Name = New and Label = New

Now change the Button Name = REFRESH, it will also changed the Label property name = Refresh

Now shave the changes and run the page and see the output

Now to show the filtered data in Employee details region we need to add Dynamic Action in Refresh button

It will add Dynamic Action on button Refresh with Name = New

Now change the property Name = Refresh Employee Details and in When section left Event = Click, Selection Type = Button and Button = REFRESH as it is.

On this page there is one Show Messages icon highlight the UI error for this page

When you click on this Shoe Messages icon it will open popup dialog with error message

Now click on this error message it will redirect you on the exact error, here it will redirect on Item(s) property of Affected Elements section of Show Action, By default APEX has Show Action set for Dynamic action.

We will change this Show action to Refresh Action after change the Name property = Refresh Employee Details region of the action

When you click on Action property Select List item which is showing Show value in it. It will display all Action list of items it has.

Now select Refresh option from the list to refresh the Employee details region when we click on Refresh Button

Still It shows Affected Elements section with Error on Item(s) property

Now change the Affected Elements section properties, Set Selection Type = Region then it will change Item(s) property change into Region property according to Selection Type = Region

Now set Region property = Employee details

Once you select Region property = Employee details then Error Message icon removed from the page and now you can Save the changes which we have done for Refresh button Dynamic Action

Now closed the Changes saved message and Run the page.

Now run the page and check the UI as below in which we have applied Dynamic Action on Refresh button

Now change the Departments select list value = Accounting and Click on Refresh button

It will display all employees who are working in the Accounting department

Now change Departments value = FINANCE, then it will display all employee who are working in FINANCE department