Hello. I build a utilization schedule in webfocus which colors in the cell if there is a shift or more. This report is by machine across week ending date. Each color represents a different customer. We would like to build a legend to show which color represents which customer. Each division will have a different set of customers. I'd think we'd build the legend to show all customers, but then have a filter on division so when a division is selected, only the customers that are in the division show up. Any help would be greatly appreciated! We are using App studio 8201

I'd like to add an image of my report, but I'm not sure how!

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

This can be a start but to built in a footer it will need more work
Depending on number of customer, it may be a pain

TABLE FILE GGSALES
BY REGION AS ''

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=REPORT,
     BORDER=OFF,
     SIZE=10,
     JUSTIFY=CENTER,
$
TYPE=DATA,
     COLOR=WHITE,
     BACKCOLOR=GREY,
$
TYPE=DATA,
     COLOR=WHITE,
     BACKCOLOR=BLUE,
     WHEN=REGION EQ 'Midwest',
$
TYPE=DATA,
     COLOR=BLACK,
     BACKCOLOR=YELLOW,
     WHEN=REGION EQ 'Northeast',
$
TYPE=DATA,
     COLOR=WHITE,
     BACKCOLOR=RGB(8 97 74),
     WHEN=REGION EQ 'Southeast',
$
ENDSTYLE
END
-RUN

That's how I built my code to create my colors. Basically, I'd like a two column legend in the footer that looks like the below. So the first column will be the color, the second column is the name of the customer


Box fill with red| BNSF
Box fill with green| Canadian Pacific
box fill with yellow| Canadian National

To have it in a FOOTING it's going to be much more difficult because the footing is "static" in a sense that it doesn't not depend on the displayed data except if you manually creates the number of lines as per number of REGION

DEFINE FILE GGSALES
DUMMY /A10 = '          ';
END
TABLE FILE GGSALES
BY DUMMY  AS ''
BY REGION AS ''

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=REPORT,
     BORDER=OFF,
     SIZE=10,
     JUSTIFY=CENTER,
$
TYPE=DATA,
     COLUMN=DUMMY,
     COLOR=WHITE,
     BACKCOLOR=GREY,
$
TYPE=DATA,
     COLUMN=DUMMY,
     COLOR=WHITE,
     BACKCOLOR=BLUE,
     WHEN=REGION EQ 'Midwest',
$
TYPE=DATA,
     COLUMN=DUMMY,
     COLOR=BLACK,
     BACKCOLOR=YELLOW,
     WHEN=REGION EQ 'Northeast',
$
TYPE=DATA,
     COLUMN=DUMMY,
     COLOR=WHITE,
     BACKCOLOR=RGB(8 97 74),
     WHEN=REGION EQ 'Southeast',
$
ENDSTYLE
END
-RUN

What is the output format of your schedule ?
Maybe a compound document may be an option where you will have your report and your legend (where the legend become a regular report as per above) added together.

The output I have as an option of either PDF or Excel.

A compound document could be a good option for the PDF format as I stated earlier.
For the Excel format, may not be the best solution since you will have the detailed data (report) in one tab and the legend in another tab. This assuming that you have one report for detail and one for legend as I suggested.

As I mentioned, having the legend in a report's footer may be a pain to develop especially because it's not a fixed footer.
The easiest way that I can think of is to have predefined footer and footer style in separate fex (one per division).
Each will have the categories listed with their respective color assigned
Then, in your report, based on a IF condition you -INCLUDE the proper footer fex and the proper footer style fex according to the division.

A IF...THEN GOTO option is also possible as per below.
But since your FOOTING section may also change (which is not the case in my sample where I always have three lines), you will also need to manage with IF...THEN GOTO
So, to have it more clear and lighter, instead of having the whole code included into linked section of the GOTO, you just have a -INCLUDE folder/SE_COLOR.fex as per example (also possible for the FOOTING section)

-DEFAULTH &LOC = '_FOC_NULL'
-*-DEFAULTH &LOC = 'Midwest'
-*-DEFAULTH &LOC = 'Northeast'
-*-DEFAULTH &LOC = 'Southeast'

TABLE FILE GGSALES
SUM DOLLARS/D8 AS 'Sold $'
BY CATEGORY NOPRINT
BY PRODUCT
BY TOTAL COMPUTE DUMMY /A5 = 'DUMMY'; NOPRINT
WHERE REGION EQ '&LOC';

FOOTING
"<DUMMY Coffee"
"<DUMMY Food"
"<DUMMY Gifts"

ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=REPORT,
     BORDER=OFF,
     SIZE=10,
     JUSTIFY=CENTER,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE, BACKCOLOR=GREY,
$
TYPE=FOOTING,
     COLOR=BLACK,
     BACKCOLOR=WHITE,
$
TYPE=FOOTING,
     LINE=1, OBJECT=TEXT,  ITEM=1, BACKCOLOR=WHITE,  COLOR=BLACK,  WIDTH=0.5,
$
TYPE=FOOTING,
     LINE=2, OBJECT=TEXT,  ITEM=1, BACKCOLOR=WHITE,  COLOR=BLACK,  WIDTH=0.5,
$
TYPE=FOOTING,
     LINE=3, OBJECT=TEXT,  ITEM=1, BACKCOLOR=WHITE,  COLOR=BLACK,  WIDTH=0.5,
$
-IF &LOC EQ 'Midwest'   THEN GOTO MWCOLOR;
-IF &LOC EQ 'Northeast' THEN GOTO NECOLOR;
-IF &LOC EQ 'Southeast' THEN GOTO SECOLOR;
TYPE=FOOTING,
     LINE=1, OBJECT=FIELD, ITEM=1, BACKCOLOR=RED,    COLOR=RED,     WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE,  BACKCOLOR=RED,
     WHEN=CATEGORY EQ 'Coffee',
$
TYPE=FOOTING,
     LINE=2, OBJECT=FIELD, ITEM=1, BACKCOLOR=GREY,   COLOR=GREY,    WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE,  BACKCOLOR=GREY,
     WHEN=CATEGORY EQ 'Food',
$
TYPE=FOOTING,
     LINE=3, OBJECT=FIELD, ITEM=1, BACKCOLOR=PURPLE, COLOR=PURPLE,  WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE,  BACKCOLOR=PURPLE,
     WHEN=CATEGORY EQ 'Gifts',
$
-GOTO ENDSTY
-MWCOLOR
TYPE=FOOTING,
     LINE=1, OBJECT=FIELD, ITEM=1, BACKCOLOR=SILVER, COLOR=SILVER, WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE,  BACKCOLOR=SILVER,
     WHEN=CATEGORY EQ 'Coffee',
$
TYPE=FOOTING,
     LINE=2, OBJECT=FIELD, ITEM=1, BACKCOLOR=ORANGE, COLOR=ORANGE, WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=BLACK,  BACKCOLOR=ORANGE,
     WHEN=CATEGORY EQ 'Food',
$
TYPE=FOOTING,
     LINE=3, OBJECT=FIELD, ITEM=1, BACKCOLOR=RGB(22 54 157), COLOR=RGB(22 54 157),  WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE,  BACKCOLOR=RGB(22 54 157),
     WHEN=CATEGORY EQ 'Gifts',
$
-GOTO ENDSTY
-NECOLOR
TYPE=FOOTING,
     LINE=1, OBJECT=FIELD, ITEM=1, BACKCOLOR=BLUE,   COLOR=BLUE,   WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE,  BACKCOLOR=BLUE,
     WHEN=CATEGORY EQ 'Coffee',
$
TYPE=FOOTING,
     LINE=2, OBJECT=FIELD, ITEM=1, BACKCOLOR=YELLOW, COLOR=YELLOW, WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=BLACK,  BACKCOLOR=YELLOW,
     WHEN=CATEGORY EQ 'Food',
$
TYPE=FOOTING,
     LINE=3, OBJECT=FIELD, ITEM=1, BACKCOLOR=GREEN,  COLOR=GREEN,  WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE,  BACKCOLOR=GREEN,
     WHEN=CATEGORY EQ 'Gifts',
$
-GOTO ENDSTY
-SECOLOR
-INCLUDE folder/SE_COLOR.fex
-GOTO ENDSTY

-ENDSTY
ENDSTYLE
END
-RUN

Fex SE_COLOR

TYPE=FOOTING,
     LINE=1, OBJECT=FIELD, ITEM=1, BACKCOLOR=YELLOW, COLOR=YELLOW, WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=BLACK,  BACKCOLOR=YELLOW,
     WHEN=CATEGORY EQ 'Coffee',
$
TYPE=FOOTING,
     LINE=2, OBJECT=FIELD, ITEM=1, BACKCOLOR=GREEN,  COLOR=GREEN,  WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE,  BACKCOLOR=GREEN,
     WHEN=CATEGORY EQ 'Food',
$
TYPE=FOOTING,
     LINE=3, OBJECT=FIELD, ITEM=1, BACKCOLOR=BLUE,   COLOR=BLUE,  WIDTH=0.5,
$
TYPE=DATA,
     COLUMN=DOLLARS, COLOR=WHITE,  BACKCOLOR=BLUE,
     WHEN=CATEGORY EQ 'Gifts',
$

Do you know how to get a compound report on to one page?

Jen

When you create a compound document it brings up a design page and you can place multiple reports onto a single page

It's not looking exactly how I want it when using the designer. I put in my full report on top, and my customer report on the bottom. If both reports are too long, it splits both reports on to a second page. Any way to fix this problem? It works great it the reports are short though.

Thanks for your help!

I think I got it to work! I set a relationship between my two reports. Thanks for helping with that. Wondering if anyone can provide guidance on another issue. On my second report (code pasted below) I have 2 columns: 1. Customer ID 2. Dummy (that I use in order to put in color). I want to be able to change the Customer_ID to the name rather than the ID. I tried the below coding, but it didn't work

TYPE=DATA,
COLUMN=IFS_CUSTOMER_ID,
VALUE=BNSF
WHEN=IFS_CUSTOMER_ID EQ '1000',
$

Wondering if there is different verbiage I can use instead of "Value"



-SET &SPCS = &SPCS | HEXBYT(160, 'A1');
-SET &HEADING_INFO = &CURYEAR | ' Utilization Schedule';
-SET &FOOTER_INFO = IFS_CUSTOMER_ID;


DEFINE FILE DW_CODE_B_V2
VALID_YEAR/YYMD = VALID_UNTIL;
VALID_YR/YY = VALID_YEAR;
END


TABLE FILE DW_CODE_B_V2
SUM
DW_CODE_B_V2.DW_CODE_B_V2.CODE_B
BY CODE_B
BY COST_CENTER_DESC
WHERE COST_CENTER_DESC NOT LIKE '%DO NOT USE%';
WHERE BUSINESS_SEGMENT EQ &BUSINESS_SEGMENT;
WHERE SUB_BUSINESS_SEGMENT EQ &SUB_BUSINESS_SEGMENT;
WHERE DIVISION EQ &DIVISION;
WHERE SUB_DIVISION EQ &SUB_DIVISION;
WHERE COST_CENTER_GROUP EQ &COST_CENTER_GROUP;
WHERE COST_CENTER EQ &CODE_B;
WHERE VALID_YR GE &CURYEAR;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS CODEBDAT
END



JOIN
LEFT_OUTER CODEBDAT.CODEBDAT.CODE_B IN CODEBDAT TO MULTIPLE
CODE_B_DATES.CODE_B_DATES.CODE_B IN code_b_dates TAG J0 AS J0
END

TABLE FILE CODEBDAT
SUM
J0.CODE_B_DATES.WEEK_END_DATE
BY J0.CODE_B_DATES.CODE_B
BY J0.CODE_B_DATES.COST_CENTER_DESC
BY J0.CODE_B_DATES.YEAR_PERIOD_DAY
WHERE ACCOUNT_YEAR EQ &CURYEAR;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS WKNDDAYS
END

JOIN
LEFT_OUTER CODEBDAT.CODEBDAT.CODE_B IN CODEBDAT TO MULTIPLE
DW_BUDGET_DAILY_SHIFTS.BUDGETDAILYSHIFTS.CODE_B IN dw_budget_daily_shifts TAG J2 AS J2
END

TABLE FILE CODEBDAT
BY IFS_CUSTOMER_ID
BY WEEK_END
BY CODE_B
WHERE ACCOUNT_YEAR EQ &CURYEAR;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS WEEKENDS
END


JOIN
LEFT_OUTER WKNDDAYS.WKNDDAYS.CODE_B AND WKNDDAYS.WKNDDAYS.WEEK_END_DATE IN
WKNDDAYS TO UNIQUE WEEKENDS.WEEKENDS.CODE_B
AND WEEKENDS.WEEKENDS.WEEK_END
IN WEEKENDS TAG J1 AS J1
END

-*JOIN
-*LEFT_OUTER WKNDDAYS.WKNDDAYS.CODE_B AND WKNDDAYS.WKNDDAYS.YEAR_PERIOD_DAY IN
-*WKNDDAYS TO UNIQUE DW_BUDGET_DAILY_SHIFTS.BUDGETDAILYSHIFTS.CODE_B
-*AND DW_BUDGET_DAILY_SHIFTS.BUDGETDAILYSHIFTS.YEAR_PERIOD_DAY
-*IN dw_budget_daily_shifts TAG J1 AS J1
-*END

TABLE FILE WKNDDAYS
SUM
J1.WEEKENDS.IFS_CUSTOMER_ID
BY CODE_B
BY COST_CENTER_DESC
BY WEEK_END_DATE
-*WHERE J0.BUDGETDAILYSHIFTS.BUDGET_TYPE EQ '&BUDGET_TYPE';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS TEMPDATA
END

TABLE FILE TEMPDATA
BY IFS_CUSTOMER_ID AS 'Customer'
BY COMPUTE DUMMY/A5='DUMMY'; AS ''
WHERE IFS_CUSTOMER_ID NE '';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,,,,,,,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=8,
COLOR='BLACK',
STYLE=NORMAL,
PAGESIZE=LEGAL,
ORIENTATION=LANDSCAPE,
LEFTMARGIN=0.00000,
RIGHTMARGIN=0.00000,
TOPMARGIN=0.055556,
BOTTOMMARGIN=0.055556,
GRID=ON,
LEFTGAP=0,
RIGHTGAP=0,
$
TYPE=DATA,
COLUMN=DUMMY,
BACKCOLOR=RGB(102 255 51),
COLOR=RGB(102 255 51),
WHEN= IFS_CUSTOMER_ID EQ '1000',
WIDTH=0.2,
$
TYPE=DATA,
COLUMN=DUMMY,
BACKCOLOR=RGB(255 192 0),
COLOR=RGB(255 192 0),
WHEN= IFS_CUSTOMER_ID EQ '1001',
WIDTH=0.2,
$
TYPE=DATA,
COLUMN=DUMMY,
BACKCOLOR=RGB(255 0 0),
COLOR=RGB(255 0 0),
WHEN= IFS_CUSTOMER_ID EQ '1002',
WIDTH=0.2,
$
TYPE=DATA,
COLUMN=DUMMY,
BACKCOLOR=RGB(102 255 255),
COLOR=RGB(102 255 255),
WHEN= IFS_CUSTOMER_ID EQ '1005',
WIDTH=0.2,
$

8201

quote:

I want to be able to change the Customer_ID to the name rather than the ID

From where does the name come from ?
Same table as where ID is stored ?
If so add it into your WEEKENDS table and keep it all the way down into others tables and you will then be able to use it.
But could be a good idea to keep the ID

Just another alternative, as done in App Studio.. The Traffic Lights for the DATA can be done in InfoAssist. But the Traffic Lights for the FOOTING cannot be done in InfoAssist. Or can InfoAssist do this?

TABLE FILE GGSALES
SUM DOLLARS UNITS
BY REGION NOPRINT
FOOTING
"<0>Midwest<0> <0>Northeast<0> <0>Southeast<0> <0>West"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, BORDER=OFF, SIZE=10, JUSTIFY=CENTER,$
TYPE=TITLE,   BACKCOLOR=SILVER, COLOR=BLACK,  STYLE=BOLD+UNDERLINE,$
TYPE=FOOTING, BACKCOLOR=RED,    COLOR=YELLOW, OBJECT=TEXT, ITEM=1,$
TYPE=FOOTING, BACKCOLOR=GREEN,  COLOR=WHITE,  OBJECT=TEXT, ITEM=3,$
TYPE=FOOTING, BACKCOLOR=BLUE,   COLOR=WHITE,  OBJECT=TEXT, ITEM=5,$
TYPE=FOOTING, BACKCOLOR=BLACK,  COLOR=WHITE,  OBJECT=TEXT, ITEM=7,$
TYPE=DATA,    BACKCOLOR=RED,    COLOR=YELLOW, WHEN=REGION EQ 'Midwest',$
TYPE=DATA,    BACKCOLOR=GREEN,  COLOR=WHITE,  WHEN=REGION EQ 'Northeast',$
TYPE=DATA,    BACKCOLOR=BLUE,   COLOR=WHITE,  WHEN=REGION EQ 'Southeast',$
TYPE=DATA,    BACKCOLOR=BLACK,  COLOR=WHITE,  WHEN=REGION EQ 'West',$
ENDSTYLE
END
-RUN

REFERENCE: "I want to be able to change the Customer_ID to the name rather than the ID":
     Consider DECODE, hard-coded or from a file/table...

I wanted to be able to abbreviate the name which is why I asked if there was a way to decode it, but I just ended up using our table file to pull in the full name.
Thank you everyone for the help!