Color Scale column based on max and min values

(3 posts) (2 voices)
  1. cayasanchez, Member

    Hi MyDBR Team:

    I want to color a column based on the maximum and minimum values, where green is the min value and it changes the color until getting red for the max value.
    I tried to use the demo available here

    But it is not working for me, it shows error message Unknown column 'calculated_percent' in 'field list'.
    The column calculated_percent is showing the right value, the problem is for cellstyle.

    Please take a look at my code, I'm using the same functions for coloring the background as the demo, the only difference is how I'm sending the fourth parameter (in_ratio), if possible let me know what I'm doing wrong.

    select 'dbr.max', 'Google_CPC'; select 'dbr.min', 'Google_CPC'; select 'dbr.calc', 'calculated_percent', '([Google_CPC]-[Google_CPC.min])/([Google_CPC.max]-[Google_CPC.min])'; select 'dbr.calc', 'cellstyle', CONCAT('background-color:#',f_color_scale3( '00FF00', 'FFFF00', 'FF0000', calculated_percent ));

    select 'dbr.cellstyle', 'Google_CPC', 'cellstyle';

    SELECT date_sales as 'Date', dayName(date_sales) as DOW, CPC as 'Google_CPC', null as calculated_percent, null as 'cellstyle' FROM table order by 1 desc;

    CREATE FUNCTION `f_color_scale3`( in_from char(6), in_middle char(6), in_to char(6), in_ratio float ) RETURNS char(6) CHARSET utf8 begin

    -- we'll use f_color_scale which picks the color between two colors, third parameter is a value between 0 and 1 if (in_ratio <= 0.5 ) then return f_color_scale( in_from, in_middle, in_ratio*2 ); else return f_color_scale( in_middle, in_to, (in_ratio-0.5)*2 ); end if;


    CREATE FUNCTION `f_color_scale`( in_from char(6), in_to char(6), in_ratio float ) RETURNS char(6) CHARSET utf8 begin

    declare v_red int; declare v_green int; declare v_blue int;

    set v_red = conv(substring(in_from,1,2), 16, 10); set v_green = conv(substring(in_from,3,2), 16, 10); set v_blue = conv(substring(in_from,5,2), 16, 10);

    set v_red = v_red + (conv(substring(in_to,1,2), 16, 10) - v_red) * in_ratio; set v_green = v_green + (conv(substring(in_to,3,2), 16, 10) - v_green) * in_ratio; set v_blue = v_blue + (conv(substring(in_to,5,2), 16, 10) - v_blue) * in_ratio;

    return concat( right(concat('0',conv(v_red, 10, 16)),2), right(concat('0',conv(v_green, 10, 16)),2), right(concat('0',conv(v_blue, 10, 16)),2) );


  2. myDBR Team, Key Master

    You can use MySQL functions in query, not in dbr.calc which uses PHP calculations. You can do something like this:

    CREATE PROCEDURE sp_DBR_report()
    begin declare v_min int;
    declare v_max int; select min(Google_CPC), max(Google_CPC) into v_min, v_max
    from table; select 'dbr.max', 'Google_CPC';
    select 'dbr.min', 'Google_CPC'; select 'dbr.cellstyle', 'Google_CPC', 'cellstyle'; select
    CONCAT('background-color:#',f_color_scale3( '00FF00', 'FFFF00', 'FF0000', calculated_percent )) as cellstyle
    from (
    select date_sales, dayName(date_sales) as DOW, Google_CPC, (Google_CPC - v_min) / (v_max-v_min) as calculated_percent
    from table
    ) as q
    order by date_sales desc; end

  3. cayasanchez, Member

    Thank you very much!! It is working now.


You must log in to post.