Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Set BACKGROUND color from data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Set BACKGROUND color from data
 Login/Join
 
Silver Member
posted
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,


WebFOCUS 8.1.05M, 8.2.02M
Windows, All Outputs
 
Posts: 34 | Location: Southern New Jersey | Registered: January 24, 2017Report This Post
Silver Member
posted Hide Post
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,$


WebFOCUS 8.1.05M, 8.2.02M
Windows, All Outputs
 
Posts: 34 | Location: Southern New Jersey | Registered: January 24, 2017Report This Post
Gold member
posted Hide Post
This should fill the entire cell red.
TYPE=DATA, COLUMN=SEATS, COLOR=WHITE, BACKCOLOR=RED, $


WebFOCUS 8

Windows, All Outputs
 
Posts: 55 | Location: Phoenix | Registered: March 01, 2018Report This Post
Virtuoso
posted Hide Post
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).


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
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



WebFOCUS 8.1.05M, 8.2.02M
Windows, All Outputs
 
Posts: 34 | Location: Southern New Jersey | Registered: January 24, 2017Report This Post
Master
posted Hide Post
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  


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Silver Member
posted Hide Post
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?


WebFOCUS 8.1.05M, 8.2.02M
Windows, All Outputs
 
Posts: 34 | Location: Southern New Jersey | Registered: January 24, 2017Report This Post
Master
posted Hide Post
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 Roll Eyes )
  
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Silver Member
posted Hide Post
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


WebFOCUS 8.1.05M, 8.2.02M
Windows, All Outputs
 
Posts: 34 | Location: Southern New Jersey | Registered: January 24, 2017Report This Post
Master
posted Hide Post
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,


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Silver Member
posted Hide Post
Works great, thanks again


WebFOCUS 8.1.05M, 8.2.02M
Windows, All Outputs
 
Posts: 34 | Location: Southern New Jersey | Registered: January 24, 2017Report This Post
Guru
posted Hide Post
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


-********************
Sandbox: 8206.10
Dev: 8201M
Prod:8009
-********************
 
Posts: 289 | Location: Houston,TX | Registered: June 11, 2004Report This Post
Master
posted Hide Post
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


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Guru
posted Hide Post
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?


-********************
Sandbox: 8206.10
Dev: 8201M
Prod:8009
-********************
 
Posts: 289 | Location: Houston,TX | Registered: June 11, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Set BACKGROUND color from data

Copyright © 1996-2020 Information Builders