MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Queries in report creation using syncsort

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
gsat
Beginner


Joined: 24 Nov 2005
Posts: 9
Topics: 3
PostPosted: Wed May 10, 2006 7:25 am    Post subject: Queries in report creation using syncsort Reply with quote
Hi,
I have given below a sample input file and the output report(s) to be created from that. I have also listed the question regarding that below.

Input file:
Code:

12345M25DEPTA0001000
12346F21DEPTA0001010
12350F25DEPTB0001015
12351M28DEPTC0001500


A) Report when input file has details records.
Code:

==============================================
Date: dd/mm/yyy                     SALARY REPORT                     Page 1
Time: hh.mm.ss                        FOR  month


    EMP NO     TYPE         AGE          DEPT                 AMOUNT

    12345       MALE         25            DEPTA               0001000.00
    12346       FEMALE      21            DEPTB               0001010.00
    12350       FEMALE      25            DEPTB               0001015.00
    12351       MALE         28            DEPTC               0001500.00

    SUB TOTAL                                                        0004525.00

    GRAND TOTAL                                              z,zzz,zzz,999.00



                                 **** CONFIDENTIAL ****
===============================================

B) Report when no detail record is present
Code:

==============================================
Date: dd/mm/yyy                     SALARY REPORT                     Page 1
Time: hh.mm.ss                        FOR  month

                              *** NO DATA ***



                                 **** CONFIDENTIAL ****
===============================================

My questions are:-

    1) Can the above done with Syncsort?

    2) Using syncsort can we capture date and time and display it in the report header as shown above using syncsort?

    3) Can we get the month from the system directly thru sort?

    4) Based on the value of a field in each record, can we have a string or
    value written in the report. (M - MALE, F - FEMALE in the above report) while processing each detail record?

    5) How to verify if there are detail recs in i/p file so as to write the report accordingly (NO DATA report if nor detail rec found)

    6) The example is a simple representation of the input data. in reality it would be a file with a huge number of records.
    For better performance, is it better to do this using Syncsort or should we code a cobol pgm or use FOCUS to do the report ?

    7) Last but not least if there is already a post on this please let me know the link as I was unable to find. Sorry for the inconvenience if any.
    Also if u have the link to the latest syncsort manual please pass that too.

Thanks a ton in advance.

rgds,
gsat
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Wed May 10, 2006 8:29 am    Post subject: Reply with quote
Gsat,

Answers for question 1 thru 5 is YES

6.To get the exact results please post the file layout along with the dcb properties.

7. Syncsort manuals are copyrighted. If your shop has a valid licence then you should be able to request a copy from syncsort.

Also how did you arrive at the subtotal and grand total fields? what is the formula for calculating the values.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
gsat
Beginner


Joined: 24 Nov 2005
Posts: 9
Topics: 3
PostPosted: Wed May 10, 2006 9:48 am    Post subject: Reply with quote
Kolusu,
The subtotal is the sum of the amount in one page and the Grand Total is the sum of all the subtotals.

Can you please let me know how to do it for Qns 4 and 5 alone (from above) using syncsort?

Thanks again,
gsat.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Wed May 10, 2006 10:05 am    Post subject: Reply with quote
Quote:

4) Based on the value of a field in each record, can we have a string or
value written in the report. (M - MALE, F - FEMALE in the above report) while processing each detail record?


Simple use a CHANGE parm to have the desired values.

Quote:

5) How to verify if there are detail recs in i/p file so as to write the report accordingly (NO DATA report if nor detail rec found)


Check this link which explains in detail about empty file checking

http://www.mvsforums.com/helpboards/viewtopic.php?t=5319

Depending on the return code you will create the report.


Here is an example of writing the report.

Code:

//STEP0100 EXEC PGM=SORT         
//SYSOUT   DD SYSOUT=*           
//SORTIN   DD *                 
12345M25DEPTA0001000             
12346F21DEPTA0001010             
12350F25DEPTB0001015             
12351M28DEPTC0001500             
//SORTOUT  DD SYSOUT=*           
//SYSIN    DD *                                         
 INREC FIELDS=(01,80,DATE1)                             
 SORT FIELDS=(01,5,CH,A)                                 
 OUTREC FIELDS=(02:01,05,                               
                10:06,01,CHANGE=(06,C'M',C'  MALE',     
                                    C'F',C'FEMALE'),     
                         NOMATCH=(C'NO GEN'),           
                22:07,02,                               
                26:09,05,                               
                35:14,07,C'.00',                         
                50:85,02,CHANGE=(09,C'01',C'JANUARY  ', 
                                    C'02',C'FEBRUARY ', 
                                    C'03',C'MARCH    ', 
                                    C'04',C'APRIL    ', 
                                    C'05',C'MAY      ', 
                                    C'06',C'JUNE     ', 
                                    C'07',C'JULY     ', 
                                    C'08',C'AUGUST   ', 
                                    C'09',C'SEPTEMBER', 
                                    C'10',C'OCTOBER  ', 
                                    C'11',C'NOVEMBER ', 
                                    C'12',C'DECEMBER '),
                         NOMATCH=(C'NO MONTH'),         
               80:X)                                     
 OUTFIL REMOVECC,                                 
 OUTREC=(1,45,80:X),                             
 HEADER2=(1/,                                     
          02:'DATE:',DATE=(MD4/),                 
          35:'SALARY REPORT',                     
          60:'PAGE : ',&PAGE,/,                   
          02:'TIME:',TIME(24.),                   
          35:'FOR MONTH OF ',50,09,               
          2/,                                     
          02:'EMP NO',                           
          10:'  TYPE',                           
          18:'   AGE',                           
          25:'  DEPT',                           
          38:' AMOUNT',/,                         
          02:'======',                           
          10:'======',                           
          18:'======',                           
          26:'=====',                             
          35:'=========='),                       
 TRAILER2=(2/,                                   
          05:'SUB TOTAL :',                       
          33:TOT=(35,7,ZD,M11,LENGTH=9),C'.00',   
          1/,                                     
          80:X),                                 
 TRAILER1=(2/,                                   
          05:'GRAND TOTAL :',                     
          33:TOT=(35,7,ZD,M11,LENGTH=9),C'.00',   
          2/,                                     
          23:'**** CONFIDENTIAL ****',           
          80:X)                                   
/*                                               


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
gsat
Beginner


Joined: 24 Nov 2005
Posts: 9
Topics: 3
PostPosted: Wed May 10, 2006 11:09 pm    Post subject: Reply with quote
Thanks a lot Kolusu. It definitely helped.
Also I did check ur earlier posts on empty file handling and have made use of the logic.

I have one question though, just to handle a different situation.
When the input file has a header rec, then detail recs and then a trailer record,
how do we generate a regular report or a 'no data' report in the same step based on the presence or absence of the details records.

Thanks,
gsat
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Thu May 11, 2006 4:26 am    Post subject: Reply with quote
Quote:

I have one question though, just to handle a different situation.
When the input file has a header rec, then detail recs and then a trailer record,
how do we generate a regular report or a 'no data' report in the same step based on the presence or absence of the details records.


gsat,

Is there a way that you can identify the header and trailer records? If so then use OMIT cond to eliminate those records and then check for empty file.

Depending on the check generate the report

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
gsat
Beginner


Joined: 24 Nov 2005
Posts: 9
Topics: 3
PostPosted: Thu May 11, 2006 10:39 am    Post subject: Reply with quote
thanks kolusu. I did that. but have got into another problem.

I used an IDCAMS step to check if the i/p file had just header and trailer rec only and based on the return code I was trying to use the appropriate sortin card for creating the report. but it was not working.

//STEP1 EXEC PGM=IDCAMS to check for empty file..
.
.
.
//CHEKPT IF STEP1.RC=00 THEN
// SET CNTLCRD='CNTL1'
// ELSE
// SET CNTLCRD='CNTL2'
// ENDIF
//*
//STEP2 EXEC PGM=SORT
//SORTIN.....
//SORTOUT....
//SYSOUT....
//SYSIN DD DSN=MY.PDS(&CNTLCRD),DISP=SHR

This way i was trying to create a report with data or no data based on the presence of detail recs in i/p file.
But what happened was the ELSE condition was not at all getting executed.

Am I missing anything here?

please let me know.

thanks,
gsat
Please
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Thu May 11, 2006 11:13 am    Post subject: Reply with quote
gsat,

You forgot the rules of using the SET statement. Check this link . read carefully the last condition.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/IEA2B631/25.1.9?SHELF=&DT=20030423085347&CASE=

You need to code 2 steps and excuete them depending on the returncode from the empty file checking step.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
gsat
Beginner


Joined: 24 Nov 2005
Posts: 9
Topics: 3
PostPosted: Thu May 11, 2006 11:24 am    Post subject: Reply with quote
Thanks Kolusu.

That was how i had initally coded it. but wanted to try it in two steps but missed it out with the SET parameter.

Thanks a lot for all your help!!

Regards,
gsat
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group