Creating 2 report from a PS file



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Creating 2 report from a PS file

Postby sudijana » Thu May 14, 2009 8:12 pm

Hi,

I have a PS file of LRECL 80 which looks like:

003,0007567,000001074620.09,03022009
003,0007252,000001403673.91,03032009
003,0005997,000000954221.77,03042009
003,0005290,000000846802.86,03052009
003,0005849,000000839802.27,03062009
004,0023819,000006339496.60,03022009
004,0017969,000005548016.89,03032009
004,0019176,000005684130.41,03042009
004,0017012,000004729511.17,03052009
004,0019796,000006463564.86,03062009
008,0076990,000014350619.12,03022009
008,0058747,000011312074.57,03032009
008,0053474,000009838281.04,03042009
008,0050253,000009910061.53,03052009

Where it indicates Division,Invoice Lines,Invoice amount,Invoice date

Now from the above file, 2 reports needs to be created which looks like:

Report1 : Sales Invoice Records by Day(Invoice lines/Day/Division)
Divisions InvDay1 InvDay2 InvDay3 InvDay4 InvDay5 InvDay6 Total
003 100 100 100 100 100 100 600
004 200 200 200 200 200 200 1200
008 300 300 300 300 300 300 1800
Total 600 600 600 600 600 600 3600

Report1 : Sales $ by Day(Invoice amount/Day/Division)
Divisions InvDay1 InvDay2 InvDay3 InvDay4 InvDay5 InvDay6 Total
003 $1000 $1000 $1000 $1000 $1000 $1000 $6000
004 $2000 $2000 $2000 $2000 $2000 $2000 $12000
008 $3000 $3000 $3000 $3000 $3000 $3000 $18000
Total $6000 $6000 $6000 $6000 $6000 $6000 $36000


Can I do it by using SORT/ICETOOL??

Please help.
sudijana
 
Posts: 9
Joined: Thu May 14, 2009 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Creating 2 report from a PS file

Postby Frank Yaeger » Thu May 14, 2009 9:06 pm

Your description is quite confusing. You're going to have to do a better job of explaining what you want to do before anyone can help you.

I don't see how your output reports relate to your input values. For example, where does '100' for 003 in the first line come from?

Please show a better example of input and output and explain what you are trying to do in terms of the example.

You show InvDay1 to InvDay2 for both reports. Will you always have a maximum of 6 days? If not what is the maximum number of days you can have.

Does '03022009' relate to InvDay2 in some way or what?
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Creating 2 report from a PS file

Postby sudijana » Fri May 15, 2009 11:27 am

Input File:

003,0007567,000001074620.09,03022009
003,0007252,000001403673.91,03032009
003,0005997,000000954221.77,03042009
003,0005290,000000846802.86,03052009
003,0005849,000000839802.27,03062009
004,0023819,000006339496.60,03022009
004,0017969,000005548016.89,03032009
004,0019176,000005684130.41,03042009
004,0017012,000004729511.17,03052009
004,0019796,000006463564.86,03062009
008,0076990,000014350619.12,03022009
008,0058747,000011312074.57,03032009
008,0053474,000009838281.04,03042009
008,0050253,000009910061.53,03052009

Format:
01-03: Div
05-11: Records
13-27: Amount
29-36: Date

Now Two output files needs to be created.

Report1 : Sales Invoice Records by Day(Records/Div/Day)
Div 03022009 03032009 03042009 03052009 03062009 03072009 Total
003 7567 7252 5997 5290 5849 0 31955
004 23819 17969 19176 17012 19796 0 97772
008 76990 58747 53474 50253 0 0 239464
Total 108376 83968 78647 72555 25645 0 369191

Report2: Sales $ by Day(Amount/Div/Day)
Div 03022009 03032009 03042009 03052009 03062009 03072009 Total
003 1074620 1403674 954222 846803 839802 0 5119121
004 6339497 5548017 5684130 4729511 6463565 0 85764720
008 14350619 11312075 9838281 9910062 0 0 45411037
Total 21764736 18263766 16476636 15486376 7303367 0 136294878

The amount is rounded off here in the report

I know the report don't look aligned so please check the link http://picasaweb.google.com/sudiptojana ... 7850326434
sudijana
 
Posts: 9
Joined: Thu May 14, 2009 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Creating 2 report from a PS file

Postby sudijana » Fri May 15, 2009 11:30 am

Max no of days can be 6 as Sunday is holiday so not required.

There can be n no of divisions.
sudijana
 
Posts: 9
Joined: Thu May 14, 2009 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Creating 2 report from a PS file

Postby skolusu » Sat May 16, 2009 2:23 am

sudjana,

The following DFSORT JCL will give you the desired results

//STEP0100 EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//SORTIN   DD *                                     
003,0007567,000001074620.09,03022009                 
003,0007252,000001403673.91,03032009                 
003,0005997,000000954221.77,03042009                 
003,0005290,000000846802.86,03052009                 
003,0005849,000000839802.27,03062009                 
004,0023819,000006339496.60,03022009                 
004,0017969,000005548016.89,03032009                 
004,0019176,000005684130.41,03042009                 
004,0017012,000004729511.17,03052009                 
004,0019796,000006463564.86,03062009                 
008,0076990,000014350619.12,03022009                 
008,0058747,000011312074.57,03032009                 
008,0053474,000009838281.04,03042009                 
008,0050253,000009910061.53,03052009                 
//OUT1     DD SYSOUT=*                               
//OUT2     DD SYSOUT=*                               
//SYSIN    DD *                                                 
  INREC IFTHEN=(WHEN=INIT,                                       
  BUILD=(162C'0',1,80,243:SEQNUM,1,ZD,RESTART=(1,3))),           
  IFTHEN=(WHEN=(243,1,ZD,EQ,1),                                 
  OVERLAY=(001:167,7,043:175,12,115:191,8)),                     
  IFTHEN=(WHEN=(243,1,ZD,EQ,2),                                 
  OVERLAY=(008:167,7,055:175,12,123:191,8)),                     
  IFTHEN=(WHEN=(243,1,ZD,EQ,3),                                 
  OVERLAY=(015:167,7,067:175,12,131:191,8)),                     
  IFTHEN=(WHEN=(243,1,ZD,EQ,4),                                 
  OVERLAY=(022:167,7,079:175,12,139:191,8)),                     
  IFTHEN=(WHEN=(243,1,ZD,EQ,5),                                 
  OVERLAY=(029:167,7,091:175,12,147:191,8)),                     
  IFTHEN=(WHEN=(243,1,ZD,EQ,6),                                 
  OVERLAY=(036:167,7,103:175,12,155:191,8))                     
                                                                 
  SORT FIELDS=(163,3,CH,A),EQUALS                               
                                                                 
  SUM FIELDS=(001,07,ZD,008,07,ZD,015,07,ZD,022,07,ZD,           
              029,07,ZD,036,07,ZD,043,12,ZD,055,12,ZD,           
              067,12,ZD,079,12,ZD,091,12,ZD,103,12,ZD,           
              115,08,ZD,123,08,ZD,131,08,ZD,139,08,ZD,           
              147,08,ZD,155,08,ZD,167,7,ZD,175,12,ZD)           
                                                                 
  OUTREC IFTHEN=(WHEN=(115,8,ZD,EQ,0),OVERLAY=(115:8X),HIT=NEXT),
         IFTHEN=(WHEN=(123,8,ZD,EQ,0),OVERLAY=(123:8X),HIT=NEXT),
         IFTHEN=(WHEN=(131,8,ZD,EQ,0),OVERLAY=(131:8X),HIT=NEXT),
         IFTHEN=(WHEN=(139,8,ZD,EQ,0),OVERLAY=(139:8X),HIT=NEXT),
         IFTHEN=(WHEN=(147,8,ZD,EQ,0),OVERLAY=(147:8X),HIT=NEXT),
         IFTHEN=(WHEN=(155,8,ZD,EQ,0),OVERLAY=(155:8X)) 
  OUTFIL FNAMES=OUT1,REMOVECC,                                       
  BUILD=(163,3,5X,                                                   
         001,07,ZD,M10,LENGTH=7,2X,08,07,ZD,M10,LENGTH=7,2X,         
         015,07,ZD,M10,LENGTH=7,2X,22,07,ZD,M10,LENGTH=7,2X,         
         029,07,ZD,M10,LENGTH=7,2X,36,07,ZD,M10,LENGTH=7,2X,         
         167,07,ZD,M10,LENGTH=7,80:X),                               
  HEADER1=(10:' SALES INVOICE RECORDS BY DAY(RECORDS/DIV/DAY)',/,     
           10:' *********************************************',/,/,   
           01:'DIV',4X,115,8,X,123,8,X,131,8,X,139,8,X,               
              147,8,X,155,8,4X,'TOTAL',/,                             
           01:3'*',4X,8'*',X,8'*',X,8'*',X,8'*',X,8'*',X,8'*',X,8'*'),
  TRAILER1=(/,'TOTAL :',TOT=(001,07,ZD,M10,LENGTH=8),X,               
                        TOT=(008,07,ZD,M10,LENGTH=8),X,               
                        TOT=(015,07,ZD,M10,LENGTH=8),X,               
                        TOT=(022,07,ZD,M10,LENGTH=8),X,               
                        TOT=(029,07,ZD,M10,LENGTH=8),X,               
                        TOT=(036,07,ZD,M10,LENGTH=8),X,               
                        TOT=(167,07,ZD,M10,LENGTH=8))                 
                                                                     
  OUTFIL FNAMES=OUT2,REMOVECC,                                       
  BUILD=(163,3,4X,                                                   
         043,12,ZD,M10,LENGTH=12,X,055,12,ZD,M10,LENGTH=12,X,         
         067,12,ZD,M10,LENGTH=12,X,079,12,ZD,M10,LENGTH=12,X,         
         091,12,ZD,M10,LENGTH=12,X,103,12,ZD,M10,LENGTH=12,X,         
         175,12,ZD,M10,LENGTH=12,104:X),                             
  HEADER1=(30:' SALES $ BY DAY(AMOUNT/DIV/DAY)',/,                   
           30:' ******************************',/,/,                 
           01:'DIV',8X,115,8,5X,123,8,5X,131,8,5X,139,8,5X,           
               147,8,5X,155,8,8X,'TOTAL',/,                           
           01:3'*',8X,8'*',5X,8'*',5X,8'*',5X,8'*',5X,               
              8'*',5X,8'*',5X,8'*'),                                 
  TRAILER1=(/,'TOTAL :',TOT=(043,12,ZD,M10,LENGTH=12),X,             
                        TOT=(055,12,ZD,M10,LENGTH=12),X,             
                        TOT=(067,12,ZD,M10,LENGTH=12),X,             
                        TOT=(079,12,ZD,M10,LENGTH=12),X,             
                        TOT=(091,12,ZD,M10,LENGTH=12),X,             
                        TOT=(103,12,ZD,M10,LENGTH=12),X,             
                        TOT=(175,12,ZD,M10,LENGTH=12))               
/*


The out1 report is as follows

          SALES INVOICE RECORDS BY DAY(RECORDS/DIV/DAY)               
          *********************************************               
                                                                     
DIV    03022009 03032009 03042009 03052009 03062009             TOTAL
***    ******** ******** ******** ******** ******** ******** ********
003        7567     7252     5997     5290     5849        0    31955
004       23819    17969    19176    17012    19796        0    97772
008       76990    58747    53474    50253        0        0   239464
                                                                     
TOTAL :  108376    83968    78647    72555    25645        0   369191



                               SALES $ BY DAY(AMOUNT/DIV/DAY)
                               ******************************

DIV        03022009     03032009     03042009     03052009     03062009                     TOTAL
****       ********     ********     ********     ********     ********     ********     ********
003         1074620      1403673       954221       846802       839802            0      5119118
004         6339496      5548016      5684130      4729511      6463564            0     28764717
008        14350619     11312074      9838281      9910061            0            0     45411035

TOTAL :    21764735     18263763     16476632     15486374      7303366            0     79294870
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Creating 2 report from a PS file

Postby sudijana » Sat May 16, 2009 4:05 am

Thanks skolusu for the response. But I am getting SOC7 error :cry: . The error message is shown below.

0 BLOCKSET SORT TECHNIQUE SELECTED
0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 15:07 ON FRI MAY 15, 20
INREC IFTHEN=(WHEN=INIT,
BUILD=(162C'0',1,80,243:SEQNUM,1,ZD,RESTART=(1,3))),
IFTHEN=(WHEN=(243,1,ZD,EQ,1),
OVERLAY=(001:167,7,043:175,12,115:191,8)),
IFTHEN=(WHEN=(243,1,ZD,EQ,2),
OVERLAY=(008:167,7,055:175,12,123:191,8)),
IFTHEN=(WHEN=(243,1,ZD,EQ,3),
OVERLAY=(015:167,7,067:175,12,131:191,8)),
IFTHEN=(WHEN=(243,1,ZD,EQ,4),
OVERLAY=(022:167,7,079:175,12,139:191,8)),
IFTHEN=(WHEN=(243,1,ZD,EQ,5),
OVERLAY=(029:167,7,091:175,12,147:191,8)),
IFTHEN=(WHEN=(243,1,ZD,EQ,6),
OVERLAY=(036:167,7,103:175,12,155:191,8))

SORT FIELDS=(163,3,CH,A),EQUALS
SUM FIELDS=(001,07,ZD,008,07,ZD,015,07,ZD,022,07,ZD,
029,07,ZD,036,07,ZD,043,12,ZD,055,12,ZD,
067,12,ZD,079,12,ZD,091,12,ZD,103,12,ZD,
115,08,ZD,123,08,ZD,131,08,ZD,139,08,ZD,
147,08,ZD,155,08,ZD,167,7,ZD,175,12,ZD)

OUTREC IFTHEN=(WHEN=(115,8,ZD,EQ,0),OVERLAY=(115:8X),HIT=NEXT),
IFTHEN=(WHEN=(123,8,ZD,EQ,0),OVERLAY=(123:8X),HIT=NEXT),
IFTHEN=(WHEN=(131,8,ZD,EQ,0),OVERLAY=(131:8X),HIT=NEXT),
IFTHEN=(WHEN=(139,8,ZD,EQ,0),OVERLAY=(139:8X),HIT=NEXT),
IFTHEN=(WHEN=(147,8,ZD,EQ,0),OVERLAY=(147:8X),HIT=NEXT),
IFTHEN=(WHEN=(155,8,ZD,EQ,0),OVERLAY=(155:8X))
OUTFIL FNAMES=OUT1,REMOVECC,
BUILD=(163,3,5X,
001,07,ZD,M10,LENGTH=7,2X,08,07,ZD,M10,LENGTH=7,2X,
015,07,ZD,M10,LENGTH=7,2X,22,07,ZD,M10,LENGTH=7,2X,
015,07,ZD,M10,LENGTH=7,2X,22,07,ZD,M10,LENGTH=7,2X,
029,07,ZD,M10,LENGTH=7,2X,36,07,ZD,M10,LENGTH=7,2X,
167,07,ZD,M10,LENGTH=7,80:X),
HEADER1=(10:' SALES INVOICE RECORDS BY DAY(RECORDS/DIV/DAY)',/,
10:' *********************************************',/,/,
01:'DIV',4X,115,8,X,123,8,X,131,8,X,139,8,X,
147,8,X,155,8,4X,'TOTAL',/,
01:3'*',4X,8'*',X,8'*',X,8'*',X,8'*',X,8'*',X,8'*',X,8'*'),
TRAILER1=(/,'TOTAL :',TOT=(001,07,ZD,M10,LENGTH=8),X,
TOT=(008,07,ZD,M10,LENGTH=8),X,
TOT=(015,07,ZD,M10,LENGTH=8),X,
TOT=(022,07,ZD,M10,LENGTH=8),X,
TOT=(029,07,ZD,M10,LENGTH=8),X,
TOT=(036,07,ZD,M10,LENGTH=8),X,
TOT=(167,07,ZD,M10,LENGTH=8))

OUTFIL FNAMES=OUT2,REMOVECC,
BUILD=(163,3,4X,
043,12,ZD,M10,LENGTH=12,X,055,12,ZD,M10,LENGTH=12,X,
067,12,ZD,M10,LENGTH=12,X,079,12,ZD,M10,LENGTH=12,X,
091,12,ZD,M10,LENGTH=12,X,103,12,ZD,M10,LENGTH=12,X,
175,12,ZD,M10,LENGTH=12,104:X),
HEADER1=(30:' SALES $ BY DAY(AMOUNT/DIV/DAY)',/,
30:' ******************************',/,/,
01:'DIV',8X,115,8,5X,123,8,5X,131,8,5X,139,8,5X,
147,8,5X,155,8,8X,'TOTAL',/,
01:3'*',8X,8'*',5X,8'*',5X,8'*',5X,8'*',5X,
8'*',5X,8'*',5X,8'*'),
TRAILER1=(/,'TOTAL :',TOT=(043,12,ZD,M10,LENGTH=12),X,
TOT=(055,12,ZD,M10,LENGTH=12),X,
TOT=(067,12,ZD,M10,LENGTH=12),X,
TOT=(079,12,ZD,M10,LENGTH=12),X,
TOT=(091,12,ZD,M10,LENGTH=12),X,
TOT=(103,12,ZD,M10,LENGTH=12),X,
TOT=(175,12,ZD,M10,LENGTH=12))
E RECORD TYPE IS F - DATA STARTS IN POSITION 1
0 C5-K26318 C6-K90007 C7-K90000 C8-K23476 E9-K90007 C9-BASE E5-K31980 E6-K1818
0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED
1 AB3850SJ.SORT0010. , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
0 MAIN STORAGE = (MAX,6291456,6266880)
0 MAIN STORAGE ABOVE 16MB = (6209520,6209520)
0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=Y,LIST=Y,ERET=ABEND,MSGDDN=SRTMSG
0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=(SYSDA ,004),ABCODE=MSG
0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,STIMER=Y,COBEXIT=COB2
0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0
0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=2097152,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
0 OPTIONS: NULLOUT=RC0
0 BSAM ACCESS METHOD USED FOR SORTIN
0 DC 500000 TC 0 CS DSVNN KSZ 7 VSZ 7
0 FSZ=6250 RC IGN=0 E AVG=248 0 WSP=2013 C DYN=0 0
2 DE-K24705 D5-K24705 DA-K28804
0 AN S0C7 ABEND WAS ISSUED BY DFSORT, ANOTHER PROGRAM OR AN EXIT (PHASE S 1)
sudijana
 
Posts: 9
Joined: Thu May 14, 2009 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Creating 2 report from a PS file

Postby dick scherrer » Sat May 16, 2009 4:31 am

Hello,

Did you use the exact input that was used in the posted solution or did you use "real" data instead?

Suggest you create a small test file with the data (copy/paste - do not modify) that worked in the posted solution and run a test with that.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Creating 2 report from a PS file

Postby sudijana » Sat May 16, 2009 5:28 pm

hey...your code did run fine!

Thanks a million...it indeed was quite a learning experience for me.
And now that I have had the taste of such ingenious coding once, i am sure to turn to you in future too for any such brain-racking project. :)
I reckon the future aint that beyond...for I have a query now itself... :)
sudijana
 
Posts: 9
Joined: Thu May 14, 2009 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Creating 2 report from a PS file

Postby sudijana » Sat May 16, 2009 5:42 pm

Yes...the query...

You see I have a report which displays data from Saturday to Friday...for example the file has been created for data display from 28/02/09 to 06/03/09.
However for 28/02/09,the data file doesn't have any data for which the report is not showing even the 'date'.

Now my requirement needs me to display the date even if no records are present corresponding to that date. :(
Can you please tell me a workaround for this?

P.S. the batch job runs every Sunday(e.g. 08/03/09) and creates data for the previous week i.e Saturday(28/02/09) till Friday(06/03/09).
sudijana
 
Posts: 9
Joined: Thu May 14, 2009 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Creating 2 report from a PS file

Postby dick scherrer » Sat May 16, 2009 9:55 pm

Hello,

it indeed was quite a learning experience for me.
And now that I have had the taste of such ingenious coding once, i am sure to turn to you in future too for any such . . .
It will be far better if you learn to turn to yourself :)

You will only be a student/fresher for a short while. Then your manager will expect you to be able to work without depending on outside resources . I'd suggest you make sure you can explain to someone else every part of the solution that produced the output you needed.

Good luck and someone will be here when there are questions :)
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post