May 13, 2019, 11:46 AM
Jen MoserI have this report that looks like a grid (or excel spreadsheet) that is sorted by Across Month, then Across each Sunday in the week. I need to put a bold line separating the months.
Jan | Feb | Mar
06 13 20 27 | 03 10 17 24 | 03 10 17 24 31 | |
| |
Below is my code:
SET ASNAMES = ON
SET HOLDATTR = ON
SET NODATA=' '
-*SET SQUEEZE=ON
-*SET SPACES = 0
-DEFAULT &CURYEAR= 2019;
-DEFAULT &BUSINESS_SEGMENT = '''Contract Services''';
-DEFAULT &SUB_BUSINESS_SEGMENT = '''Fleet''';
-DEFAULT &DIVISION = '''Ditchers''';
-DEFAULT &SUB_DIVISION = '''Ditchers''';
-DEFAULT &COST_CENTER_GROUP = '''Ditchers''';
-DEFAULT &CODE_B = 'FOC_NONE';
-DEFAULT &COST_CENTER_DESC = 'FOC_NONE';
-DEFAULT &CUSTOMER_ID = 'FOC_NONE';
-DEFAULT &CUSTOMER_NAME = 'FOC_NONE';
-DEFAULT &WFFMT = 'HTML';
-DEFAULT &SUMMARY_LEVEL = 'CODE_B';
-DEFAULT &BUDGET_TYPE = 'Working';
-*-SET &OUTPUT_TYPE = 'FMT_HOLD';
-DEFAULT &SPCS = '';
-SET &SPCS = &SPCS | HEXBYT(160, 'A1');
-SET &HEADING_INFO = &CURYEAR | ' Utilization Schedule';
-SET &FOOTER_INFO = IFS_CUSTOMER_ID;
-SET &DATEM = &DATEM;
-SET &TODAY= &YYMD;
-SET &CURRENT_WK = DATECVT((DATEMOV((DATECVT(&TODAY,'I8YYMD','YYMD')),'BOW')),'YYMD','I8YYMD');
-SET &CURRENT_WEEK = AYMD(&CURRENT_WK,-1,'I8YYMD');
-SET &CURRENT_DAY=EDIT(EDIT(&CURRENT_WEEK,'$$$$$$99'));
DEFINE FILE DW_CODE_B_V2
VALID_YEAR/YYMD = VALID_UNTIL;
VALID_YR/YY = VALID_YEAR;
DISPO_YR/YYMD = DISPOSITION_DATE;
DISPO_YEAR/YY = IF DISPO_YR EQ '' THEN '9999' ELSE DISPO_YR;
END
TABLE FILE DW_CODE_B_V2
SUM
DW_CODE_B_V2.DW_CODE_B_V2.CODE_B
BY DIVISION
BY CODE_B
BY COST_CENTER_DESC
WHERE COST_CENTER_DESC NOT LIKE '%DO NOT USE%' OR 'RG331' OR 'RG318' OR 'RG311' OR 'RG306' OR 'J10' OR 'J11' OR 'J8' OR 'RW12' OR 'LR502' OR 'LRG14' OR 'CN-KBC750' OR 'CRG01' OR 'AS%' OR 'KM%' OR 'LRT2' OR 'RG310' OR 'RG319' OR 'RGS11' OR 'RV10%' OR 'RV20' OR 'SAR-RGI7' OR 'SBC34' OR 'SBC4' OR 'TLU01' OR 'UC2400';
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_YEAR GE &CURYEAR;
WHERE DISPO_YEAR 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
-*DEFINE FILE CODEBDAT
-*WEEK_BEG_DATE/YYMD = DATEADD(WEEK_END_DATE_YEAR_D, 'D', -5);
-*END
TABLE FILE CODEBDAT
BY J0.CODE_B_DATES.WEEK_END_DATE
-* J0.CODE_B_DATES.WEEK_BEG_DATE
BY J0.CODE_B_DATES.DIVISION
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
-*DEFINE FILE CODEBDAT
-*WEEK_BEG/YYMD = DATEADD(DATEMOV(DW_BUDGET_DAILY_SHIFTS.BUDGETDAILYSHIFTS.DATE_TIME, 'BOW'), 'D', 0);
-*END
TABLE FILE CODEBDAT
BY IFS_CUSTOMER_ID
-*BY WEEK_BEG
BY DIVISION
BY CODE_B
BY WEEK_END
WHERE ACCOUNT_YEAR EQ &CURYEAR;
WHERE BUDGET_TYPE EQ '&BUDGET_TYPE';
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
DEFINE FILE WKNDDAYS
WEEK_ENDING/YYMD = WKNDDAYS.WKNDDAYS.WEEK_END;
END
TABLE FILE WKNDDAYS
SUM
J1.WEEKENDS.IFS_CUSTOMER_ID
BY DIVISION
BY CODE_B
BY COST_CENTER_DESC
BY WEEK_ENDING
-*BY WKNDDAYS.WKNDDAYS.WEEK_END
-*BY WEEK_BEG_DATE
-*WHERE BUDGET_TYPE EQ '&BUDGET_TYPE';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS TEMPDATA
END
DEFINE FILE TEMPDATA
WK_BEG_DATE/YYMD = DATEADD(WEEK_END,'D',-6);
YR/YY=WK_BEG_DATE;
YEAR/A4=EDIT(YR);
MTH/M=WK_BEG_DATE;
MONTH/I2=EDIT(MTH);
DAY/D=WK_BEG_DATE;
DAY1/A2=EDIT(DAY);
MONTHDAY/A7=MONTH |'/'| DAY1;
MONTH_NAME/A12 = DECODE MTH ('01' JAN '02' FEB '03' MAR '04' APR '05' MAY '06' JUN '07' JUL '08' AUG '09' SEP '10' OCT '11' NOV '12' DEC );
SPACES/A30=' ';
COST_CENTER/A250V=IF COST_CENTER_DESC LIKE 'RV10 and Truck%' THEN 'RV10/TruckVac' ELSE COST_CENTER_DESC;
COSTCENTER/A255 = COST_CENTER | '&SPCS';
DUMMY/A5='DUMMY';
CURRENT_WEEK/YYMD=&CURRENT_WEEK;
END
-*SET COMPOUND = OPEN
TABLE FILE TEMPDATA
SUM
TEMPDATA.TEMPDATA.IFS_CUSTOMER_ID
BY TEMPDATA.TEMPDATA.DIVISION NOPRINT
BY TEMPDATA.TEMPDATA.COSTCENTER AS ''
ACROSS YEAR NOPRINT
ACROSS MTH NOPRINT
ACROSS MONTH_NAME AS ''
ACROSS WK_BEG_DATE NOPRINT
-*ACROSS MONTH_NAME
ACROSS CURRENT_WEEK NOPRINT
ACROSS TEMPDATA.TEMPDATA.YEAR NOPRINT
ACROSS TEMPDATA.TEMPDATA.MTH NOPRINT
ACROSS TEMPDATA.TEMPDATA.DAY1 NOPRINT
ACROSS TEMPDATA.TEMPDATA.DAY1 AS ''
-*WHERE WK_END_DATE EQ CURRENT_WEEK;
ON TABLE HEADING
"&HEADING_INFO"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET FORMULTIPLE ON
ON TABLE SET AUTOFIT ON
ON TABLE NOTOTAL
-*ON TABLE SET ACROSSPANEL OFF
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=9,
COLOR='BLACK',
STYLE=NORMAL,
PAGESIZE='11 X 17',
ORIENTATION=LANDSCAPE,
LEFTMARGIN=0.50,
RIGHTMARGIN=0.5,
TOPMARGIN=0.055556,
BOTTOMMARGIN=0.055556,
GRID=ON,
LEFTGAP=0,
RIGHTGAP=0,
$
DEFMACRO=COND0001,
MACTYPE=RULE,
WHEN=WK_BEG_DATE EQ CURRENT_WEEK,
$
-* DEFMACRO=COND0002,
-* MACTYPE=RULE,
-* WHEN=DAY1 EQ &CURRENT_DAY,
-*$
TYPE=HEADING,
JUSTIFY=LEFT,
SIZE=20,
$
-*TYPE=FOOTING,
-* LINE=1,
-* OBJECT=FIELD,
-* ITEM=1,
-* BACKCOLOR=RGB(102 255 51),
-* COLOR=RGB(102 255 51),
-* WIDTH=.2,
-* SIZE=2,
-*$
-*TYPE=FOOTING,
-* LINE=1,
-* OBJECT=TEXT,
-* ITEM=1,
-* SIZE=7,
-*$
-*TYPE=FOOTING,
-* LINE=2,
-* OBJECT=FIELD,
-* ITEM=1,
-* BACKCOLOR=RGB(255 192 0),
-* COLOR=RGB(255 192 0),
-* WIDTH=.2,
-* SIZE=2,
-*$
-*TYPE=FOOTING,
-* LINE=2,
-* OBJECT=TEXT,
-* ITEM=1,
-* SIZE=7,
-*$
TYPE=DATA,
BACKCOLOR='WHITE',
JUSTIFY=LEFT,
$
TYPE=ACROSSVALUE,
JUSTIFY=CENTER,
$
-*TYPE=DATA,
-* ACROSSCOLUMN=IFS_CUSTOMER_ID,
-* BORDER-LEFT=MEDIUM,
-* BORDER-LEFT-COLOR='RED',
-* WHEN=MTH EQ &DATEM,
-*$
TYPE=ACROSSVALUE,
ACROSS=6,
COLOR='RED',
WHEN=WK_BEG_DATE EQ CURRENT_WEEK,
$
TYPE=ACROSSVALUE,
ACROSS=9,
COLOR='RED',
WHEN=WK_BEG_DATE EQ CURRENT_WEEK,
$
TYPE=DATA,
ACROSSCOLUMN=IFS_CUSTOMER_ID,
BORDER-LEFT=HEAVY,
BORDER-LEFT-COLOR='RED',
BORDER-LEFT-STYLE=SOLID,
BORDER-RIGHT=HEAVY,
BORDER-RIGHT-STYLE=SOLID,
BORDER-RIGHT-COLOR='RED',
WHEN=WK_BEG_DATE EQ CURRENT_WEEK,
$
TYPE=DATA,
ACROSSCOLUMN=IFS_CUSTOMER_ID,
BORDER-LEFT=HEAVY,
BORDER-LEFT-COLOR='BLACK',
BORDER-LEFT-STYLE=SOLID,
BORDER-RIGHT=HEAVY,
BORDER-RIGHT-STYLE=SOLID,
BORDER-RIGHT-COLOR='BLACK',
WHEN=DAY1 LE 7,
$
-*TYPE=DATA,
-*-* ACROSS=3,
-* BORDER-LEFT=HEAVY,
-* BORDER-LEFT-COLOR='BLACK',
-* BORDER-LEFT-STYLE=SOLID,
-* WHEN=MONTH_DIFF NE '1',
-*$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(242 220 219),
WHEN=MTH EQ 01,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 01,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(216 228 188),
WHEN=MTH EQ 02,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 02,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(228 223 236),
WHEN=MTH EQ 03,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 03,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(255 255 204),
WHEN=MTH EQ 04,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 04,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(252 213 180),
WHEN=MTH EQ 05,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 05,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(218 238 243),
WHEN=MTH EQ 06,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 06,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(242 220 219),
WHEN=MTH EQ 07,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 07,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(216 228 188),
WHEN=MTH EQ 08,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 08,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(228 223 236),
WHEN=MTH EQ 09,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 09,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(255 255 204),
WHEN=MTH EQ 10,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 10,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(252 213 180),
WHEN=MTH EQ 11,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 11,
$
TYPE=ACROSSVALUE,
ACROSS=3,
BACKCOLOR=RGB(218 238 243),
WHEN=MTH EQ 12,
$
TYPE=ACROSSVALUE,
ACROSS=9,
BACKCOLOR='WHITE',
WHEN=MTH EQ 12,
$
TYPE=DATA,
ACROSSCOLUMN=IFS_CUSTOMER_ID,
COLOR= 'WHITE',
BACKCOLOR='WHITE',
WHEN=IFS_CUSTOMER_ID EQ ' ',
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
$
TYPE=DATA,
ACROSSCOLUMN=IFS_CUSTOMER_ID,
COLOR= RGB(102 255 51),
BACKCOLOR=RGB(102 255 51),
WHEN=IFS_CUSTOMER_ID EQ '1000',
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
-* SIZE=20,
$
May 15, 2019, 01:43 PM
Edward WolfgramI know the topic is marked solved, but it seemed like an interesting problem to demonstrate using DECODE files to map input dates to arbitrary data, such as the previous Sunday week.
-* -------------------------------------------------------------------
-* Example: creating a DECODE file for fast date lookup into arbitrary data
-* -------------------------------------------------------------------
-*
-* The idea here is to create a Focus Decode file that can be used to
-* lookup information for particular dates, such as week month and day, week
-* number, holiday or business day, etc. Here is an example of some code/decode
-* entries in the decode file for week month and day, where the input date
-* is mapped to the *previous* Sunday, whose year, month, and day
-* represents the week used for the date:
-*
-* 20190501,2019040428
-* 20190502,2019040428
-* 20190503,2019040428
-* 20190504,2019040428
-* 20190505,2019050505
-* 20190506,2019050505
-* 20190507,2019050505
-* 20190508,2019050505
-* 20190509,2019050505
-* 20190510,2019050505
-* 20190511,2019050505
-* 20190512,2019050512
-* 20190513,2019050512
-* 20190514,2019050512
-* 20190515,2019050512
-*
-* We see that for May 1, 2019, the previous Sunday was April 28, 2019, etc.
-* With this decode file, we can use a DECODE Focus subcommand to
-* decode an I8YYMD olddate to find the WEEKSUN A8 value: e.g.
-*
-* WEEKSUN/A8 = DECODE INDAY (MYDCOD ELSE 'UNKNOWN ');
-*
-* From the WEEKSUN field, we can easily get the start week year, month, or
-* day using the EDIT subcommand: e.g.
-*
-* WEEKMON/A2 = EDIT(WEEKSUN,'$$$$$$99');
-* -------------------------------------------------------------------
-* First, we create a very simple Focus file that only contains a key
-* (ORDERKEY) that is simply an ascending integer starting from 1,
-* along with random IVAL and AVAL fields just for fun. Take the following
-* master and put it into MYSMALL.MAS:
-GOTO HERE
SUFFIX=FOC
SEGNAME=ONLY,SEGTYPE=S1,$
FIELD=ORDERKEY,,I6,$
FIELD=IVAL,,I2,$
FIELD=AVAL,,A2,$
END
-HERE
-* Now create the MYSMALL file. Here, we populate the file with 20,000
-* entries going from 1 to 20,000:
CREATE FILE MYSMALL
-RUN
MODIFY FILE MYSMALL
COMPUTE
A1DUM/A1 = ;
FIXFORM 20000(A1DUM/1 X-1)
COMPUTE
ORDERKEY = ORDERKEY + 1;
SEED = 1;
IVAL = PRDUNI(SEED,'D2') * 100;
AVAL = EDIT(IVAL) ;
MATCH ORDERKEY
ON MATCH REJECT
ON NOMATCH INCLUDE
LOG DUPL MSG OFF
DATA
1
END
-RUN
-* Now that we have the MYSMALL file, we will use it to create our DECODE file
-* as described above. In the example just below, we start with the date
-* Jan 1, 2019, and create the MYDCOD DECODE file for 200 days after the
-* start date:
DEFINE FILE MYSMALL
TODAY/I8YYMD = 20190101 ;
TOD/YYMD = TODAY ;
CURRDAY/YYMD = TOD + ORDERKEY - 1;
ICURR/I8YYMD = CURRDAY ;
MYDAY/I6 = CURRDAY - (INT(CURRDAY/7)*7) ;
MYDAYB/I6 = IF MYDAY EQ 6 THEN 0 ELSE MYDAY + 1;
MYCSUN/YYMD = IF(MYDAYB NE 0)THEN CURRDAY - MYDAYB ELSE CURRDAY;
IMYCSUN/I8YYMD = MYCSUN ;
AMYC/A8 = EDIT(IMYCSUN) ;
DCOD/A10 = EDIT(AMYC,'999999') | EDIT(AMYC,'$$$$99') | EDIT(AMYC,'$$$$$$99');
LINE/A19 = EDIT(ICURR) | ',' | DCOD ;
END
-RUN
TABLE FILE MYSMALL
PRINT LINE
ON TABLE SAVE AS MYDCOD
IF RECORDLIMIT EQ 200
END
-RUN
-* And that's it! There is now a DECODE file with the name MYDCOD
-* that can be used by *any* program to quickly find the prevous
-* Sunday week date based on an input I8YYMD date. In the example
-* below, we re-use the MYSMALL file we just created only to provide
-* some input data where we can call DECODE with our new MYDCoD file.
-* For the MYSMALL Focus file, we associate ORDERKEY with consecutive
-* days starting with Jan 1, 2019, (e.g. oRDERKEY=1 turns into
-* I8YYMD 20190101), and use IVAL as the fake data for each day.
SET PANEL=80
DEFINE FILE MYSMALL
TODAY/I8YYMD = 20190101 ;
TOD/YYMD = TODAY ;
CURRDAY/YYMD = TOD + ORDERKEY - 1;
ICURR/I8YYMD = CURRDAY ;
MYDCOD/A10 = DECODE ICURR (MYDCOD ELSE 'UNKNOWN');
MYD/D3 = IVAL ;
ISYMON/A6 = EDIT(MYDCOD,'999999') ;
ISMON/A2 = EDIT(MYDCOD,'$$$$$$99') ;
ISDAY/A2 = EDIT(MYDCOD,'$$$$$$$$99') ;
END
-RUN
TABLE FILE MYSMALL
SUM MYD ACROSS ISYMON AS '' ACROSS ISMON AS '' ACROSS ISDAY AS ''
IF RECORDLIMIT EQ 200
END
-RUN
-EXIT
PAGE 1.1
201812 201901 201902 201903
12 01 02 03
30 06 13 20 27 03 10 17 24 03 10
------------------------------------------------------------------------------
276 327 303 459 389 358 355 308 284 229 328 2
PAGE 1.2
201904 201905 201
04 05 06
17 24 31 07 14 21 28 05 12 19 26 02
-------------------------------------------------------------------------------
96 256 396 302 316 355 413 285 250 243 243 326
PAGE 1.3
906 201907
07
09 16 23 30 07 14
----------------------------------------------
300 564 225 197 331 171