I would like to hear from experts how to achieve the below-listed report with formatting.
Input file will be like field 1 with store #, field 2 with dept, field 3 with FLAG [y/n] and fourth field with status (A-Active or I-inactive)
|6003| 1|N|A|
|6003| 1|N|A|
|6003| 1|Y|A|
|6003| 1|N|A|
|6003| 2|Y|A|
|6003| 2|N|A|
|6003| 2|Y|A|
|6003| 2|N|A|
|6003| 3|N|A|
|6003| 3|Y|A|
|6003| 3|N|A|
|6003| 4|Y|A|
|6003| 4|N|A|
|6003| 5|Y|A|
|6003| 5|N|A|
|6003| 6|Y|A|
|6003| 6|N|A|
|6003| 6|Y|A|
|6003| 8|Y|A|
|6003| 1|N|A|
|6003| 1|Y|A|
|6003| 1|N|A|
|6003| 2|Y|A|
|6003| 2|N|A|
|6003| 2|Y|A|
|6003| 2|N|A|
|6003| 3|N|A|
|6003| 3|Y|A|
|6003| 3|N|A|
|6003| 4|Y|A|
|6003| 4|N|A|
|6003| 5|Y|A|
|6003| 5|N|A|
|6003| 6|Y|A|
|6003| 6|N|A|
|6003| 6|Y|A|
|6003| 8|Y|A|
Output report
REPORT TOTAL REPORT PGM-NAME
RUN DATE - 01/13/17 Page 1
Field#1 Field#2 Field#3 Field#4 Field#5
6003 001 SUB-DIV1 Y 15
001 SUB-DIV1 N 1,058
002 SUB-DIV2 Y 2,526
002 SUB-DIV2 N 2,689
003 SUB-DIV3 Y 130
003 SUB-DIV3 N 1,283
004 SUB-DIV4 N 2,652
005 SUB-DIV5 N 1,920
006 SUB-DIV6 Y 269
006 SUB-DIV6 N 3,740
008 SUB-DIV8 N 3,061
TOTAL STORE 6003 INBOUND ITEMS 7,789
TOTAL STORE 6003 OUTBOUND ITEMS 1,190
TOTAL STORE 6003 ITEMS 8,979
...
...
...
.
INVENTORY GRAND TOTAL = 38,415,086
INVENTORY INBOUND GRAND TOTAL = 12,853,600
INVENTORY OUTBOUND GRAND TOTAL = 25,561,486
Input file contains millions of records in which field 1 hold several other store # records. In order to achieve the desired result, I will use DFSORT "SUM" (third field from the input file) for every dept under store # to get the records counts based on FLAG [Y/N]. Also how to display the store # for the very first record in the report. How to generate that report by grouping the field # 1(store), field #2 (dept) for every flag value (field #3).
Appreciate any hints. Thanks for your time.
