DEFINE FILE FINALREPORT
CHECK_ITEM_DATE/YYMD = DATECVT(CHECK_ITEM_DATE,'MDYY','YYMD');
DaysToPay/D6 = IF CHECK_ITEM_DATE EQ '' THEN 0 ELSE CHECK_ITEM_DATE - INVOICE_DATE;
DaysToPay2/A20 = IF CHECK_ITEM_DATE EQ '' THEN 'OPEN PAYABLE' ELSE FTOA(DaysToPay,'(D6)', DaysToPay2);
DaysInOpenAP/D6 = IF CHECK_ITEM_DATE EQ '' THEN 0 ELSE CHECK_ITEM_DATE - YYMDGLDate;
DaysInOpenAP2/A20 = IF CHECK_ITEM_DATE EQ '' THEN 'OPEN PAYABLE' ELSE FTOA(DaysInOpenAP,'(D6)', DaysInOpenAP2);
LATE/A11 = IF CHECK_ITEM_DATE NE '' AND CHECK_ITEM_DATE LT DUE_DATE THEN 'YES' ELSE IF CHECK_ITEM_DATE NE '' AND CHECK_ITEM_DATE GT DUE_DATE THEN 'NO' ELSE IF CHECK_ITEM_DATE EQ '' THEN 'OPEN';
END
TABLE FILE FINALREPORT
PRINT
VENDOR_NUMBER
ALPHA_NAME
PAYMENT_TERMS
INVOICE_NUMBER
AMOUNT
PAYMENT_ITEM_NUMBER
INVOICE_DATE
YYMDScanDate
YYMDGLDate
DUE_DATE
CHECK_ITEM_DATE
DaysToReceive
DaysToApprove
DaysInOpenAP
DaysInOpenAP2
LATE
BY VENDOR_NUMBER NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
I need to display the Average of: DaysToReceive DaysToApprove DaysInOpenAP DaysInOpenAP2
under their column. How can I accomplish that?
Thanks,This message has been edited. Last edited by: kpiracha,
WebFOCUS 7.6.9 Windows 2003 HTML, Plain Text
April 08, 2011, 12:08 PM
kpiracha
can anyone help with this?
WebFOCUS 7.6.9 Windows 2003 HTML, Plain Text
April 08, 2011, 12:28 PM
jimster06
Although you did not say so, I suspect that you want these data at the vendor break.
Take a look at Manipulating Summary Values With Prefix Operators in the Creating Reports manual.
This should get you started; styling will be interesting.
HTH
jimster06 DevStu WF 7.6.11 W7 HTML, PDF, EXL2K
April 08, 2011, 12:35 PM
kpiracha
Yes, I actually wanted by Vendor and then Invoice. I will taked a look at the top suggested.
WebFOCUS 7.6.9 Windows 2003 HTML, Plain Text
April 10, 2011, 12:51 PM
<JG>
quote:
PRINT
This automatically means that the average applies to an input row and so is equal the value.
Basically 1 / 1 = 1.
Multi-verb request is required
TABLE FILE FINALREPORT
SUM
COMPUTE AVG_AMT/D20.2= AMOUNT / CNT.INVOICE_NUMBER;
BY VENDOR_NUMBER
BY ALPHA_NAME
PRINT
AMOUNT
PAYMENT_ITEM_NUMBER
INVOICE_DATE
YYMDScanDate
YYMDGLDate
DUE_DATE
CHECK_ITEM_DATE
DaysToReceive
DaysToApprove
DaysInOpenAP
DaysInOpenAP2
LATE
BY VENDOR_NUMBER
BY ALPHA_NAME
BY PAYMENT_TERMS
BY INVOICE_NUMBER
END
April 11, 2011, 09:17 AM
Danny-SRL
I think that this is what you might be wanting:
TABLE FILE CAR
PRINT RCOST DCOST
BY COUNTRY
ON COUNTRY SUBTOTAL AVE. RCOST AVE. DCOST
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
April 11, 2011, 09:34 AM
kpiracha
Thanks for all your help guys. I tried both options but because of the DEFINE DaysToPay2/A20 = IF CHECK_ITEM_DATE EQ '' THEN 'OPEN PAYABLE' ELSE FTOA(DaysToPay,'(D6)', DaysToPay2);
The averages were not showing up correctly. If I did AVE. DaysToPay2, it will just show as blank. If I used DaysToPay for the avg, it will give the average based on the number of rows. For eg:
If DaysToPay column as the following: 26 30 0 0 0 0
Then the average was 26+30\6 because there were 6 rows. This was not the right average for our purpose since we wanted to ignore the blank or 0s.
I had to update the code as follows:
DEFINE FILE FINALREPORT
CHECK_ITEM_DATE/YYMD = DATECVT(CHECK_ITEM_DATE,'MDYY','YYMD');
DaysToPay/D6 = CHECK_ITEM_DATE - INVOICE_DATE;
DaysToPay2/A20 = IF CHECK_ITEM_DATE EQ '' THEN 'OPEN PAYABLE' ELSE FTOA(DaysToPay,'(D6)', DaysToPay2);
DaysInOpenAP/D6S = CHECK_ITEM_DATE - YYMDGLDate;
DaysInOpenAP2/A20 = IF CHECK_ITEM_DATE EQ '' THEN 'OPEN PAYABLE' ELSE FTOA(DaysInOpenAP,'(D6)', DaysInOpenAP2);
LATE/A11 = IF CHECK_ITEM_DATE NE '' AND CHECK_ITEM_DATE LT DUE_DATE THEN 'YES' ELSE IF CHECK_ITEM_DATE NE '' AND CHECK_ITEM_DATE GT DUE_DATE THEN 'NO' ELSE IF CHECK_ITEM_DATE EQ '' THEN 'OPEN';
TOAVG/D10 MISSING ON= IF DaysToPay LE 0 THEN MISSING ELSE DaysToPay;
TOAVG2/D10 MISSING ON= IF DaysInOpenAP LE 0 THEN MISSING ELSE DaysInOpenAP;
END
TABLE FILE FINALREPORT
SUM AVE.DaysToReceive NOPRINT
AVE.DaysToApprove NOPRINT
AVE.TOAVG NOPRINT
AVE.TOAVG2 NOPRINT
BY VENDOR NOPRINT
PRINT
VENDOR_NUMBER
ALPHA_NAME
PAYMENT_TERMS
INVOICE_NUMBER
AMOUNT
PAYMENT_ITEM_NUMBER
INVOICE_DATE
YYMDScanDate
YYMDGLDate
DUE_DATE
CHECK_ITEM_DATE
DaysToReceive
DaysToApprove
DaysToPay2
DaysInOpenAP2
LATE
BY VENDOR_NUMBER NOPRINT
-*ON TABLE SUBTOTAL AVE. DaysToReceive AVE. DaysToApprove AVE. DaysToPay2 AVE. DaysInOpenAP2
ON VENDOR_NUMBER SUBFOOT
"Average Days - Receive,Approve,Pay & Open AP<AVE.DaysToReceive<AVE.DaysToApprove<AVE.TOAVG<AVE.TOAVG2"
ON TABLE PCHOLD FORMAT EXL2K
and then used the stylesheet modification to move the amount under the right column.