Oracle Apex - Better way to set background color stored in database (JSON) into classic report cells?

user_19916684 0
Viewed 46

I'm working on Oracle Apex 22.1.3. I have a database with datas like bellow :

Name Month Year Day_1 Day_2
DUPONT Pierre 09 2022 {"VALUE": 7,70, "COLOR":"#dae727"} ...
DURAND Jean 09 2022 {"VALUE": 8,70, "COLOR":"#dae122"} ...

I have created an application, with a classic report and several select list that allow the users to select Name, Month and Year.
And then the classic report was updated with the value for each day of the month, like table below :
(when Month selected = September and Year selected = 2022)

Name Thu
01		 Fri
02
DUPONT Pierre 7,70 ...
DURANT Jean 8,70 ...

I would like to know if there is a way to set Background-color, stored in database, in cells containing values using Javascript ?

Is there a way to store alls datas in Javascript variable ?

For example:
const datas = apex.getAllDatas("TableName")

Thanks

oracle-apex plsql sql javascript
1 Answers

No idea how to store alls datas in a Javascript variable (not even sure what that means) but I think this is an XY problem and this is how I'd solve it. Why would you use javascript if it is not needed ?

First, parse the data and extract the columns value and color, then create a report on that and use apex native feature to apply the color styling to a report cell.

the query

CREATE my_table (name, month_name, year_name, value_color_json) as
(
  SELECT 'DUPONT Pierre',   '09'    ,'2022' ,'{"VALUE": "7,70", "COLOR":"#ff0000"}' FROM DUAL  UNION ALL
  SELECT 'DURAND Jean',     '09'    ,'2021' ,'{"VALUE": "8,70", "COLOR":"#3333ff"}' FROM DUAL 
);

select name, month_name, year_name, 
       JSON_VALUE(dbms_lob.substr(value_color_json,4000,1),'$."VALUE"' returning VARCHAR2) as value,
       JSON_VALUE(dbms_lob.substr(value_color_json,4000,1),'$."COLOR"' returning VARCHAR2) as color    
  from my_table;

This gives:

enter image description here

Notes:

  • The json in your example wasn't valid json, I fixed it for the example
  • Made colors a bit more distinct for the sake of the example
  • dbms_lob.substr is needed because json is clob

the report

  • Create a classic report with the above query as source.
  • In the column "value", set the column formatting to the the following:

enter image description here

  • change the type of the column "color" to "Hidden Column"
  • Save and run. There is your report:

enter image description here

user_41898140
Related