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 EXL2KThis 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.
O/S : Windows
Data Migrator: 7702
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:
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' );
|Powered by Social Strata|