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] Apply color coding in across column values

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Apply color coding in across column values
 Login/Join
 
Member
posted
I need to apply color coding for the following condition,
when the country in 'ENGLAND, FRANCE' , I need to apply color only on seats column as,
more than 100- green
less than 90- red
else yellow

TABLE FILE CAR
SUM
SEATS
RETAIL_COST
BY MODEL
ACROSS COUNTRY

Pls help

This message has been edited. Last edited by: Kerry,
 
Posts: 11 | Registered: October 19, 2006Report This Post
<JG>
posted
Lookup ACROSSCOLUMN attribute and WHEN attribute in the online help.

Tip. the WHEN needs to be based on a DEFINE'd NOPRINT column because IN is not a valid operator for WHEN.
 
Report This Post
Member
posted Hide Post
hi,

I already tried with a define column, like setting a flag when it is england and seats GE 100 then 1 else seats LE 90 then 2 else 3

TYPE=DATA, ACROSSCOLUMN=SEATS,BACKCOLOR=GREEN, WHEN=FLAG EQ 1, $

This Doesnt work,Without the when condition, all the SEATS column across all the value gets colored. when applied a when clause, nothing really happens.

TYPE=DATA, COLUMN=N3 ,BACKCOLOR=GREEN, WHEN=N3 GE 100, $

Also doesnt work in my case, cos of ACROSS.

Pls let me know if you have any suggestions.
 
Posts: 11 | Registered: October 19, 2006Report This Post
Expert
posted Hide Post
The first problem you have is that SEATS will never be GE 100, SEATS values only include 2, 4 and 5.

The second problem is that there is a bug with styling ACROSSCOLUMN: you cannot specify the column name - you have to use positional notation, i.e. P1.

Here is a working example:

TABLE FILE CAR
SUM
COMPUTE FLAG1/A1 = 
IF NOT COUNTRY IN ('ENGLAND', 'FRANCE') THEN '0' ELSE
IF SEATS GT 4 THEN '1' ELSE
IF SEATS LT 3 THEN '2' ELSE '3'; NOPRINT
SEATS
RETAIL_COST
BY BODYTYPE
ACROSS COUNTRY
ON TABLE SET STYLE *
TYPE=DATA, ACROSSCOLUMN=P1, COLOR=GREEN, WHEN= FLAG1 EQ '1', $
TYPE=DATA, ACROSSCOLUMN=P1, COLOR=RED, WHEN= FLAG1 EQ '2', $
TYPE=DATA, ACROSSCOLUMN=P1, COLOR=BLUE, WHEN= FLAG1 EQ '3', $
ENDSTYLE
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
Beg to differ on the second point works fine in 7.6.6 and 7.6.10

DEFINE FILE CAR
COLORIT/A1= IF COUNTRY IN ('ENGLAND','FRANCE') THEN 'Y' ELSE 'N';
END
TABLE FILE CAR
SUM
SEATS
RETAIL_COST
COLORIT NOPRINT
BY BODYTYPE
ACROSS COUNTRY
ON TABLE SET STYLE *
TYPE=DATA, ACROSSCOLUMN=SEATS, BACKCOLOR=GREEN, WHEN= COLORIT EQ 'Y', $
ENDSTYLE
END
 
Report This Post
Expert
posted Hide Post
TABLE FILE CAR
SUM
COMPUTE FLAG1/A1 = 
IF NOT COUNTRY IN ('ENGLAND', 'FRANCE') THEN '0' ELSE
IF SEATS GT 4 THEN '1' ELSE
IF SEATS LT 3 THEN '2' ELSE '3'; NOPRINT
SEATS
RETAIL_COST
BY BODYTYPE
ACROSS COUNTRY
ON TABLE SET STYLE *
TYPE=DATA, ACROSSCOLUMN=SEATS, COLOR=GREEN, WHEN= FLAG1 EQ '1', $
TYPE=DATA, ACROSSCOLUMN=SEATS, COLOR=RED, WHEN= FLAG1 EQ '2', $
TYPE=DATA, ACROSSCOLUMN=SEATS, COLOR=BLUE, WHEN= FLAG1 EQ '3', $
ENDSTYLE
END


This does not work in WebFOCUS v7.6.8, Windows XP and AIX.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
Francis, the problem is with COMPUTE statement. I moved it down, and it works fine in 7.1.6 and 7.6.9


-*Method - 1
TABLE FILE CAR
SUM
SEATS
RETAIL_COST
COMPUTE FLAG1/A1 = IF NOT COUNTRY IN ('ENGLAND', 'FRANCE') THEN '0' ELSE IF SEATS GT 4 THEN '1' ELSE IF SEATS LT 3 THEN '2' ELSE '3';
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD
END

TABLE FILE HOLD
SUM
FLAG1 NOPRINT
SEATS
RETAIL_COST
BY BODYTYPE
ACROSS COUNTRY
ON TABLE SET STYLE *
TYPE=DATA, ACROSSCOLUMN=SEATS, COLOR=GREEN, WHEN= FLAG1 EQ '1', $
TYPE=DATA, ACROSSCOLUMN=SEATS, COLOR=RED, WHEN= FLAG1 EQ '2', $
TYPE=DATA, ACROSSCOLUMN=SEATS, COLOR=BLUE, WHEN= FLAG1 EQ '3', $
ENDSTYLE
END


-*Method - 2
TABLE FILE CAR
SUM
SEATS
RETAIL_COST
COMPUTE FLAG1/A1 = IF NOT COUNTRY IN ('ENGLAND', 'FRANCE') THEN '0' ELSE IF SEATS GT 4 THEN '1' ELSE IF SEATS LT 3 THEN '2' ELSE '3'; NOPRINT
BY BODYTYPE
ACROSS COUNTRY
ON TABLE SET STYLE *
TYPE=DATA, ACROSSCOLUMN=SEATS, COLOR=GREEN, WHEN= FLAG1 EQ '1', $
TYPE=DATA, ACROSSCOLUMN=SEATS, COLOR=RED, WHEN= FLAG1 EQ '2', $
TYPE=DATA, ACROSSCOLUMN=SEATS, COLOR=BLUE, WHEN= FLAG1 EQ '3', $
ENDSTYLE
END
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
Expert
posted Hide Post
Thank you for that! I will open a case because this does not make sense.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
The computed NOPRINT condition field MUST be after the column being styled. Then it works.

Ram just beat me
 
Report This Post
Expert
posted Hide Post
"The computed NOPRINT condition field MUST be after the column being styled" - is this documented anywhere?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
SORT OF.

Mainframe FOCUS documentation

http://documentation.informati...eports72/10rpt26.htm

Basically

quote:
If a StyleSheet uses ACROSSCOLUMN with WHEN and a field name referenced in the WHEN condition appears both under the ACROSS and elsewhere in the report (as is possible with a multi-verb request), the field name under the ACROSS takes precedence. You can refer to the other column using another version of the column notation, such as Cn


The upshot is (although not explicitly stated) that because you use SEATS in the COMPUTE, that is what WebFOCUS
finds first in the internal matrix when trying to apply the styling, so the result is that it appears styling is not working.
Under those circumstances positional column notation N,C,B needs to be used.
 
Report This Post
Member
posted Hide Post
Thanks everyone. The positioning worked perfect for the report.
 
Posts: 11 | Registered: October 19, 2006Report This Post
Virtuoso
posted Hide Post
How to apply this to ACROSSVALUE formatting?

We have a report here listing data by age, where age is divided into 4 categories that need colour-coding in the heading. The number of categories displayed is variable, there's not always data for all four age ranges.

I tried the below, but it doesn't colour-code the across value headings:
DEFINE FILE CAR
    COLORIT/A1= IF COUNTRY IN ('ENGLAND','FRANCE') THEN 'Y' ELSE 'N';
END
TABLE FILE CAR
SUM
    SEATS
    RETAIL_COST
    COLORIT NOPRINT
BY BODYTYPE
ACROSS COUNTRY
ON TABLE SET STYLE *
TYPE=ACROSSVALUE, ACROSSCOLUMN=N1, BACKCOLOR=GREEN, WHEN= COLORIT EQ 'Y', $
ENDSTYLE
END


(Using SEATS doesn't work here in 7.6.11 either - we have to use N1)


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
COLUMN=Cx seems to work for ACROSSVALUE, but I could only get this to work by establishing which columns to color in advance:

SET HOLDLIST = PRINTONLY
-*
TABLE FILE CAR
 SUM
 COMPUTE SEQ_NO/I5 = IF (LAST SEQ_NO EQ 0) THEN 1 ELSE (LAST SEQ_NO + 2); NOPRINT
 COMPUTE STYLE/A50 = IF COUNTRY IN ('ENGLAND','JAPAN')
                     THEN 'TYPE=ACROSSVALUE,BACKCOLOR=GREEN,COLUMN=C' || TRIM('L',FPRINT(SEQ_NO,'I5','A5'),5,' ',1,'A5') || ',$'
                     ELSE 'TYPE=ACROSSVALUE,BACKCOLOR=NONE, COLUMN=C' || TRIM('L',FPRINT(SEQ_NO,'I5','A5'),5,' ',1,'A5') || ',$';
 BY COUNTRY NOPRINT
 ON TABLE SAVE AS STYLE1
END
-RUN
-*
TABLE FILE CAR
 SUM
  SEATS
  RETAIL_COST
 BY BODYTYPE
 ACROSS COUNTRY
 ON TABLE SET STYLE *
-INCLUDE STYLE1
 ENDSTYLE
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report 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] Apply color coding in across column values

Copyright © 1996-2020 Information Builders