A customer from Tennessee recently asked for help in creating a simple dashboard in their
Oracle APEX application. In the PHP system they were coming from, they had a dashboard that looked like the following:
Most people think of dashboards as a nice cockpit panel containing charts and graphics. While this example doesn't perfectly fit that description, it can be classified as a report that is summarized, and any elements which need attention are presented in a different color.
I've implemented similar solutions in the past, selecting an image reference in the SELECT clause of my report query, and then referencing this image reference as the column value. But this time, I first solicited the opinion of
Shakeeb Rahman, the Design Lead for
Oracle APEX, and he provided me a better solution. Using a simple combination of SQL and
Font APEX, this can be easily and elegantly solved!
For this example, I created a new table CITY_STATUSES
create table city_statuses (
city_name varchar2(100) primary key,
status1 number,
status2 number,
status3 number);
I populated it with data, and then I created a new application with an Interactive Report on the table. The query of the Interactive Report was simply:
select city_name,
status1,
status2,
status3
from city_statuses
and my initial report looked like:
In my example, 1 is a good condition, 0 is a warning, and -1 indicates that an action must be taken.
Universal Theme
Universal Theme is a responsive, versatile, and customizable user interface for your Application Express apps. The Universal Theme in Oracle APEX 5.1 includes Font APEX, a drop-in replacement for Font Awesome, but with better graphics and more of them (courtesy of master graphic artist
Bob Daly). You can learn more about the Universal Theme at
https://apex.oracle.com/ut, and you can learn more about Font APEX at
https://apex.oracle.com/fontapex.
Shakeeb recommended I use Font APEX and the Universal Theme helper classes to solve this problem. The helper classes can be used to set the colors on any custom component. You can find these Universal Theme helper classes at
https://apex.oracle.com/ut -> Reference -> Color and Status Modifiers. What's nice about these colors is that they are coordinated with the Theme Roller in APEX. If you change the global success color in Theme Roller, the icon color will also be updated.
To solve this specific problem for the dashboard, I selected two additional columns in the SELECT clause for each STATUS column:
- status_icon - String representing the icon class and modifier class
- status_description - Description of the status icon, for accessibility purposes. This is very important, because we are changing from a discrete value in the report to an icon and a color. Without the description column, this information will be inaccessible.
For the icons and modifiers, I used:
- Success: fa-check-circle-o u-success-text
- Warning: fa-exclamation-triangle u-warning-text
- Error: fa-exception u-danger-text
The Solution
For each status column in my SELECT clause, I added a corresponding icon and description column:
select city_name,
status1,
status2,
status3,
case status1
when 1 then 'fa-check-circle-o u-success-text'
when 0 then 'fa-exclamation-triangle u-warning-text'
when -1 then 'fa-exception u-danger-text'
end status1_icon,
case status1
when 1 then 'OK'
when 0 then 'Warning'
when -1 then 'Danger'
end status1_description,
case status2
when 1 then 'fa-check-circle-o u-success-text'
when 0 then 'fa-exclamation-triangle u-warning-text'
when -1 then 'fa-exception u-danger-text'
end status2_icon,
case status2
when 1 then 'OK'
when 0 then 'Warning'
when -1 then 'Danger'
end status2_description,
case status3
when 1 then 'fa-check-circle-o u-success-text'
when 0 then 'fa-exclamation-triangle u-warning-text'
when -1 then 'fa-exception u-danger-text'
end status3_icon,
case status3
when 1 then 'OK'
when 0 then 'Warning'
when -1 then 'Danger'
end status3_description
from city_statuses
After saving the updated query for the Interactive Report, I edited these columns in Page Designer and changed the property
Type from
Plain Text to
Hidden Column.
Then, for the columns STATUS1, STATUS2 and STATUS3, in Page Designer I changed the property
HTML Expression to:
<span class="fa #STATUS1_ICON#" title="#STATUS1_DESC#"></span>
Obviously, replace STATUS1 with the correct corresponding column name. I adjusted the heading and column alignment of each column to center, and voila! It couldn't be easier.
If for some reason you want to make the icons even larger, no problem! Simply add the fa-2x modifier in the HTML expression (after #STATUS1_ICON#).
Experiment with the modifiers of Font APEX at
https://apex.oracle.com/fontapex. Choose your icon, vary the size, animation, modifier, and status. Just don't go crazy - we don't want to see the world's APEX apps introduce the equivalent of the <marquee> tag again.
Shakeeb presented the Universal Theme, these modifiers, and much more in a
recorded Webinar from
ODTUG.
P.S. While you might be tempted to simplify the query and use an
inline PL/SQL function in the WITH clause of the query, you most likely will encounter error "ORA-32034: unsupported use of WITH clause". This is because the Interactive Report will enclose your original query in a subquery, and in general, inline PL/SQL functions in subqueries are intentionally prohibited by the Oracle Database. However, the solution above also works with a Classic Report, and in that case, you could use an inline PL/SQL function in the WITH clause of the query.
