[CLOSED] Alternating Colors with Stylesheet and Small-Cell Protection

How about using 'Stop Lights' based on 'categories'? Change the background colors based on the value of the category...

In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted

Virtuoso

posted

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.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, 2013

Ignored post by MartinY posted

Platinum Member

posted

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.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Doug

Expert

posted

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 FOCUS to WebFOCUS.
Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted

Platinum Member

posted

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.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Platinum Member

posted

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.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Virtuoso

posted

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.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, 2013

Ignored post by MartinY posted

Platinum Member

posted

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.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Platinum Member

posted

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.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Virtuoso

posted

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.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, 2013

Ignored post by MartinY posted

Tony A

Expert

posted

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

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Virtuoso

posted

Tony

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.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, 2013

Ignored post by MartinY posted