Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Alternating Colors with Stylesheet and Small-Cell Protection
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Alternating Colors with Stylesheet and Small-Cell Protection
 Login/Join
 
Platinum Member
posted
Forum Community:

I know there are many posts about tables and alternating colors. I've read several, but I've not yet stumbled across the one that might answer my question. Of course, I might be overlooking the obvious answer.

I have numerous tables on my portal, all of which rely upon a stylesheet to alternate the colors for every row of data. What I've found is that if one of my nine categories is missing from a particular store, the colors are out of order for the next store. I want to force the alternating colors to always stay in the proper order. Here's a list of categories and the preferred order for my tables.

    Grocery - White
    Floral - Yellow
    Deli - White
    Coffee Shop - Yellow
    Pharmacy - White
    Health and Beauty - Yellow
    Pet Food and Supplies - White
    Automotive - Yellow
    Toys - White
    Total Formatted a different way


I read several posts about forcing colors, many of which discuss the subtotal and total features of AppStudio. I am not using the subtotal and total features due to the small-cell protection procedure we use to protect some of our data. I'm not sure, but it appears as if it might be affecting the alternating colors.

What happens when a category is missing is that the alternating of the colors gets out of order for the next store. So, while the order might be correct for the store above it, the second store is off in terms of its color like this:

This is the first store, and it's correct in terms of the colors.

    Grocery - White
    Floral - Yellow
    Deli - White
    Coffee Shop - Yellow
    Pharmacy - White
    Health and Beauty - Yellow
    Pet Food and Supplies - White
    Automotive - Yellow
    Total Formatted a different way


This is the second store, and it's incorrect in terms of the colors. It picks up from the previous store's colors and does not correct itself even though this store has all nine categories.

    Grocery - Yellow - s/be White
    Floral - White s/be Yellow
    Deli - Yellow s/be White
    Coffee Shop - White s/be Yellow
    Pharmacy - Yellow s/be White
    Health and Beauty - White s/be Yellow
    Pet Food and Supplies - Yellow s/be White
    Automotive - White s/be Yellow
    Toys - Yellow s/be White
    Total Formatted a different way


I might have overlooked a way to force the proper alternating pattern of colors in some of the forum posts. I saw some posts about subtotals and totals, but an important distinction I'd like to note about my tables is that we do not use subtotal and total features due to the small cell procedures we have to use to protect data on our public-facing portal. We have create them different ways as you'll see in the table code (immediately below).

 -* EXTRACT SOURCE DATA
DEFINE FILE DEPARTMENTS
QSET/I4 = EDIT(SALES_YEAR);
YR /YY = QSET;
SORT_DEPT/A46 = IF DEPT_NAME EQ 'Grocery'  THEN '         Grocery' ELSE
	         IF DEPT_NAME EQ 'Floral' THEN '        Floral' ELSE
	         IF DEPT_NAME EQ 'Deli'   THEN '       Deli'    ELSE
			 IF DEPT_NAME EQ 'Coffee Shop' THEN '      Coffee Shop' ELSE
			 IF DEPT_NAME EQ 'Pharmacy' THEN '     Pharmacy' ELSE
			 IF DEPT_NAME EQ 'Health and Beauty' THEN '    Health and Beauty' ELSE
			 IF DEPT_NAME EQ 'Pet Food and Supplies' THEN '   Pet Food and Supplies' ELSE
			 IF DEPT_NAME EQ 'Automotive' THEN '  Automotive'  ELSE
			 IF DEPT_NAME EQ 'Toys' THEN ' Toys' ELSE
			 IF DEPT_NAME EQ 'Total' THEN 'Total' ELSE DEPT_NAME ;
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR-5;

TABLE FILE DEPARTMENTS
-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-*INCLUDE small_cell_protect_5
-*-----------------------------------------------------------------------------------------------
SUM
SUM.DEPARTMENTS.DEPARTMENTS.EMPLOYEES AS 'SC_COUNTS'
BY DEPARTMENTS.DEPARTMENTS.STORE_NAME
BY  DEPARTMENTS.DEPARTMENTS.SALES_YEAR
BY  DEPARTMENTS.DEPARTMENTS.YR
BY  LOWEST DEPARTMENTS.DEPARTMENTS.SORT_DEPT
WHERE ( DEPARTMENTS.DEPARTMENTS.QUARTER_NAME EQ 'First Quarter' )
WHERE (DEPARTMENTS.DEPARTMENTS.YR GE &MINYR);
WHERE (DEPARTMENTS.DEPARTMENTS.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

DEFINE FILE EXTDATA
SALES_YEAR/A100 =EDIT(YR)
END

TABLE FILE EXTDATA
SUM
SC_COUNTS
BY STORE_NAME
BY SALES_YEAR
BY LOWEST SORT_DEPT
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS REPINPUT
ON TABLE SET STYLE *
ENDSTYLE
END

-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-SET &input   = 'repinput';
-SET &output  = 'finaldata';
-SET &dim1    = 'SORT_NAME';
-SET &dim1_id = 'SORT_DEPT';
-SET &dim2    = 'SALES_YEAR';
-SET &measure = 'SC_COUNTS';

-*Include small-cell protection file here

-* Create report based on small protect output.

DEFINE FILE finaldata
SORT_REGION/A2V = IF &dim1 LIKE '%Julie%' THEN '2' ELSE
IF &dim1 LIKE '%Lisa%' THEN '4' ELSE
IF &dim1 LIKE '%Jake%' THEN '3' ELSE '1' ;
END

TABLE FILE finaldata

ON TABLE SUBHEAD
"Stores"
"Employees by Department"
"Sales Years &MINYR - &MAXYR"
"Store Listing"
ON TABLE SUBFOOT
"Source: Sales Data           "
" "
"^ Indicates a cell has been marked for small-cell protection."
    SUM &measure
	 BY SORT_REGION NOPRINT
	 BY &dim1._SORT NOPRINT
	 BY &dim1   AS 'Store'
	 BY LOWEST &dim1_id   AS 'Department'
 ACROSS &dim2      AS 'First Quarter'
WHERE &dim2 NE 'Total' AND &dim1 NE 'Total'
ON &dim1  SUBFOOT
" "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML Web Document (HTML),HTML>,<Excel XLSX (XLSX),XLSX>).Output type.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE STYLESHEET file here
$
TYPE=REPORT,
	 HFREEZE=TOP,
$
TYPE=TITLE,
	 BORDER-BOTTOM=ON,
	 BORDER-BOTTOM-COLOR=RGB (0 58 99),
$
TYPE=TITLE,
	 COLUMN=N3,
     JUSTIFY=LEFT,
	 BORDER-BOTTOM=ON,
	 BORDER-BOTTOM-COLOR=RGB (0 58 99),
$
TYPE=TITLE,
	 COLUMN=N4,
     JUSTIFY=LEFT,
	 BORDER-BOTTOM=ON,
	 BORDER-BOTTOM-COLOR=RGB (0 58 99),
$
TYPE=ACROSSVALUE,
     JUSTIFY=CENTER,
	 WRAP=ON,
$
TYPE=DATA,
     JUSTIFY=CENTER,
$
TYPE=DATA,
     COLUMN=N3,
     JUSTIFY=LEFT,
	 SQUEEZE=ON,
$
TYPE=DATA,
     COLUMN=N4,
     JUSTIFY=LEFT,
$
TYPE=DATA,
 	 BORDER-TOP=OFF,
	 BORDER-BOTTOM=OFF,
$
TYPE=TABFOOTING,
     BORDER-TOP=OFF,
$
TYPE=TABFOOTING,
     LINE=1,
     JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=TABFOOTING,
     LINE=2,
	 OBJECT=TEXT,
     ITEM=1,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=TABFOOTING,
     LINE=3,
	 OBJECT=TEXT,
     ITEM=1,
     SIZE=9,
     STYLE=ITALIC,
$
TYPE=SUBFOOT
	 BORDER-BOTTOM=ON,
	 BORDER-TOP=ON,
$
TYPE=SUBFOOT
	 BORDER-BOTTOM=LIGHT,
	 BORDER-TOP=LIGHT,
$
TYPE=DATA,
COLUMN=N12,
     BORDER-LEFT=LIGHT,
     BORDER-LEFT-COLOR=RGB(0 58 99),
$
TYPE=ACROSSVALUE,
COLUMN=N12,
     BORDER-LEFT=LIGHT,
     BORDER-LEFT-COLOR=RGB(0 58 99),
$
TYPE=DATA,        WHEN = &dim1_id  EQ 'Total', COLOR=RGB(255 226 146), STYLE=NORMAL, $
TYPE=DATA,        WHEN = &dim1_id  EQ 'Total', BACKCOLOR=RGB(0 58 99), STYLE=NORMAL, $
TYPE=DATA,        WHEN = &dim1_id  EQ 'Total', BORDER-BOTTOM=LIGHT, BORDER-BOTTOM-COLOR=RGB(0 58 99),$
TYPE=DATA,        WHEN = &dim1_id  EQ 'Total', BORDER-BOTTOM=LIGHT, BORDER-BOTTOM-COLOR=RGB(0 58 99),$
ENDSTYLE
END
-RUN




 


And here is our stylesheet code:

 type=report,
    color=rgb(0 0 0),
    font='CALIBRI',
    size=10,
    squeeze=on,
    grid=off,
	LEFTMARGIN=0.05,
    TOPMARGIN=0.025,
$
type=report,
    object=status-area,
    color=white,
    backcolor=rgb(240 190 48),
$
type=report,
    object=current-row,
    backcolor=rgb (0 0 0),
    hover-backcolor=rgb(255 243 209),
$
type=report,
	hfreeze=top,
    graphcolor=green,
    graphcolorneg=red,
$
type=report,
    object=calc-area,
    color=white,
    backcolor=rgb(240 190 48),
$
type=report,
    object=menu,
    color=white,
    backcolor=rgb(240 190 48),
    border-color=white,
    hover-color=rgb(0 58 99),
    hover-backcolor=rgb(255 243 209),
$
type=pagenum,
    style=italic,
    size=8,
    justify=right,
$
type=tabheading,
    line=1,
    justify=left,
    style=bold,
    size=16,
$
type=tabheading,
    line=2,
    justify=left,
    style=normal,
    size=14,
$
type=tabheading,
    line=4,
    justify=left,
    style=normal,
    size=13,
$
type=tabheading,
    line=5,
    justify=left,
    style=normal,
    size=13,
$
type=tabheading,
    line=3,
    justify=left,
    style=normal,
    size=14,
$
type=tabfooting,
    style=normal,
    size=11,
    justify=right,
    border-top=light,
    border-top-color=rgb(0 58 99),
$
type=heading,
    style=bold,
    size=14,
    justify=left,
$
type=footing,
    style=bold,
    size=12,
    justify=left,
$
type=title,
    backcolor=rgb(0 58 99),
    color=rgb(255 226 146),
    style=-underline,
	justify=center,
$
type=data,
    backcolor=(rgb(255 255 255) rgb(255 243 209)),
    justify=center,
$
type=acrosstitle,
    border-top=light,
    border-bottom=light,
    border-top-color=rgb(0 58 99),
    border-bottom-color=rgb(0 58 99),
	color=rgb(0 58 99),
	backcolor=rgb(255 255 255),
    size=12,
    style=-underline,
	wrap=on,
$
type=acrossvalue,
    border-top=light,
	border-top-color=rgb(0 58 99),
    backcolor=rgb(255 243 209),
	color=rgb(0 58 99),
    style=normal,
$
type=subhead,
    size=12,
    style=bold,
	border-top=light,
    border-bottom=light,
    border-top-color=rgb(0 58 99),
    border-bottom-color=rgb(0 58 99),
$
type=subfoot,
    size=12,
    style=bold,
    border-top=light,
    border-bottom=light,
    border-top-color=rgb(0 58 99),
    border-bottom-color=rgb(0 58 99),
$
type=subtotal,
    backcolor=RGB(255 255 255),
	color=rgb(0 0 0),
    style=normal,
    border-top=light,
    border-top-color=rgb(0 58 99),
	border-bottom=light,
    border-bottom-color=rgb(0 58 99),
$
type=grandtotal,
    backcolor=rgb(0 58 99),
    color=rgb(255 226 146),
    style=normal,
    border-top=light,
    border-bottom=light,
    border-top-color=rgb(0 58 99),
    border-top-style=solid,
    border-bottom-style=double,
$

*GRAPH_SCRIPT
setFillColor(getSeries(0),new Color(0,58,99));
setFillColor(getSeries(1),new Color(213,159,15));
setFillColor(getSeries(2),new Color(191,49,26));
setFillColor(getSeries(3),new Color(103,145,70));
setFillColor(getSeries(4),new Color(98,26,76));
setFillColor(getSeries(5),new Color(145,145,149));
setFillColor(getSeries(6),new Color(222,121,28));
setFillColor(getSeries(7),new Color(185,199,212));
setFillColor(getSeries(8),new Color(255,226,146));
setFillColor(getSeries(9),new Color(247,163,129));
setFillColor(getSeries(10),new Color(206,224,179));
setFillColor(getSeries(11),new Color(210,188,195));
setFillColor(getSeries(12),new Color(201,203,204));
setFillColor(getSeries(13),new Color(250,215,179));
setFillColor(getSeries(14),new Color(94,124,152));
setFillColor(getSeries(15),new Color(240,190,48));
setFillColor(getSeries(16),new Color(241,93,34));
setFillColor(getSeries(17),new Color(154,193,125));
setFillColor(getSeries(18),new Color(158,110,125));
setFillColor(getSeries(19),new Color(177,177,177));
setFillColor(getSeries(20),new Color(236,168,106));
setFillColor(getSeries(21),new Color(213,237,255));
setFillColor(getSeries(22),new Color(251,237,202));
setFillColor(getSeries(23),new Color(248,209,203));
setFillColor(getSeries(24),new Color(224,236,215));
setFillColor(getSeries(25),new Color(238,193,223));
setFillColor(getSeries(26),new Color(242,242,242));
setFillColor(getSeries(27),new Color(249,227,208));
setFillColor(getSeries(28),new Color(146,168,188));
setFillColor(getSeries(29),new Color(248,224,158));
setFillColor(getSeries(30),new Color(244,125,78));
setFillColor(getSeries(31),new Color(183,210,162));
setFillColor(getSeries(32),new Color(195,165,174));
setFillColor(getSeries(33),new Color(192,192,192));
setFillColor(getSeries(34),new Color(241,190,143));
setFillColor(getSeries(35),new Color(189,228,255));
setFillColor(getSeries(36),new Color(245,211,115));
setFillColor(getSeries(37),new Color(236,130,112));
setFillColor(getSeries(38),new Color(128,128,128));
setFillColor(getTimeScaleLevel(0), new Color(240,159,97));
setFillColor(getTimeScaleLevel(1), new Color(255,243,172));
setFillColor(getTimeScaleLevel(2), new Color(106,189,187));
setFillColor(getY1Title(),new Color(0,0,0));
setFillColor(getY1Label(),new Color(0,0,0));
setFillColor(getY2Label(),new Color(0,0,0));
setFillColor(getY3Label(),new Color(0,0,0));
setFillColor(getY4Label(),new Color(0,0,0));
setFillColor(getY5Label(),new Color(0,0,0));
setFillColor(getO1Title(),new Color(0,0,0));
setFillColor(getX1Title(),new Color(0,0,0));
setFillColor(getO1Label(),new Color(0,0,0));
setFillColor(getO2Label(),new Color(0,0,0));
setFillColor(getX1Label(),new Color(0,0,0));
setFillColor(getLegendText(),new Color(0,0,0));
setFillColor(getFrame(),new Color(235,235,240));
setFillColor(getChartBackground(),new Color(255,255,255));
setFillColor(getCubeFloor(),new Color(255,255,255));
setFillColor(getCubeLeftWall(),new Color(255,255,255));
setFillColor(getCubeRightWall(),new Color(255,255,255));
setFillColor(getFrame(),new Color(255,255,255));
setFillColor(getChartBackground(),new Color(255,255,255));
setFillColor(getFrameBottom(),new Color(255,255,255));
setFillColor(getFrameSeparator(),new Color(255,255,255));
setFillColor(getFrameSide(),new Color(255,255,255));
setFillColor(getHistogramRiser(),new Color(158,144,188));
setFillColor(getLegendArea(),new Color(255,255,255));
setBorderColor(getSeries(0),new Color(0,0,0));
setBorderColor(getSeries(1),new Color(0,0,0));
setBorderColor(getSeries(2),new Color(0,0,0));
setBorderColor(getSeries(3),new Color(0,0,0));
setBorderColor(getSeries(4),new Color(0,0,0));
setBorderColor(getSeries(5),new Color(0,0,0));
setBorderColor(getSeries(6),new Color(0,0,0));
setBorderColor(getSeries(7),new Color(0,0,0));
setBorderColor(getSeries(8),new Color(0,0,0));
setBorderColor(getSeries(9),new Color(0,0,0));
setBorderColor(getSeries(10),new Color(0,0,0));
setBorderColor(getSeries(11),new Color(0,0,0));
setBorderColor(getSeries(12),new Color(0,0,0));
setBorderColor(getSeries(13),new Color(0,0,0));
setBorderColor(getSeries(14),new Color(0,0,0));
setBorderColor(getSeries(15),new Color(0,0,0));
setBorderColor(getSeries(16),new Color(0,0,0));
setBorderColor(getSeries(17),new Color(0,0,0));
setBorderColor(getSeries(18),new Color(0,0,0));
setBorderColor(getSeries(19),new Color(0,0,0));
setBorderColor(getSeries(20),new Color(0,0,0));
setBorderColor(getHistogramRiser(),new Color(0,0,0));
setBorderColor(getLegendArea(),new Color(0,0,0));
setBorderColor(getY1MajorGrid(),new Color(204,204,204));
setBorderColor(getY1MinorGrid(),new Color(204,204,204));
setBorderColor(getY2MajorGrid(),new Color(204,204,204));
setBorderColor(getY2MinorGrid(),new Color(204,204,204));
setBorderColor(getO1MajorGrid(),new Color(204,204,204));
setBorderColor(getO1MinorGrid(),new Color(204,204,204));
setBorderColor(getX1MajorGrid(),new Color(204,204,204));
setBorderColor(getX1MinorGrid(),new Color(204,204,204));
setBorderColor(getO1AxisLine(),new Color(130,130,130));
setBorderColor(getY1AxisLine(),new Color(130,130,130));
setBorderColor(getCubeFloor(),new Color(0,0,0));
setBorderColor(getCubeLeftWall(),new Color(0,0,0));
setBorderColor(getCubeRightWall(),new Color(0,0,0));
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setTransparentBorderColor(getSeries(11),true);
setTransparentBorderColor(getSeries(12),true);
setTransparentBorderColor(getSeries(13),true);
setTransparentBorderColor(getSeries(14),true);
setTransparentBorderColor(getSeries(15),true);
setTransparentBorderColor(getSeries(16),true);
setTransparentBorderColor(getSeries(17),true);
setTransparentBorderColor(getSeries(18),true);
setTransparentBorderColor(getSeries(19),true);
setTransparentBorderColor(getSeries(20),true);
setTransparentBorderColor(getFrameBottom(),false);
setTransparentBorderColor(getFrameSide(),false);
setTransparentBorderColor(getLegendArea(),false);
setTransparentBorderColor(getO1MajorGrid(),false);
setTransparentBorderColor(getPieFrame(),true);
setTransparentBorderColor(getCubeFloor(),false);
setTransparentBorderColor(getCubeLeftWall(),false);
setTransparentBorderColor(getCubeRightWall(),false);
setTransparentBorderColor(getChartBackground(),true);
setTransparentBorderColor(getLegendArea(), true);
setTransparentFillColor(getLegendArea(),true);
setTransparentBorderColor(getHistogramRiser(),true);
setCubeFocusFactor(95.83333182119424);
setCubePanX(41.3);
setCubePanY(54.7);
setCubeRotationMatrix(0.9527877123115605,-0.39648905742363183,1.0691265903653806,-0.012240167246073914,1.389589570659449,0.5262420232508634,-1.1402128823990627,-0.3462324595884829,0.8877371966710723);
setCubeViewerZ(93.61645050038162);
setCubeZoomFactor(55.0);
setCubeWallThickZ(0.0);
setCubeWallThickY(0.0);
setCubeWallThickX(0.0);
setY1MajorGridStyle(0);
setY2MajorGridStyle(0);
setO1MajorGridStyle(0);
setX1MajorGridStyle(0);
setLineStyleLegendMarkers(3);
setNullLegendMarkerShapesAsSquares(false);
setFrameAutoShade(true);
setColorMode(1);
setUseDefaultBubbleMarker(false);
setFontName(getDataText(),"CALIBRI");
setFontName(getPieLabel(),"CALIBRI");
setFontName(getPieBarLabel(),"CALIBRI");
setFontName(getPieRingLabel(),"CALIBRI");
setFontName(getPieSliceLabel(),"CALIBRI");
setFontName(getLegendText(),"CALIBRI");
setFontName(getO1Title(),"CALIBRI");
setFontName(getX1Title(),"CALIBRI");
setFontName(getY1Title(),"CALIBRI");
setFontName(getO2Title(),"CALIBRI");
setFontName(getY2Title(),"CALIBRI");
setFontName(getY3Title(),"CALIBRI");
setFontName(getY4Title(),"CALIBRI");
setFontName(getY5Title(),"CALIBRI");
setFontName(getTitle(),"CALIBRI");
setFontName(getSubtitle(),"CALIBRI");
setFontName(getFootnote(),"CALIBRI");
setFontName(getAllText(),"CALIBRI");
setReverseSeries(false);
setDisplay(getFootnote(),true);

*GRAPH_JS
"mouseOverIndicator": {
         "enabled": true,
         "color": "rgba(255,255,255,0.5)",
	"marker":{"size": 0}
},
"introAnimation": {
   "enabled": true,
   "duration": 1400
  },
htmlToolTip: {
	enabled: true,
-*    snap: true,
    style: {
		border: 'thin #FFFFFF',
		background: '#FFFFFF',
		font: "CALIBRI",
	},
},
"riserBevel":"bevel"


*END
 

This message has been edited. Last edited by: FP Mod Chuck,


WebFocus 8.1.05M
AppStudio 8.1.05M

 
Posts: 100 | Registered: September 18, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
How about using 'Stop Lights' based on 'categories'? Change the background colors based on the value of the category...


   In FOCUS Since 1983 ~ from PC FOCUS to WebFOCUS.
   Currrently using 8204 Gen 48 in Test and Production.
   KGPCo at CenturyLink. October 2017 to Present...
 
Posts: 2802 | Location: Nashville TN Area [Test&Prod:8204Mg48] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You can also use something such as this
DEFINE FILE CAR
DSPYEL /A1 = IF COUNTRY EQ 'FRANCE' OR 'JAPAN' THEN 'Y' ELSE 'N';
END
TABLE FILE CAR
SUM SEATS
    DSPYEL NOPRINT
BY COUNTRY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

     DEFMACRO=BACKYELLOW,
     MACTYPE=RULE,
     WHEN=DSPYEL EQ 'Y',
$
TYPE=DATA,
    BACKCOLOR=RGB(255 255 255),
    JUSTIFY=CENTER,
$
TYPE=DATA,
    BACKCOLOR=RGB(255 243 209),
    JUSTIFY=CENTER,
    MACRO=BACKYELLOW,
$
ENDSTYLE
END

The DEFINE will include all categories that you want to be yellow


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 1880 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thank you both for the replies. I tried Martin's, and while it worked for the question I asked (and I learned a new technique), it didn't address what I really need (I didn't ask my question right).

If a category is missing, either one of these solutions will not adjust the coloring properly for that particular store. So, if one of the categories is missing, it will leave out the yellow and then end up with two white rows. Is there a way to make the color adjust appropriately?


WebFocus 8.1.05M
AppStudio 8.1.05M

 
Posts: 100 | Registered: September 18, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
Can you duplicate that issue using one of the IBI SAMPLE sources? If so, we can definitely answer your question. I know we can do that in WebFOCUS...


   In FOCUS Since 1983 ~ from PC FOCUS to WebFOCUS.
   Currrently using 8204 Gen 48 in Test and Production.
   KGPCo at CenturyLink. October 2017 to Present...
 
Posts: 2802 | Location: Nashville TN Area [Test&Prod:8204Mg48] | Registered: February 23, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Doug,

That's a good question about the sample sources. I'll look for them and see if I can replicate it there. I might also just try to upload a simple Excel file. It shows the issue clearly.

I'll get back to you.

Thank you.


WebFocus 8.1.05M
AppStudio 8.1.05M

 
Posts: 100 | Registered: September 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I've tried to use some of the sample sources, and I receive error messages, and I remembered we cannot upload images easily to the forum.

So, I'm going to try and explain a different way.

If a category is missing, using the methods outlined earlier, here's what it looks like:

Store 1
Row 1 - Back color is blue
Row 2 - Back color is green
Row 3 - Back color is blue
Row 4 - Back color is blue

In reality, this is how the rows should look for that store:

Store 1
Row 1 - Back color is blue
Row 2 - Back color is green
Row 3 - Back color is blue
Row 4 - Back color is green

The next store has all the categories and should look this:

Store 2
Row 1 - Back color is blue
Row 2 - Back color is green
Row 3 - Back color is blue
Row 4 - Back color is green
Row 5 - Back color is blue

Due to how Store 1 looks though, it's producing this effect:

Row 1 - Back color is green
Row 2 - Back color is blue
Row 3 - Back color is green
Row 4 - Back color is blue
Row 5 - Back color is green

Is there a way to adjust the color based on when the store name changes to another store?

Thanks for any help.


WebFocus 8.1.05M
AppStudio 8.1.05M

 
Posts: 100 | Registered: September 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I am confused.

Automatic color alternate should manage that by itself.

But you've requested to keep the categories the same color which my sample is doing
Grocery - White
Floral - Yellow
Deli - White
Coffee Shop - Yellow
Pharmacy - White
Health and Beauty - Yellow
Pet Food and Supplies - White
Automotive - Yellow
Toys - White


So what you are saying that you donSo, You would like to have the color "reset" ? But what for the next region ? Back to the original color pattern or continue the pattern as is with the automatic color alternate ?

Fixed color alternate per "type"
DEFINE FILE GGSALES
DSPYEL /A1 = IF PRODUCT EQ 'Capuccino' OR 'Coffee Pot' OR 'Espresso' OR 'Mug' OR 'Thermos' THEN 'Y' ELSE 'N';
END
TABLE FILE GGSALES
SUM DOLLARS
    DSPYEL NOPRINT
BY REGION
BY PRODUCT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

     DEFMACRO=BACKYELLOW,
     MACTYPE=RULE,
     WHEN=DSPYEL EQ 'Y',
$
TYPE=DATA,
    BACKCOLOR=RGB(255 255 255),
    JUSTIFY=CENTER,
$
TYPE=DATA,
    BACKCOLOR=RGB(255 243 209),
    JUSTIFY=CENTER,
    MACRO=BACKYELLOW,
$
ENDSTYLE
END


Automatic color alternate
TABLE FILE GGSALES
SUM DOLLARS
BY REGION
BY PRODUCT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=DATA,
    BACKCOLOR=(RGB(255 255 255) RGB(255 243 209)),
    JUSTIFY=CENTER,
$
ENDSTYLE
END


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 1880 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Yes, Martin, that was my original question and in my reply, I admitted I didn't ask my question correctly. I will try the method you just outlined for me, but it might be tomorrow. Thanks so much!


WebFocus 8.1.05M
AppStudio 8.1.05M

 
Posts: 100 | Registered: September 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Martin,

First, I want to say thank you again for all your suggestions on this post and some of my other posts in the past. I have learned so much from you that I have used on my portal.

As far as this post, I tried your latest suggestion. Although it alternates the colors nicely and I've learned another new trick, it still didn't address my business challenge in full, something my users would really like to see.

I want the color scheme to start over with each new store. Is that even possible?

So, what I'm saying is this: If the first store has nine categories, the color scheme will be perfect. If the second store has six categories, the suggestion you provided alternates the colors, but it does not start the scheme over from scratch. I wonder if it has something to do with the code I posted at the beginning.

You'll note I use both a subhead and subfoot. There is an extra white line between stores. Every time the store name changes, I truly want the color scheme to start over from the beginning. The white line serves as a divider, and the totals are custom formatted in blue. Is it possible to start the color scheme over at each store?

Thanks for any assistance. Have a great evening.


WebFocus 8.1.05M
AppStudio 8.1.05M

 
Posts: 100 | Registered: September 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi Julie,

Sure it is possible.
Instead of using the categories as the pattern indicator (as per my sample with the define) use a row indicator.
Meaning that for each row from a store set a number to it (rowid) no matter the category and then use the rowid in the define to set the color.
But you will need one more step

TABLE FILE GGSALES
SUM DOLLARS
BY REGION
BY TOTAL COMPUTE ROWID /I2 = IF REGION EQ LAST REGION THEN ROWID + 1 ELSE 1;
BY PRODUCT
ON TABLE HOLD AS EXTDATA
END
-RUN

DEFINE FILE EXTDATA
-*-* FOR EACH EVEN ROW, THE COLOR WILL CHANGE
DSPYEL /A1 = IF IMOD(ROWID, 2, 'I2') EQ 0 THEN 'Y' ELSE 'N';
END
TABLE FILE EXTDATA
SUM DOLLARS
    DSPYEL NOPRINT
BY REGION
ON REGION SUBHEAD
""
BY PRODUCT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

     DEFMACRO=BACKYELLOW,
     MACTYPE=RULE,
     WHEN=DSPYEL EQ 'Y',
$
TYPE=DATA,
    BACKCOLOR=RGB(255 255 255),
    JUSTIFY=CENTER,
$
TYPE=DATA,
    BACKCOLOR=RGB(255 243 209),
    JUSTIFY=CENTER,
    MACRO=BACKYELLOW,
$
ENDSTYLE
END
-RUN

Using the above you will see the difference with my previous sample (Fixed color alternate per "type") where for Midwest the first two rows had a blank background


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 1880 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Primed by Martin, you can do this in a single pass -

TABLE FILE GGSALES
SUM DOLLARS
BY REGION
BY TOTAL COMPUTE ROWID /I2 = IF REGION EQ LAST REGION THEN ROWID ELSE ABS(LAST ROWID - 1); NOPRINT
BY PRODUCT
ON TABLE SET STYLE *
TYPE=DATA,
    BACKCOLOR=RGB(255 255 255),
    JUSTIFY=CENTER, WHEN=ROWID EQ 0,
$
TYPE=DATA,
    BACKCOLOR=RGB(255 243 209),
    JUSTIFY=CENTER, WHEN=ROWID NE 0,
$
ENDSTYLE
END
-RUN


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.05 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.05 standalone on Windows 10 
 
Posts: 5487 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Tony Good One

It's an old habit that I have to always first extract the data that I need to produce the report and have the latest step (report display) with as less as possible of data manipulation.
But yes, when your source data allows you to perform in one step, why not.


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 1880 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Alternating Colors with Stylesheet and Small-Cell Protection

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.