I'm trying to set the background color (gradient) of a cell based on a column in a table. SETCOLOR will be a column in each row which will determine what to set the background color to be. In the CAR example below, I have it working except that it is setting the background of just the text in the cell. I need it to fill in the background of the cell.

SETCOLOR can be any color in the color spectrum, so using WHEN=N1 EQ somevalue, will not work for me.
I tried doing something like TYPE=DATA,BACKCOLOR=SETCOLOR,$ but couldn't get it to work.

  
DEFINE FILE CAR
    SETCOLOR/A20='RGB(255 0 0)';
END

TABLE FILE CAR
  SUM
	SEATS
    COMPUTE C_SEATS/A100='<span style="BACKGROUND-COLOR:' | SETCOLOR | ';  COLOR:RGB(255 255 255);">' | EDIT(SEATS,'999999') | '</span> ';
    DCOST
BY	COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP-COLOR=RGB(51 51 51),
BORDER-BOTTOM-COLOR=RGB(51 51 51),
BORDER-LEFT-COLOR=RGB(51 51 51),
BORDER-RIGHT-COLOR=RGB(51 51 51),
$
END
-RUN

Thanks for any help
David

This message has been edited. Last edited by: David M,

David,

With or without the commas in the RGB values I get a red background over the data only, but I need the red to fill in the entire cell within the borders, similar to setting TYPE=DATA,COLUMN=N3,BACKCOLOR=RED,$

This should fill the entire cell red.
TYPE=DATA, COLUMN=SEATS, COLOR=WHITE, BACKCOLOR=RED, $

And what about this David

TABLE FILE CAR
SUM SEATS
    DCOST
    COMPUTE IS_ORANGE /A1 = IF SEATS GT 2 AND DEALER_COST GT 15000 THEN 'Y' ELSE 'N'; NOPRINT
BY COUNTRY
BY CAR
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

     DEFMACRO=SET_GREEN,
     MACTYPE=RULE,
     WHEN=SEATS EQ 2,
$
     DEFMACRO=SET_RED,
     MACTYPE=RULE,
     WHEN=SEATS GT 2,
$
     DEFMACRO=SET_ORANGE,
     MACTYPE=RULE,
     WHEN=IS_ORANGE EQ 'Y',
$
TYPE=DATA,
   BORDER=LIGHT,
   BORDER-COLOR=RGB(51 51 51),
$
TYPE=DATA,
   COLUMN=SEATS,
   BACKCOLOR=GREEN,
   MACRO=SET_GREEN,
$
TYPE=DATA,
   COLUMN=SEATS,
   BACKCOLOR=RGB(242 148 47),
   MACRO=SET_ORANGE,
$
TYPE=DATA,
   COLUMN=SEATS,
   BACKCOLOR=RED,
   MACRO=SET_RED,
$
ENDSTYLE
END
-RUN

Styling order is important when you use the MACRO=
If I would have placed the MACOR=SET_ORANGE styling as the last one, the MACRO=SET_RED styling takes precedence.
You need to order them from the most restrictive to the lowest one when you have similar condition (SEATS GT 2 in both conditions).

Thank you Les and Martin for your suggestions, but using macros will not work for me. What I am trying to do is imitate the "Format cells based on their values" functionality in Excel where you can choose a range of numbers and excel will set the shade of the colors based on their values. I have a SQL stored procedure that determines the RGB(0,0,0) value for each row of data and stores it in a column. So for example in the CAR table I would have

CAR=JAGUAR, SEATS=7, SEATS_COLOR=RGB(255,0,0)
CAR=JENSEN, SEATS=4, SEATS_COLOR=RGB(255,100,0)
CAR=TRIUMPH, SEATS=2, SEATS_COLOR=RGB(255,100,200)

SEATS_COLOR can be any shade of RED, YELLOW, or GREEN, which is why macros would not work.

The code would look like this, with SEATS_COLOR coming from the table in the Db. The example I gave in my original post works, but it only sets the background of the text and not the cell.

  
TABLE FILE CAR
SUM
    SEATS
    COMPUTE C_SEATS/A100='<span style="BACKGROUND-COLOR:' | SEATS_COLOR | ';  COLOR:RGB(255 255 255);">' | EDIT(SEATS,'999999') | '</span> ';
    DCOST
BY	COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP-COLOR=RGB(51 51 51),
BORDER-BOTTOM-COLOR=RGB(51 51 51),
BORDER-LEFT-COLOR=RGB(51 51 51),
BORDER-RIGHT-COLOR=RGB(51 51 51),
$
END
-RUN

You need to change your span (inline element) to a div (block element) and add in the style for the height to match the td height (20px in the example below).

The td element has a style with padding on it. So you need to remove the padding from all the td elements in the column. So the -HTMLFORM block removes that padding from every 3rd td in each tr:

DEFINE FILE CAR
    SETCOLOR/A20='RGB(255 0 0)';
END

TABLE FILE CAR
  SUM
	SEATS
    COMPUTE C_SEATS/A200V='<div style="BACKGROUND-COLOR:' | SETCOLOR | ';  COLOR:RGB(255 255 255); height:20px;">' | EDIT(SEATS,'999999') | '</div> ';
    DCOST
BY	COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP-COLOR=RGB(51 51 51),
BORDER-BOTTOM-COLOR=RGB(51 51 51),
BORDER-LEFT-COLOR=RGB(51 51 51),
BORDER-RIGHT-COLOR=RGB(51 51 51),
$
END
-RUN

-HTMLFORM BEGIN NOEVAL
<style>
    td:nth-child(3){
        padding: 0 !important;
    }
</style>
-HTMLFORM END  

Thanks Hallway,

Much better, but it's not entirely filling the cell like TYPE=DATA,COLUMN=SEATS,BACKCOLOR=RED,$ does. Is there a way to do that?

So, if we set the position of the td to be relative, we can set the div to be absolute and set the position of the div within the td. This works for me in Chrome and FF ( it doesn't work for me in either MSFT browser... go figure )

  
DEFINE FILE CAR
    SETCOLOR/A20='RGB(255, 0, 0)';
END

TABLE FILE CAR
SUM SEATS
    COMPUTE C_SEATS/A200V='<div style="BACKGROUND-COLOR:' | SETCOLOR | ';  COLOR:RGB(255, 255, 255);">' | EDIT(SEATS,'999999') | '</div>';
    DCOST
BY  COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP-COLOR=RGB(51 51 51),
BORDER-BOTTOM-COLOR=RGB(51 51 51),
BORDER-LEFT-COLOR=RGB(51 51 51),
BORDER-RIGHT-COLOR=RGB(51 51 51),
$
END
-RUN

-HTMLFORM BEGIN NOEVAL
<style>
    td:nth-child(3){
        padding:0 !important;
        position:relative !important;
    }
    td:nth-child(3)>div{
        height:100% !important; 
        width:100% !important; 
        margin:0 auto !important; 
        position:absolute !important; 
        top:0 !important; 
        left:0 !important;        
    }
</style>
-HTMLFORM END 

Hallway,

Perfect! thanks

Yes, doesn't work in MSFT browsers, but we use Chrome.

What would I use in nth-child(?) to apply to all my columns?

Thanks again

David

quote:

What would I use in nth-child(?) to apply to all my columns?

The nth-child(3) I used above basically just targets the 3rd column, the third <td> child of each parent <tr>. To apply to all cells just remove the :nth-child() pseudo-class so it is just td.

  
-HTMLFORM BEGIN NOEVAL
<style>
    td{
        padding:0 !important;
        position:relative !important;
    }
    td>div{
        height:100% !important; 
        width:100% !important; 
        margin:0 auto !important; 
        position:absolute !important; 
        top:0 !important; 
        left:0 !important;        
    }
</style>
-HTMLFORM END 

This message has been edited. Last edited by: Hallway,

Works great, thanks again

This is a great technique, we have a similiar requirement but we need to use HSL codes from a db and use it as data backcolor. Another problem is its an EXCEL output. Any ideas?

quote:

DEFINE FILE CAR
SETCOLOR/A20='RGB(255, 0, 0)';
END

TABLE FILE CAR
SUM SEATS
COMPUTE C_SEATS/A200V='

' | EDIT(SEATS,'999999') | '

';
DCOST
BY COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP-COLOR=RGB(51 51 51),
BORDER-BOTTOM-COLOR=RGB(51 51 51),
BORDER-LEFT-COLOR=RGB(51 51 51),
BORDER-RIGHT-COLOR=RGB(51 51 51),
$
END
-RUN

-HTMLFORM BEGIN NOEVAL

-HTMLFORM END

I'm not sure how the data looks in your table for the hsl value, but you just need to the define to be hsl. the code below changes the color to orange:

  
DEFINE FILE CAR
    SETCOLOR/A20='hsl(33, 100%, 55%)';
END

TABLE FILE CAR
SUM SEATS
    COMPUTE C_SEATS/A200V='<div style="background-color:' | SETCOLOR | ';  color:rgb(255, 255, 255);">' | EDIT(SEATS,'999999') | '</div>';
    DCOST
BY  COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP-COLOR=RGB(51 51 51),
BORDER-BOTTOM-COLOR=RGB(51 51 51),
BORDER-LEFT-COLOR=RGB(51 51 51),
BORDER-RIGHT-COLOR=RGB(51 51 51),
$
END
-RUN

-HTMLFORM BEGIN NOEVAL
<style>
    td:nth-child(3){
        padding:0 !important;
        position:relative !important;
    }
    td:nth-child(3)>div{
        height:100% !important; 
        width:100% !important; 
        margin:0 auto !important; 
        position:absolute !important; 
        top:0 !important; 
        left:0 !important;        
    }
</style>
-HTMLFORM END

Thank you! I playd with some other css the other day and Somehow I found that using HSL works with Chrome but not in IE. I tried adding < !DOCTYPE html> but that didnt help either. Any thoughts?