Press "Enter" to skip to content

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.

Oracle Apex: Creating a Blank Page.

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:

Creating Interactive Report region in Oracle Apex.

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:

Oracle Apex interactive report source as SQL Query.

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:

Oracle Apex - setting headings for the columns.

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:

Oracle Apex - set interactive report attributes.

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:

Change column order of Oracle Apex interactive report.

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:

Save interactive report.

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:

Save interactive report as Primary report.

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:

Oracle Apex - synchronize columns.

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:

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

guest
9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
FJcr
FJcr
4 months ago

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
FJcr
4 months ago

Image for my question attached.

FJcr
FJcr
Reply to  Vinish Kapoor
4 months ago

Thank you Vinish.
By inspecting the aggregate column I see its html is:

<td class="a-IRR-aggregate" headers="C38330158850415029876 B38008400903242970130_1"><span y="" class="a-IRR-aggregate-type u-VisuallyHidden">Sum : </span><span class="a-IRR-aggregate-value">$2,300.00</span></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"><span y="" class="a-IRR-aggregate-type u-VisuallyHidden">Sum : </span><span class="a-IRR-aggregate-value">$2,300.00</span></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.

FJcr
FJcr
Reply to  Vinish Kapoor
3 months ago

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!

Gustavo Ariel Vallejos
Gustavo Ariel Vallejos
2 months ago

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