How to Highlight Specific Column Values based on a CSS Class and Binary Digit On/Off Positions

Question

Using Oracle APEX v5.1.2.

Unsure how to tackle the following but I have a table called, flag_defs with the following example data:

ID      NAME
------- ------
1       A
2       B
3       C
4       D
5       E
6       F

Based on the above table, I need to display all these names with a report region but not on separate lines but in the following fashion:

A B C D
E F

where I'm only showing 4 names across.

Now based on the following binary convention, say the following:

010010

where the first 0 lines up to ID = 1 and the last 0 in this sequence lines up to ID = 6

Based on this binary sequence, which will be stored within a database field in another table, I need to apply a class called "flag-red" that I will define as color:red;font-weight:bold; to the names that have the ID position set to "1".

So in the above example binary sequence, both "B" and "E" only would receive the class of "flag-red" and would be red/bold within the report region. The others would not.

The same goes with removing the class if the digit "1" is reset back to "0" for that ID.

I would need to iterate through each digit in this field to set the correct class in my report.

I'm assuming I would firstly create a report and assign a span class to each name but unsure if this is the correct approach.

Furthermore, would JavaScript be a better option or stick to SQL

Would appreciate some assistance on how to tackle the above.

Answer 1

I would suggest you to make a report in the following way:

Lets say you have a query which returns some rows with rownum column. Adding a pivot to this query we can turn it into the following:

select *
  from (select r, mod(rownum, 6) group_no, floor((rownum - 1)/6) row_id
          from (select rownum r
          from dual connect by level <= 30) t)
 pivot(max(r) for group_no in (1, 2, 3, 4, 5, 0))
 order by 1

Now we can join this query with the table which stores binary mask to show. Lets say initial query has columns C1, C2, ... C6with data, and joined table with mask data has columns M1, M2, ... M6. (If it is one column with a mask, we can produce 6 columns using calculating expressions) You can calculate a name of a CSS class in these columns, something like

select ...
       case when <expression1> then 'flag-red' else 'flad-green' end M1,
       ...

Next step is to go to report properties, choose column C1 in the column list, go to Column formatting section, put #M1# in the CSS Classes field:

Also, mark M1, M2, ... M6 columns as hidden.