I made 3 reports all reporting off of the same table with different criteria. I want to combined the reports to only print 1 report with all the data. Here is my code.

TABLE FILE FDI001
SUM 
     'CNT.FDI001.FDI001.FDI001_DT_REPORTED' AS 'NEW'
BY 'FDI001.FDI001.FDI001_ACCT'
BY 'FDI001.FDI001.MonthYear2'
HEADING
""
FOOTING
""
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) AND ( FDI001.FDI001.dtrpt2 GE '2010/01/01' ) AND ( FDI001.FDI001.dtrpt2 LE '2010/10/31' );


TABLE FILE FDI001
SUM 
     'CNT.FDI001.FDI001.FDI001_DT_REPORTED' AS 'CLOSED'
BY 'FDI001.FDI001.FDI001_ACCT'
BY 'FDI001.FDI001.MonthYear3'
HEADING
""
FOOTING
""
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) AND ( FDI001.FDI001.closedt2 GE '2010/01/01' ) AND ( FDI001.FDI001.closedt2 LE '2010/10/31' );


TABLE FILE FDI001
SUM 
     'CNT.FDI001.FDI001.FDI001_DT_REPORTED' AS 'OPEN'
BY 'FDI001.FDI001.MonthYear2'
BY 'FDI001.FDI001.FDI001_ACCT'
HEADING
""
FOOTING
""
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) AND ( FDI001.FDI001.FDI001_DEPOSIT_AMT_BAL EQ 0 ) AND ( FDI001.FDI001.FDI001_CLOSE_DT EQ 0 ) AND ( FDI001.FDI001.dtrpt2 GE '2010/01/01' ) AND ( FDI001.FDI001.dtrpt2 LE '2010/10/31' );

I want the output to look like this.

Month Year    NEW    CLOSED    OPEN
January,2010  10     5         2
Febuary,2010  25     7         40

I was going to make the reports hold files and go from there but because the closed report is reporting on a differrent date the data for the closed column is all wrong. I am not sure how to fix this. Any help is appreciated.

This message has been edited. Last edited by: <d3nis370>,

quote:

WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) AND ( FDI001.FDI001.dtrpt2 GE '2010/01/01' ) AND ( FDI001.FDI001.dtrpt2 LE '2010/10/31' );

I would try to create a define for each of the where's in the 3 requests. The first could possibly read something like:

NEW/I4 = IF (FDI001_ACCT EQ 7125 OR 7126 OR 7127) AND (dtrpt2 GE '2010/01/01') AND (dtrpt2 LE '2010/10/31') THEN 1 ELSE 0;

And then it is just a question of SUM-ming the defined fields by the sort fields. I did also notice that one of the sort orders is different from the other two. That is hopefully not intentional, if it is you can't use this method because you can't have two different sort orders in one request.

Use Match File

MATCH FILE FDI001
SUM
     'CNT.FDI001.FDI001.FDI001_DT_REPORTED' AS 'NEW'
BY 'FDI001.FDI001.FDI001_ACCT'
BY 'FDI001.FDI001.MonthYear2'  AS monthyear
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) 
AND ( FDI001.FDI001.dtrpt2 GE '2010/01/01' ) 
AND ( FDI001.FDI001.dtrpt2 LE '2010/10/31' );
RUN

FILE FDI001
SUM
     'CNT.FDI001.FDI001.FDI001_DT_REPORTED' AS 'CLOSED'
BY 'FDI001.FDI001.FDI001_ACCT'
BY 'FDI001.FDI001.MonthYear3'  AS monthyear
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) 
AND ( FDI001.FDI001.closedt2 GE '2010/01/01' ) 
AND ( FDI001.FDI001.closedt2 LE '2010/10/31' );
AFTER MATCH HOLD OLD-OR-NEW
RUN

FILE FDI001
SUM
     'CNT.FDI001.FDI001.FDI001_DT_REPORTED' AS 'OPEN'
BY 'FDI001.FDI001.FDI001_ACCT'
BY 'FDI001.FDI001.MonthYear2'  AS monthyear
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) 
AND ( FDI001.FDI001.FDI001_DEPOSIT_AMT_BAL EQ 0 ) 
AND ( FDI001.FDI001.FDI001_CLOSE_DT EQ 0 ) 
AND ( FDI001.FDI001.dtrpt2 GE '2010/01/01' ) 
AND ( FDI001.FDI001.dtrpt2 LE '2010/10/31' );
AFTER MATCH HOLD OLD-OR-NEW
END

(note that you have to put the BY fields in corresponding order, and give them matching column-names.)
That should give you a hold file with the required rows and columns.

Ah, yes, overlooked that the sorts are all different. But as long as the sortorder is the same, you could also try to do it with a define. Something like SORTFIELD/MYY = IF NEW EQ 1 'CLOSED' THEN MonthYear3 ELSE MonthYear2; and then sort by SORTFIELD. Might also work ...

GamP the data for New and Open is correct but for closed its still wrong.

j.gross I got an error saying unrecognized (and it prints the code). I am guessing it isn't recognizing the match.

quote:

SORTFIELD/MYY = IF NEW EQ 1 'CLOSED' THEN MonthYear3 ELSE MonthYear2;

why is 'CLOSED' in the define?

well i figured out what was wrong with my match and it worked perfect! Thanks for everyones help!!!