Wednesday, July 26, 2017

Easy Dashboard using nothing but APEX, Font APEX and SQL!

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:
  1. status_icon - String representing the icon class and modifier class
  2. 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.

14 comments:

  1. Hi Joel,

    Great article. Your SQL query was just an illustration, but may I suggest a query rewrite with less redundancy:

    with icon_descr(status, icon, descr) as (
    select 1, 'fa-check-circle-o u-success-text', 'OK' from dual
    union all
    select 0, 'fa-exclamation-triangle u-warning-text', 'Warning' from dual
    union all
    select -1, 'fa-exception u-danger-text', 'Danger' from dual
    )
    select c.CITY_NAME, c.STATUS1, c.STATUS2, c.STATUS3,
    id1.ICON icon1, id1.DESCR descr1,
    id2.ICON icon2, id2.DESCR descr2,
    id3.ICON icon3, id2.DESCR descr3
    from city_statuses c
    join icon_descr id1 on c.status1 = id1.status
    join icon_descr id2 on c.status2 = id2.status
    join icon_descr id3 on c.status3 = id3.status;

    If applications want to standardize these icons across pages, then ICON_DESCR could be centralized as a table, or two tables if you wanted to translate the description into different languages.

    Best regards, Stew Ashton

    ReplyDelete
  2. Hi Joel,

    here another suggestion for a simplified query:

    select city_name,
    status1 AS status1_icon,
    status1 AS status1_description,
    status2 AS status2_icon,
    status2 AS status2_description,
    status3 AS status3_icon,
    status3 AS status3_description,
    from city_statuses


    If you define the status and description columns as "Plain Text (based on List of Values)"
    you can use LOVs with static values to get the same result:

    List of Values Name: status_icon
    Display Return
    fa-check-circle-o u-success-text 1
    fa-exclamation-triangle u-warning-text 0
    fa-exception u-danger-text -1

    List of Values Name: status_description
    Display Return
    OK 1
    Warning 0
    Danger -1


    Best regards
    Klaus Schuermann

    ReplyDelete
  3. Very nice Klaus!
    That is exactly the change in solution I was thinking about to reduce de redundancy of code.

    ReplyDelete
  4. Great post. However, my icons won't change colour. for the query I've got:

    case bank_stmts.reconciled
    when 'Y' then 'fa-check-circle-o u-success-text'
    when 'E' then 'fa-exclamation-triangle u-warning-text'
    when null then 'fa-exception u-danger-text'
    end Recon2,

    Then HTML expression:

    It shows the correct icons nicely (and size is double). but its black instead of green, red or amber.

    when I inspect the source for the icon, it shows the correct class. i.e. for success I get a double size circle.

    this is my source

    span class="fa fa-check-circle-o u-success-text fa-2x"> == $0
    ::before
    /span

    I've removed the < and > on the span so that I can post the content.

    So, not sure what to check to get the colour?

    Kind Regards
    Richard

    ReplyDelete
  5. Stew / Klaus,

    Thank you for your suggestions and improvements to the SQL. As Stew stated, I intended this to be an illustration so that it's completely lucid to the non-SQL savvy. However, I do fully appreciate your suggestions for less verbose and more efficient way to write this in SQL.

    Joel

    ReplyDelete
  6. Hi Richard,

    Is this using APEX 5.1 and the Universal Theme in APEX 5.1? Can you possibly please replicate this in a test case on apex.oracle.com?

    Joel

    ReplyDelete
  7. Hi Joel,

    Yes, I've reproduced this on apex.oracle.com

    Workspace: vrs
    Username: test
    password: test2
    Application: Test - 291
    Page: 25

    I must be doing something a little bit wrong, but cannot figure out what.
    By the way, 'fa-exception u-danger-text' doesn't seem to work at all. (blank).

    Many thanks for your help.

    Rgds
    Richard

    p.s. Im away for 10 days, with limited access to tech, so If I don't respond, that's why.

    ReplyDelete
  8. Hi Richard,

    1) I made a copy of your application as 134034
    2) I went into Shared Components -> Themes -> Universal Theme (42), and then clicked the "Refresh Theme" button.

    This now works as you would expect. You needed to refresh to the APEX 5.1 Universal Theme. The stateful utility classes of Universal Theme are first there in UT 1.1 (shipped with APEX 5.1).

    Joel

    P.S. I hope you don't mind - I changed the password of TEST to something else. I feel uncomfortable having this password listed publicly on the Internet.

    ReplyDelete
  9. Hey Joel. Thanks for this. I'll check when I'm back. No problem about pw change etc.

    Thanks once again

    Rgds
    Richard

    ReplyDelete
  10. Hi Joel, got this all working great thanks. Just one thing. I don't have an fa-exception class. its just blank?? I reproduced this on the same example page above.

    regards
    Richard

    ReplyDelete
  11. Hi Richard,

    This seems to work fine in application 134034 in your workspace, which I created as a copy of app 291 and then refreshed Theme 42.

    Joel

    ReplyDelete
  12. Hi,

    Thanks for such informative article.
    Can APEX pages be deployed within oracle ERP?


    Ali

    ReplyDelete
  13. Nice dashboard and simple.
    One thing I immediately noticed is the text in the query, in our projects (multi language) this is not allowed.
    Wouldn't a LOV be a alternative solution?
    So just a define a LOV with:
    value / display
    1 / span> class="fa fa-check-circle-o u-success-text" title="Ok" </span
    etc..

    Two advantages:
    - You only have to define the icon and title once per status
    - It shows up in translations.

    ReplyDelete
  14. HI Peter - I'm not sure you could solve this with an LOV - I don't believe dynamic LOV's are translatable. The strings are used for the description - I would probably use APEX_LANG.MESSAGE for the actual string itself.

    Hi Ali - Yes, you can use APEX to extend ERP in a supported fashion. Many customers do this today. See this white paper: http://www.oracle.com/technetwork/developer-tools/apex/learnmore/apex-ebs-extension-white-paper-345780.pdf

    Joel

    ReplyDelete