Lucas Rayner
- Sep 14, 2018
- 3 min read

Viewing icons in the Interactive Report [APEX 18.1]

Updated: Dec 3, 2018

Want to display icons instead of data in interactive reports? In this post we will see how easy, practical and beautiful to accomplish this task in Oracle Application Express (APEX). Let's go!

Description

As an example, we will use an environment where we will have two tables: Tasks and Task Status. Each task must have a status, and it will be in the status column that we will display, instead of the status name, icons.

Our testing environment is represented by the diagram below.

Execution

1. We'll start with the creation of the tables.

create table status_task
(
 status_id number generated as identity nocache,
 status    varchar2(30) not null unique,
 code      number(2) not null unique,
 
 constraint pk_status_task primary key (status_id)
);

create table task
(
 task_id     number generated as identity nocache,
 title       varchar2(45) not null,
 description varchar2(145),
 progress    number(3) not null,
 status_id   number,
 
 constraint pk_task primary key (task_id),
 constraint fk_status_task foreign key (status_id) references status_task 
(status_id)
);

2. Soon after creating the tables, we isnert the status that the task in our example can assume (Open, Running, Pending, Canceled, Closed).

insert into status_task (status, code) values ('Open', 10);
insert into status_task (status, code) values ('Running', 20);
insert into status_task (status, code) values ('Pending', 30);
insert into status_task (status, code) values ('Canceled', 40);
insert into status_task (status, code) values ('Closed', 10);

Note that we assume that each status will have a code. In my opinion, this is very useful when working with dynamic IDs. We will see its usefulness later.

3. After creating the tables, we created a form page of type "Report with Form on Table" to register the tasks and display their data.

4. Created our form and report page, we created the responsible query by defining which icons will represent the status of the task. We can create the query in the"Source" region (Type: SQL Query) from our interactive report or through a view. In our example we will create a view and refer to it in the "Source" region of the interactive report.

create or replace view vw_tasks
 as
 
 select a.task_id,
        a.title,
        a.description,
        a.progress,
        b.status,

        case b.code when 10 then 'fa-file'
                    when 20 then 'fa-play'
                    when 30 then 'fa-pause'
                    when 40 then 'fa-times'
                    when 50 then 'fa-check'
        end as icon_class,
        
        case b.code when 10 then '#ff9900'
                    when 20 then '#0099ff'
                    when 30 then '#3f51b5'
                    when 40 then '#ff3300'
                    when 50 then '#00cc66'
        end as color_class
        
 from task to join status_task b on (a.status_id = b.status_id)

See that in the view we use two case expressions. One to set the icon (icon_class) and the other to set its color (color_class) based on the status code.

Note: To learn more about Case Expressions, go to the Oracle Help Center.

5. Now, let's go to the "Source" region of our interactive report on the edit page, change the type to "Table / View" and in the "Table Name" label select the view we just created.

6. After making these changes, we still need to edit the column that will display the status of each task. Since we have three columns that deal with status, one for the status name, other to set the icon and another to set the color, we need to hide two. We will hide the "icon_class" and "color_class" column. Let's go to the columns properties, in the "Identification" region, select the "Hidden Column" type.

7. Now we need to edit the column that will display the status of the task, the "Status" column. So that it does not display the type of status but the corresponding icon defined in the case expression, we go to the "Column Formatting" region, on the "HTML Expression" label and include the following code:

<span class="fa #ICON_CLASS#" style="color: #COLOR_CLASS#;">
    <spam class="visuallyhidden">#STATUS#</spam>
</span>

Note: The class "visuallyhidden" is set to the status column in order to not display the descriptive text next to the icons, and is visible only in the filters in the interactive report.

As a result we will have the following report: