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 :)

3 comments:

Ingo Wevers - IWS RESULT said...

Hi Dimitri,

Nice, it will work on the report view of an interactieve report, but not on a pivot view.
Do you have any idea how this will work in a pivot view ?

Ingo

Unknown said...

We have some CSS defined inline in the Page:

.negative-number {
color:red;
}

I tried to put this inline but it didn't work, it was giving no red color. rest of things on post worked

MQ said...

Hi, this worked great for plain text fields, but I'm trying to highlight a link column. There is no HTML expression available when you use 'Link' as your column type. How do you do the last step in this case?