IBM Mainframe Forum

Posted: **Fri Mar 10, 2017 3:14 am**

Hi Team,

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)

Select all

|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|

 

Output report

Select all

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.

Posted: **Fri Mar 10, 2017 5:04 am**

Don't use SUM. The sample data you have shown is in sequence. You don't want to SORT millions of records just to be able to use SUM.

Use OUTFIL reporting procedures. There are good examples in DFSORT Getting Started, and the full details in the DFSORT Application Programming Guide.

Posted: **Fri Mar 10, 2017 9:51 am**

Hello,

You can also look at the tools provided by ICETOOL's DISPLAY operator. It comes with a good suite of commands which can be used to format and present data.

Posted: **Fri Mar 10, 2017 6:30 pm**

@BillyBoyo , The input file records will be in sequence order. Thanks Ai88 and billy. I will check on both OUTFIL as well ICETOOL.

Posted: **Fri Mar 10, 2017 11:46 pm**

Hi All,

I tried with DFSort and got the required output.

SORT FIELDS=(3,7,CH,A,64,01,CH,A)
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(64,1,TRAILER3=(3,8,64,1,X,COUNT=(M10,LENGTH=6)))

Select all

Is there any option to display the value 6003 for first record alone and rest should be filled with blanks. Also I struck with displaying sub-total and total count. Already I am using TRAILER3 in getting the field 4 (in output report). If I use TRAILER1 to display sub total or total, I am getting the different Value.

Please do advice how to proceed on this.

Posted: **Sat Mar 11, 2017 2:59 am**

*** Latest updates ***

I got the required report with final GRAND TOTAL. Still digging out to get the sub-total for every store. I am trying to use TRAILER2 to achieve it. But it is not working out.

Select all

  SORT FIELDS=(3,7,CH,A,64,01,CH,A)                        
  OUTFIL REMOVECC,NODETAIL,                                
  SECTIONS=(64,1,TRAILER3=(3,8,11:64,1,12:'|',             
                   X,COUNT=(M10,LENGTH=6))),               
  TRAILER1=(2/,6:'TOTAL COUNT OF THE FILE IS:', 2/,        
                 COUNT=(EDIT=(II,III,III,IIT),LENGTH=14))  
/*   

My output is

Select all

28|  Y|     42                            
1|  N|  30493                            
1|  Y|   4472                            
2|  N|  49183                            
2|  Y|  61542                            
3|  N|   1029                            
3|  Y|     26                            
4|  N|   8926                            
4|  Y|      8                            
8|  N|  10793                            
8|  Y|     77                            
9|  N|  18547                            
9|  Y|    410                            
11|  N|   4374                            
11|  Y|      5                            
12|  N|    474                            
12|  Y|    836                            
17|  N|   2585                            
17|  Y|     54                            
21|  N|   1965                            
21|  Y|      9                            
28|  N|   5498                            
28|  Y|     41                            
                                               
     TOTAL COUNT OF THE FILE IS:     6,384,656

But expected output is

Select all

6087|001|  N|  30493                            
    |001|  Y|   4472                            
    |002|  N|  49183                            
    |002|  Y|  61542                            
    |003|  N|   1029                            
    |003|  Y|     26                            
    |004|  N|   8926                            
    |004|  Y|      8                            
    |008|  N|  10793                            
    |008|  Y|     77                            
    |009|  N|  18547                            
    |009|  Y|    410                            
    |011|  N|   4374                            
    |011|  Y|      5                            
    |012|  N|    474                            
    |012|  Y|    836                            
    |017|  N|   2585                            
    |017|  Y|     54                            
    |021|  N|   1965                            
    |021|  Y|      9                            
    |028|  N|   5498                            
    |028|  Y|     41                            
    SUBTOTAL COUNT of 6087 IS :       201,347   
    
    
    TOTAL COUNT OF THE FILE IS:     6,384,656 
     

output Field#1 should display the value for very first record. Field#2 with three-byte length and filled with prefix zeros. Any help on this would be great. thanks.

Posted: **Mon Mar 13, 2017 10:00 pm**

Hi,

Any hint on the current issue. Appreciate your time and help.

Posted: **Mon Mar 13, 2017 10:53 pm**

Any hint on the current issue

DO NOT SOLICIT FOR REPLIES
people answer on their own time and free of charge
( ... when and if they feel like )

if You have time constraints a paid consultant will be happy ( for a proper fee )
to provide help according to Your time requirements

Posted: **Tue Mar 14, 2017 2:48 am**

Topic locked - posted on another forum.

IBM Mainframe Forum

Generating Report using ICEMAN !!!

Generating Report using ICEMAN !!!

Re: Generating Report using ICEMAN !!!

Re: Generating Report using ICEMAN !!!

Re: Generating Report using ICEMAN !!!

Re: Generating Report using ICEMAN !!!

Re: Generating Report using ICEMAN !!!

Re: Generating Report using DFSORT!!!

Re: Generating Report using ICEMAN !!!

Re: Generating Report using ICEMAN !!!