Oracle Apex Interactive Report Tutorial

In this tutorial, you will learn how to create an Interactive Report in Oracle Apex.

Creating an Interactive Report in Oracle Apex

I will explain to you by creating an Interactive Report manually on the blank page in Oracle Apex. With this manual method, you will get more clarity then creating it through the wizard.

Create a Blank Page

To create a blank page, open your application in Oracle Apex, and then click on the Create button. The following dialog will open. Select the Blank Page option and click on the Next button and then follow the rest steps.

Create a Region

After completing the wizard, you will land upon the blank page. Now do the right-click on the Region node and select the option Create Region.

Then set the type of newly created region to the Interactive Report. And then immediately, you will notice that the Source section will be visible in the property palette, as shown in the below image:

Specify the Report Source

You can set the report source type as Table or the SQL query. These two options are the most commonly used. There is also an option PL/SQL Function Returning SQL Query, which you can use to build a report on a dynamic SQL query.

If the source type is a table, then specify the table name in the Table Name field, as shown in the above image. Also, define the where condition and set the items in Page items to Submit field if any page item is being used in the Where Clause. The following is a Where Clause condition example:

admission_date = to_date(:p22_adm_date)

The specify the :p22_adm_date in the Page Items to Submit field.

If you are choosing the report source as SQL Query, then specify the SQL query and set the properties as shown in the below image:

If you need to specify the Where Condition, then you can specify it in the query as we usually do. Below is an example:

select ST_ID,
       STUDENT_NAME,
       ROLL_NO,
       FATHERS_NAME,
       MOTHERS_NAME,
       OCCUPATION,
       RELIGION,
       ADMISSION_DATE,
       TC_SUBMITED,
       LAST_SCHOOL
  from STUDENT
where admission_date = to_date(:p22_adm_date)

And then define the page item :p22_adm_date in the Page Items to Submit field.

Setting Headings and Other Properties

To specify headings for the report columns, click on the Columns node to expand the columns, and then click on each column to specify its heading and even more other properties on the right-side. Below is the image for your reference:

Setting Report Attributes

There are plenty of attributes for an interactive report in Oracle Apex. Such as, you can specify a link column to open another page, showing an alternate value for the Null values, pagination type, interactive report toolbar, etc. The following is the image illustrate some commonly use attribute settings:

Changing Column Orders of the Interactive Report

Initially, when you create the interactive report, the column order would be the same as per the Table or the SQL query. But if there is a requirement to set a particular order for the columns and if you will try to set it by dragging up and down in the Oracle Apex page designer, then it won't help.

So if you want to change the column order, then you have to run the report and then follow the instructions below.

Click on the Actions menu, then select the option Columns. The following dialog will open, as shown in the below image:

Here you can click on the column, then click on the Up and Down arrow buttons to move columns up and down. Then click on the Apply button.

But this change is currently temporary. To make this change permanent and to available to all the application users, you have to save it as the default primary report. Follow these instructions:

Click on the Actions menu, then select the option Report > Save Report. The following dialog will appear:

Here change the Save type to the Default report and then the options will change to Primary and Alternative report. Select the Primary option and click on the Apply button. As shown in the below image:

Now the report has been saved with your changed column order and will be visible to all users. Similarly, you can make any changes in the report at runtime and save it as above to make the changes available to all the users.

Update Columns of the Interactive Report

Suppose, the Table on which the interactive report is created, is modified and some columns have been changed, for example, data type changed or columns renamed or new columns have been added.

And you want to update the interactive report so that the changes can take effect. To do this, simply do the right-click on the report region and select the option Synchronize Columns as shown in the below image: