Highlighting Only Changed Values in APEX Report

I hope everyone is doing great in the new year !!

Lets start the first post of the year 2022.

Recently I was working on a requirement where I had to highlight only changed values of a report. We have an audit table which gets a new entry every time any record in the main table is updated.

In all these records, we need to highlight only those values which are changed.

Primarily, there are two major points:

1. Comparing the new value with old value using some LAG/LEAD analytical functions of SQL.

2. Highlight new value if its not matching with old value using CSS attributes.

Lets, first make changes in the SQL query to add LAG/LEAD function.

LAG : LAG() is an analytic function which allows to access the row at a given offset prior to the current row without using a self-join.

The following illustrates the syntax of the LAG() function:

LAG(expression [, offset ] [, default ])
OVER (
	[ query_partition_clause ] 
	order_by_clause
)

LEAD : LEAD() is an analytic function which allows to access the following row from the current row without using a self-join.

The following illustrates the syntax of the LEAD() function:

LEAD(expression [, offset ] [, default ])
OVER (
	[ query_partition_clause ] 
	order_by_clause
)

Step 1:

SQL Query

Here, I am comparing a value with corresponding next row value. If they are not equal then highlight that value with RED color

SELECT COL1,
            CASE WHEN COL1 <> LEAD (COL1,1) OVER (ORDER BY DATE_COL DESC)
                 THEN '#dc3545'
            ELSE NULL
        END COL1_CSS_STYLE
FROM TAB1

Note: This query will create an extra column COL1_CSS_STYLE in the report which we need to hide.

Step 2:

Go to COL1 properties (Colum Formatting --> HTML Expression)

Put the following statement :

<span style="color:#COL1_CSS_STYLE#">#COL1#</span> 

This will make the updated values highlighted in a different color in your report.

I mainly use this formatting in history tables, audit tables etc.

Thanks for reading my post !!