Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Generate report using the values in the file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Thu Aug 09, 2007 1:41 am    Post subject: Generate report using the values in the file
Reply with quote

Mu input file having 150 bytes.

The layout is

Lastname x(20)
Firstname x(20)
Month x(03)
Amount 9(11)

Please check the attachement for the sample Input and output file :
(i.e) If the sigle person (Having same last and first name) having the duplicate row in the input file then the amount have to appear under the header of month's respectively.

It would be great if someone provide me the solution.
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Thu Aug 09, 2007 3:09 am    Post subject: Re: Generate report using the values in the file
Reply with quote

bbharathiraj wrote:
It would be great if someone provide me the solution.
I'd love to, but not all of us can afford the overpriced Excel.....
Please copy you "requirements" into Notepad, align the columns - no tabs, just spaces - and copy it to here and wrap it in "code".....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 09, 2007 3:12 am    Post subject:
Reply with quote

Here is the link to the DFSORT programmers manual contents page.
Sorry, it is late and I don't have the time to generate your answer for you.
There are many examples. Look for key words like HEADER, TRAILER, REPORT, LINES.
You can also download this manual so that you will always have a ready copy for reference.
This link is for all the DFSORT Manuals.
This one is titled Getting Started which also has report examples.

This last link is for DFSORTTRICKs which you can use for future situations. I don't think there is a REPORT example that would provide you a solution to your present problem.

If you wish to download, instead of opening a new Tab or Page with a right-click, use the SAVE AS option which will immediately drive you to a download. Opening large PDF's in a browser sometimes take a while; it is a manual, download it and keep it on your PC.

Though you exell sheet was excellent, many, including me, do not like to download stuff that is just posted on a website.
You could have explained it using BBCODE using [code] and [/code] tags, which will maintain spacing for your entries and are generated as -
Code:
          black screens

On the left bottom of every page on this website under Options you will see ' BBCode is ON', with BBCode as a hyperlink to the FAQ.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Aug 09, 2007 3:22 am    Post subject:
Reply with quote

It's not entirely clear what you want to do.

You say the amount is 9(11) which would be an 11-byte ZD value but in your attachment you show $12.00 which is NOT a ZD value. Does the amount actually have a $ sign and decimal point or is it really just 1200?

Your records only have JAN-JUL data and you only show JAN-JUL in the ouput. Do you actually have to determine which months are present and only print those, or can the months to print be fixed in some way (e.g. you can always show JAN-DEC or you can always just show JAN-JUL or ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 09, 2007 3:23 am    Post subject:
Reply with quote

For those of you who don't want to buy the academic version of MS OFFICE (which is really cheap),
you can download the free OPEN OFFICE Suite that my friends use to deal with my WORD & EXCEL documents.

This is actually an open source 'MS OFFICE' (it is not MS OFFICE) for free written under the GNU license.

You can use this software to read and create documents compatible with MS OFFICE, even imports PDF'S. Like if you want to continue writing your program documentation at home! icon_wink.gif
Back to top
View user's profile Send private message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Thu Aug 09, 2007 4:53 am    Post subject: Re: Generate report using the values in the file
Reply with quote

Hi,


Sorry for the confusion. The amount field is the packed decimal. (s9(11)-COMP variable).

And the month range should be JAN - JUL only.

Thanks,
Bharathiraj
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Aug 10, 2007 1:32 am    Post subject:
Reply with quote

Quote:
The amount field is the packed decimal. (s9(11)-COMP variable).


COMP is BI. COMP-3 is PD. I'll assume it's s9(11) COMP-3 - a 6-byte PD value.

I'm assuming the records for each name are grouped together as in your example, and that you want $IIIIT.TT as the edit mask for the output amounts. Note that's only 7 digits of your 11 digit PD value but that seems to correspond to the output example you gave. Of course, you can change the output format by changing the DFSORT control statements appropriately.

Given all that, here's a DFSORT job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file
//SORTOUT DD DSN=...  output file
//SYSIN    DD    *
  OPTION COPY
  INREC IFOUTLEN=120,
   IFTHEN=(WHEN=INIT,
     BUILD=(1,43,44:44,6,50:7X'00000000000C')),
   IFTHEN=(WHEN=(41,3,CH,EQ,C'JAN'),
     OVERLAY=(86:44,6)),
   IFTHEN=(WHEN=(41,3,CH,EQ,C'FEB'),
     OVERLAY=(50:44,6,86:44,6,44:X'00000000000C')),
   IFTHEN=(WHEN=(41,3,CH,EQ,C'MAR'),
     OVERLAY=(56:44,6,86:44,6,44:X'00000000000C')),
   IFTHEN=(WHEN=(41,3,CH,EQ,C'APR'),
     OVERLAY=(62:44,6,86:44,6,44:X'00000000000C')),
   IFTHEN=(WHEN=(41,3,CH,EQ,C'MAY'),
     OVERLAY=(68:44,6,86:44,6,44:X'00000000000C')),
   IFTHEN=(WHEN=(41,3,CH,EQ,C'JUN'),
     OVERLAY=(74:44,6,86:44,6,44:X'00000000000C')),
   IFTHEN=(WHEN=(41,3,CH,EQ,C'JUL'),
     OVERLAY=(80:44,6,86:44,6,44:X'00000000000C'))
  OUTFIL REMOVECC,NODETAIL,
   HEADER2=(1:'LAST NAME',21:'FIRST NAME',43:'JAN ''06',
    54:'FEB ''06',63:'MAR ''06',73:'APR ''06',83:'MAY ''06',
    93:'JUN ''06',103:'JUL ''06',115:'TOTAL'),
   SECTIONS=(1,40,
     TRAILER3=(1,40,
        41:TOT=(44,6,PD,EDIT=($IIIIT.TT)),
        51:TOT=(50,6,PD,EDIT=($IIIIT.TT)),
        61:TOT=(56,6,PD,EDIT=($IIIIT.TT)),
        71:TOT=(62,6,PD,EDIT=($IIIIT.TT)),
        81:TOT=(68,6,PD,EDIT=($IIIIT.TT)),
        91:TOT=(74,6,PD,EDIT=($IIIIT.TT)),
       101:TOT=(80,6,PD,EDIT=($IIIIT.TT)),
       111:TOT=(86,6,PD,EDIT=($IIIIT.TT))))
/*
Back to top
View user's profile Send private message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Mon Aug 13, 2007 10:23 pm    Post subject: Re: Generate report using the values in the file
Reply with quote

Thanks for the solution. Its working fine for me.
But I have another requirement which is mostly same except the format and I have tried after changing the SORT card. But it didn't work.

Below is the input.(Rec len - 65 and RecFm =FB)

----+----1----+----2----+----3----+----4----+----5----+----6----
ABEND ,CARL ,00569400000,20031201, 000036.04
ABEND ,CARL ,00569400000,20031101, 000036.04
ABEND ,CARL ,00569400000,20031001, 000036.04
ABEND ,CARL ,00569400000,20030901, 000036.04
ABEND ,CARL ,00569400000,20030801, 000036.04
ABEND ,CARL ,00569400000,20030701, 000036.04
ABEND ,CARL ,00569400000,20030601, 000036.04
ABEND ,CARL ,00569400000,20030501, 000036.04
ABEND ,CARL ,00569400000,20030401, 000036.04
KKKKKA ,THERESA ,00556500000,20031201, 000009.55
KKKKKA ,THERESA ,00556500000,20031101, 000009.55
KKKKKA ,THERESA ,00556500000,20031001, 000013.56

The coulm 50 having the value of month (From Jan to Dec).

I am trying to get the output like below

FIRSTNAME LASTNAME ENUM JAN .. APR MAY....... .DEC) ABEND CARL 00569400000 36.00$36.00 $36.00

Could you please assist me and provide me the solution.

Thanks,
Bharathiraj
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Aug 13, 2007 11:09 pm    Post subject:
Reply with quote

It's not clear to me what you're trying to do from the example you show. Please use code tags around your data. Explain what you're trying to do. Explain the input fields. Show a better example of your input records with different values for different records, and the expected output records. Give the starting position, length and format of each relevant field.
Back to top
View user's profile Send private message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Tue Aug 14, 2007 2:03 am    Post subject: Re: Generate report using the values in the file
Reply with quote

Let me give me all the details.

Details of the input feilds.

Record length - 64
Format - FB

First Name x(21)
Last Name x(12)
Emp Number x(12)
date x(09) --> (yyyymmdd)
amount x(10)

Below is the sample input feild.


Code:

ABEND               ,DBCARL     ,00569481962,20030101, 000036.04
ABEND               ,DBCARL     ,00569481962,20030201, 000036.04
ABEND               ,DBCARL     ,00569481962,20030401, 000036.04
ACOSTA              ,THERESA    ,00556520898,20030101, 000009.55
ACOSTA              ,THERESA    ,00556520898,20030201, 000009.55
ACOSTA              ,THERESA    ,00556520898,20030301, 000013.56



I am expecting the output like below

(i.e) If the first and last name or employee number is same, then all the duplicate record should come under one row with the value of amount under respective month.

Code:

FIRSTNAME LASTNAME  JAN   FEB   MAR   APR...... (UPTO DEC)
ABEND      DBCARL   36.00 36.00 00.00 36.00
ACOSTA     THERESA  09.55 09.55 13.56 00.00


Please note the output can have any length and format should be FB.
The code tag is really amazing.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Aug 14, 2007 2:33 am    Post subject:
Reply with quote

Quote:
If the first and last name or employee number is same


Not sure how to parse that "and" and "or". Does it mean "first and last name, or first and employee number is the same" or "first and last are the same, or employee number is the same" or what? Please show a better example with these cases covered.

Also, you show 000036.04 in the input, but 36.00 in the output. Should that be 36.04 in the output? If not, what's the rule that gives 36.00 instead of 36.04?

Is the year in every record always the same (e.g. 2003 for all of the records, or 2004 for all of the records, etc)?
Back to top
View user's profile Send private message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Tue Aug 14, 2007 3:01 am    Post subject: Re: Generate report using the values in the file
Reply with quote

Thanks for your response.

And the amount in output fields is typo. It should be 000036.04. (The same value from amount field).

And there is no special rules there for Name and employee number.

I just mentioned that the first name and last name and employee number should be same.

Thanks,
Bharathiraj
Back to top
View user's profile Send private message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Tue Aug 14, 2007 8:19 pm    Post subject: Re: Generate report using the values in the file
Reply with quote

Sorry.. I missed some asked info.

1) The amount should be 000036.04 (The real value in Amount field from input file)

2) And phrase "AND" "OR" is typo. If the first and last name and employee number is same, then all the duplicate record should come under one row with the value of amount under respective month.

3) Yes. I have only 2003 in the input field.

Thanks a lot.

Bharathi
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Aug 14, 2007 10:20 pm    Post subject:
Reply with quote

Here's a DFSORT job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=... input file (FB/64)
//SORTOUT DD DSN=... output file (FB/185)
//SYSIN    DD    *
  OPTION COPY
   INREC IFTHEN=(WHEN=INIT,
     BUILD=(1:1,65,66:12C'000000.00 ')),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'01'),
     OVERLAY=(66:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'02'),
     OVERLAY=(76:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'03'),
     OVERLAY=(86:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'04'),
     OVERLAY=(96:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'05'),
     OVERLAY=(106:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'06'),
     OVERLAY=(116:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'07'),
     OVERLAY=(126:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'08'),
     OVERLAY=(136:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'09'),
     OVERLAY=(146:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'10'),
     OVERLAY=(156:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'11'),
     OVERLAY=(166:56,9)),
   IFTHEN=(WHEN=(50,2,CH,EQ,C'12'),
     OVERLAY=(176:56,9))
  OUTFIL REMOVECC,NODETAIL,
   HEADER2=(1:'FIRSTNAME',22:'LASTNAME',34:'JAN',44:'FEB',
    54:'MAR',64:'APR',74:'MAY',84:'JUN',94:'JUL',104:'AUG',
    114:'SEP',124:'OCT',134:'NOV',144:'DEC'),
   SECTIONS=(1,44,
     TRAILER3=(1,20,22:22,11,
       34:TOT=(66,9,UFF,EDIT=(TTTTTT.TT)),
       44:TOT=(76,9,UFF,EDIT=(TTTTTT.TT)),
       54:TOT=(86,9,UFF,EDIT=(TTTTTT.TT)),
       64:TOT=(96,9,UFF,EDIT=(TTTTTT.TT)),
       74:TOT=(106,9,UFF,EDIT=(TTTTTT.TT)),
       84:TOT=(116,9,UFF,EDIT=(TTTTTT.TT)),
       94:TOT=(126,9,UFF,EDIT=(TTTTTT.TT)),
      104:TOT=(136,9,UFF,EDIT=(TTTTTT.TT)),
      114:TOT=(146,9,UFF,EDIT=(TTTTTT.TT)),
      124:TOT=(156,9,UFF,EDIT=(TTTTTT.TT)),
      134:TOT=(166,9,UFF,EDIT=(TTTTTT.TT)),
      144:TOT=(176,9,UFF,EDIT=(TTTTTT.TT))))
/*
Back to top
View user's profile Send private message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Wed Aug 15, 2007 2:29 am    Post subject: Reply to: Generate report using the values in the file
Reply with quote

Hi,

I have got my output. Thank you very much for your solution.

Regards,
Bharathi
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Splitting a report based on a field abdulrafi DFSORT/ICETOOL 6 Thu Mar 23, 2017 6:56 pm
No new posts Receive a file using PCOMM macro Harald.v.K IBM Tools 0 Thu Mar 23, 2017 6:50 pm
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm
No new posts Formatting VB File Learncoholic DFSORT/ICETOOL 3 Mon Mar 20, 2017 12:29 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us