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;

   end

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

   end
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
   
     date_sales,
   
     DOW,
   
     calculated_percent,
   
     Google_CPC,
   
     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.

Reply

You must log in to post.