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:

I hope you found this tutorial helpful, and now you would be able to create an interactive report in Oracle Apex easily.

There are some more tutorials I have written on the Oracle Apex Interactive Report. Please check the following:

This Post Has 12 Comments

FJcr September 9, 2020
Can Alternative Reports, created in runtime from a primary interactive report, be edited? This is the thing:
Based on a primary IR, I added in runtime:
a compute column ("Budget vs Cost")
a control-break for one of the columns ("Project")
an aggregate sum for the new compute column
That aggregate sum is by default aligned to the right, which doesn't look good.
I save the Alternative Report in runtime and I'm able to find it everything I run the app, BUT, I don't find in the Page Designer to make the change to the Aggregate alignment. The Page for the primary report shows, below its Attributes, the Saved Report with the name I gave it in runtime, but there's no way to see and modify the columns of the saved report.
Help appreciated
-FJ
FJcr September 9, 2020
Image for my question attached.
1. Vinish Kapoor September 10, 2020
 You will have to use the custom CSS to align a particular field. To do this:
 At runtime in Chrome browser, do the right-click on the aggregate sum field and select inspect element from the shortcut menu.
 There you will find the existing CSS for that element, try to modify as per your need and then copy that code and paste it to the page inline CSS section.
 This can solve your issue.
2. FJcr September 10, 2020
 Thank you Vinish.
 By inspecting the aggregate column I see its html is:
 <td class="a-IRR-aggregate" headers="C38330158850415029876 B38008400903242970130_1">Sum : $2,300.00</td>
 By adding "u-tR" to the a-IRR-aggregate classI get the wanted alignment:
 <td class="a-IRR-aggregate u-tR" headers="C38330158850415029876 B38008400903242970130_1">Sum : $2,300.00</td>
 Now, how or where do I change that in APEX Page Designer in order to affect only that aggregate and not every other one using that class?
 Thank you in advance.
3. Vinish Kapoor September 11, 2020
 You don't need to change in HTML code.
 In the inspect element window, you will see the CSS panel, change the CSS code there and when you will get the desired result, copy that CSS and paste it to your page inline CSS section.
 You will find this on your page. Open the page in Apex, click on the page name. Then right side scroll down to the CSS section. You will find there the Inline CSS text box.
4. FJcr September 23, 2020
 Hi Vanish, thank you for your guidance.
 I got it done by including this in the Page inline CSS:
 .a-IRR-aggregate {
 text-align:right;
 }
 Now the thing is that every Aggregate (e.g. Sum as in this case) for a Computed column that I add to the report in runtime will have that class "a-IRR-aggregate", therefore it will be affected by that text alignment.
 What if I don't want a particular aggregate to be affected by that inline css?
 How can I do that?
 Thank you!
5. Vinish Kapoor September 24, 2020
 Ok, try to specify the static id to your report and to the particular column you are summing up.
 After that run the report and inspect the element again, then you can get a different unique CSS class to target.
Gustavo Ariel Vallejos October 23, 2020
hi Vinish,
I have IR where I had to add new columns that ONLY need to be displayed when I export the report to a file ( NVL(:REQUEST,'EMPTY') IN ('CSV','XLS','PDF','XML','RTF','HTMLD') ) , that works fine but I need to change the order of the new column - it always go at the end of the columns . thanks
1. Vinish Kapoor October 23, 2020
  Hi Gustavo,
  You run the report and then choose the Columns option from the Actions menu. There you can reorder the columns.
  Then click again on the Action menu and click on the Reports > Save As and then save it as the Primary report.
  I have described the same steps in the above tutorial, check from the heading Changing Column order of Interactive Report.
Naveen January 22, 2021
Could you please share the insert query (create attendance?)
Naveen January 22, 2021
DECLARE
l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
   l_selected := APEX_UTIL.STRING_TO_TABLE(RTRIM(LTRIM(:P7_IDS, ':'),':'));
  FOR i IN 1..l_selected.count
  LOOP
    INSERT INTO PATIENTS_STUDYS (STUDY_ACRONYM, PID)
        VALUES (:P7_STUDYSELECT, l_selected(i));
  END LOOP;
END;
Ahmed Haroon April 21, 2021
hi, working on a client requirement and need help, they want to create a Department wise IR which should contains multi level sums on item and amount columns like (emp): in each Department JOB wise Total, after end of each department should show Department's Total and in end of report it will show Grand Total. I use Control Beak on Department and then Job but it is showing only Job totals. may be I am doing wrong here OR any other feature it offers for the same? be blessed 🙂 regards