Wednesday, January 20, 2016

Highlight numbers in an APEX Report (SQL and Class)

Last year I blogged about highlighting negative numbers in an APEX Report, the CSS only way.
At that time I gave two alternative approaches; by using JQuery or SQL, but it looks like I didn't do those posts yet, till somebody reminded me. This post is about using SQL to highlight something in a report.

Let's say we want to highlight negative numbers in a report (as in the previous post):


We have some CSS defined inline in the Page:

.negative-number {
  color:red;
}

The negative-number class we will add to some values. All the logic to know if it's a negative number will be in SQL. Why SQL you might ask? This example is very simple, but you could call a function which has a lot of complexity to decide if you want to assign a class to a record or not, the principe of this example is more important, that you can use logic in SQL to work with CSS.

The SQL Query of the Report looks like this. Watch for the case statement where we say when to assign a value for the class:

select 
 description,
 amount,
 case 
   when amount < 0
   then 'negative-number'
   else ''
 end as class
from dimi_transaction
order by id

Finally we assign the class to the amount, by adding a span in the HTML Expression of the Amount column:


The Class column you can make Conditional = Never as it's something we just use behind the scenes.

That's how you make a bridge between SQL and CSS.

You can now play more with the case statement and even let the class or style e.g. color, come from a user defined table... unlimited possibilities :)