I have 3 reports that I have made. Two of them have specific dates to report off of while the other report does not. Here is where I am having trouble for the third report all I want to know is for a specific month ( like for the month of november ) how many records are there. Now since the other two reports are using a by for the month my output is incorrect. I am getting

 
Month Year        New     Closed     Open
January 2009      25      10         85 
.                                    85
.                                    .
.                                    .
November 2010     158     52         85

I want to be getting

  
Month Year        New     Closed     Open
January 2010      25      10         25 
.                                    65
.                                    .
.                                    .
November 2010     158     52         46

So how do I get my report to do this? I know it has to do with the dates.

Here is my code

 
-DEFAULTS &FromDate = '2010/01/01', &ToDate = '2010/11/30'
DEFINE FILE FDI001
I8dtrpt/I8YYMD=FDI001.FDI001.FDI001_DT_REPORTED;
MonthYear2/MtrYY=I8dtrpt;
dtrpt2/YYMD=I8dtrpt;
I8closedt/I8YYMD=FDI001.FDI001.FDI001_CLOSE_DT;
MonthYear3/MtrYY=I8closedt;
closedt2/YYMD=I8closedt;
END
MATCH FILE FDI001
SUM
     'CNT.FDI001.FDI001.FDI001_DT_REPORTED' AS 'NEW'
BY 'FDI001.FDI001.FDI001_ACCT'
BY 'FDI001.FDI001.MonthYear2' AS 'Month Year'
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) AND ( FDI001.FDI001.dtrpt2 GE '&FromDate' ) AND ( FDI001.FDI001.dtrpt2 LE '&ToDate' );
RUN


FILE FDI001
SUM
     'CNT.FDI001.FDI001.FDI001_DT_REPORTED' AS 'CLOSED'
BY 'FDI001.FDI001.FDI001_ACCT'
BY 'FDI001.FDI001.MonthYear3' AS 'Month Year'
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) AND ( FDI001.FDI001.closedt2 GE '&FromDate' ) AND ( FDI001.FDI001.closedt2 LE '&ToDate' );
AFTER MATCH HOLD AS SUMHOLD2 OLD
END


JOIN
 SUMHOLD2.SUMHOLD2.FDI001_ACCT IN SUMHOLD2 TO MULTIPLE FDI001.FDI001.FDI001_ACCT
 IN FDI001 TAG J0 AS J0
 END
DEFINE FILE SUMHOLD2
ServRel/A1='-';
END

TABLE FILE SUMHOLD2
SUM 
     'SUMHOLD2.SUMHOLD2.NEW' AS 'New'
     'SUMHOLD2.SUMHOLD2.CLOSED' AS 'Closed'
     ServRel AS 'Service Released'
     'CNT.J0.FDI001.FDI001_LOAN_NO' AS 'OPEN'
BY 'SUMHOLD2.SUMHOLD2.Month Year'
ON TABLE SUBHEAD
"Monitored Claims"
WHERE ( J0.FDI001.FDI001_CLOSE_DT EQ 0 ) AND ( J0.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) AND ( J0.FDI001.FDI001_DEPOSIT_AMT_BAL EQ 0 );
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K

This message has been edited. Last edited by: Kerry,

It depends a bit on what your definition of the OPEN count is. Your join for the last request is on account number, while you sort by month/year. Assuming that the acoount numbers used will all be present for every month, the count for OPEN will yield the same amount for every month. And that seems also to be what you are getting as a result (always 85).

Here is a better example of what I am trying to do.

 
-*CLOSED REPORT
DEFINE FILE FDI001
i8closedt/I8YYMD=FDI001.FDI001.FDI001_CLOSE_DT;
closedt/YYMD=i8closedt;
MonthYear1/MtrYY=i8closedt;
END
TABLE FILE FDI001
SUM 
     'CNT.FDI001.FDI001.FDI001_CLOSE_DT' AS 'Closed'
BY 'FDI001.FDI001.MonthYear1'
HEADING
"Monitored Claims"
FOOTING
""
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7126 OR 7127 OR 7125 ) AND ( FDI001.FDI001.closedt GE '&FromDate' ) AND ( FDI001.FDI001.closedt LE '&ToDate' );
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END

-*LOAN STATUS REPORT
DEFINE FILE FDI001
Current/I5=IF FDI001.FDI001.FDI001_LOAN_STATUS EQ 'C' THEN 1 ELSE 0;
Delinquent/I5=IF FDI001.FDI001.FDI001_LOAN_STATUS EQ 'D' THEN 1 ELSE 0;
Forclosure/I5=IF FDI001.FDI001.FDI001_LOAN_STATUS EQ 'F' THEN 1 ELSE 0;
Bankruptcy/I5=IF FDI001.FDI001.FDI001_LOAN_STATUS EQ 'B' THEN 1 ELSE 0;
END

TABLE FILE FDI001
SUM
     'FDI001.FDI001.Current'
     'FDI001.FDI001.Delinquent'
     'FDI001.FDI001.Foreclosure'
     'FDI001.FDI001.Bankruptcy'
HEADING
"Monitor Loan Status Report"
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7126 OR 7127 ) AND ( FDI001.FDI001.FDI001_CLOSE_DT EQ 0 ) AND ( FDI001.FDI001.FDI001_DEPOSIT_AMT_BAL GT 0 );
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END

-*DAYS OPEN REPORT
JOIN
 FDI001.FDI001.FDI001_ACCT AND FDI001.FDI001.FDI001_LOAN_NO IN FDI001
 TO MULTIPLE FDI002.FDI002.FDI002_ACCT AND FDI002.FDI002.FDI002_LOAN_NO
 IN FDI002 TAG J0 AS J0
 END
JOIN
 FDI001.FDI001.FDI001_ACCT AND FDI001.FDI001.FDI001_LOAN_NO IN FDI001
 TO MULTIPLE FDI007.FDI007.FDI007_ACCOUNT_NO AND FDI007.FDI007.FDI007_LOAN_NO
 IN FDI007 TAG J2 AS J2
 END

DEFINE FILE FDI001
dtrpt/I8YYMD=FDI001.FDI001.FDI001_DT_REPORTED;
Currentdt2/DMtY=&YYMD;
dtrpt3/DMtY=dtrpt;
CurrentDate/YY|M|D=&YYMD;
dtrpt2/YY|M|D=dtrpt;
daysopen2/I5=DATEDIF(FDI001.FDI001.dtrpt2, CurrentDate, 'D');
90days/I5=IF daysopen2 LE 90 THEN 1 ELSE 0;
91to120days/I5=IF daysopen2 GE 91 AND daysopen2 LE 120 THEN 1 ELSE 0;
121to150days/I5=IF daysopen2 GE 121 AND daysopen2 LE 150 THEN 1 ELSE 0;
151to180/I5=IF daysopen2 GE 151 AND daysopen2 LE 180 THEN 1 ELSE 0;
181to360days/I5=IF daysopen2 GE 181 AND daysopen2 LE 360 THEN 1 ELSE 0;
361days/I5=IF daysopen2 GE 361 THEN 1 ELSE 0;
END

TABLE FILE FDI001
SUM 
     90days
     91to120days
     121to150days
     151to180
     181to360days
     361days
HEADING
"Monitor Days Open Report"
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7126 OR 7127 ) AND ( FDI001.FDI001.FDI001_CLOSE_DT EQ 0 ) AND ( FDI001.FDI001.FDI001_DEPOSIT_AMT_BAL GT 0 );
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K CLOSE NOBREAK
END 

This is what prints out when I run it.

Monitored Claims
MonthYear1        Closed
January 2010      25
Febuary 2010      65
.
.
.
November 2010     84

Monitor Loan Status Report
Current     Delinquent      Foreclosure     Bankruptcy
51          44              25              5

Monitor Days Open Report
90days    91to120days     121to150days      151to180      181to360days     361days   TOTAl 
41        21              27                1             20               14        124

The first report is correct but for the last two I am wanting to add a month column. These reports will be run the first week of the month for the previous months like we will run jan 2 2011 and the reports will populate from january 1 2010 to december 31 2010 (depending on what the user enters for the date range). I want the output for the last two reports to look this this.

Monitor Loan Status Report
MonthYear        Current     Delinquent      Foreclosure     Bankruptcy
November 2010    51          44              25              5
December 2010    54          25              5               1

Monitor Days Open Report
MonthYear        90days    91to120days     121to150days      151to180      181to360days     361days   TOTAl 
November 2010    41        21              27                1             20               14        124
December 2010    25        54              8                 3             10               2         102

So how do I get webfocus to keep the output from last month and how would I display the month?

Can you HOLD the output of your first report and then add the MonthYear1 column to the second? Then create a join on the MonthYear1 column from your HOLD file to FDI001 on the matching field. That should link all your metrics up together.

I am a little uncertain about your exact requirements, but see if these changes will provide the desired output:

First, add this DEFINE to the second and third reports:

MonthYear1/MtrYY = FDI001.FDI001.FDI001_DT_REPORTED ;

Then add this BY phrase to both reports:

BY MonthYear1

Finally, change your WHERE clauses in these two reports to:

WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7126 OR 7127 )
  AND ( FDI001.FDI001.FDI001_CLOSE_DT EQ 0 )
  AND ( FDI001.FDI001.FDI001_DEPOSIT_AMT_BAL GT 0 )
  AND ( FDI001.FDI001.FDI001_DT_REPORTED FROM '&ToDate' TO '&YYMD' );