ON TABLE COLUMN-TOTAL AS 'TOTAL' totearlyinvamt totlateinvamt
The totearlyinvamt is coming out correct but the totlateinvamt is not. In fact it isn't even close. Here are the column definitions.
totearlyinvamt/D12.2CSM AS 'Invoice Amount,Paid Early'
totlateinvamt/D12.2CSM AS 'Invoice Amount,Paid Late'
Here are the defines for each.
IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE 0
IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE 0
Can anyone help with this?This message has been edited. Last edited by: Tracie,
WebFocus 7703 Windows 7 Output format: HTML, Excel, PDF
November 18, 2008, 11:22 AM
Prarie
Are Are you sure the individual amounts are correct in totlateinvamt? Are you capturing everything that should be in that define? If the Total is incorrect...then I would imagine it is the individual amounts that are incorrect.
In Focus since 1993. WebFOCUS 7.7.03 Win 2003
November 18, 2008, 11:26 AM
Tracie
The totals are correct. The totlateinvamt starts out as correct. Invoice amt $150 on the first line/totlateinvamt = "$150. Second line invoice amt is $200/totlateinvamt is $350 and so on. The number is correct until it hits the first line contains an paid early amount.
WebFocus 7703 Windows 7 Output format: HTML, Excel, PDF
November 18, 2008, 11:45 AM
Tracie
Here are the first two lines of the report
$89,732.47 total late $89,732.47
$42,570.50 total late $132,302.97
It keeps adding this way until::
$4,410.40 total late $371,442.90
$22,391.86 total early $22,291.86
$3,813.10 total early $26,204.96
$25,106.61 total early $51,311.57
$10,997.00 total early $62,308.57
$5,195.40 total late $5195.30
$67,175.58 total early $67,175.58
$2,484.58 total late $2,484.58
Total early $229,392.54 This number is correct.
Total late $3,845,744.34 (should be 379,122.88)
WebFocus 7703 Windows 7 Output format: HTML, Excel, PDF
November 18, 2008, 11:52 AM
Spence
Is the date comparison working?
IF paydate1 GT DUE_DATE
WF 8 version 8.2.04. Windows. In focus since 1990.
November 18, 2008, 11:53 AM
Tracie
Yes, the date comparison is working. The ones that should be early are and the ones late are showing as late.
WebFocus 7703 Windows 7 Output format: HTML, Excel, PDF
November 18, 2008, 01:28 PM
Darin Lee
Could you give us all of your code? I am not even seeing how you are using column-total. From your example, it appears like you are creating a running total, not a column-total.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
November 18, 2008, 02:11 PM
Tracie
PRINT
SERIES_ID AS 'Invoice Type'
GROSS_AMOUNT/D20.2CM AS 'Invoice,Amount'
INVOICE_DATE/MDYY AS 'Invoice Date'
DUE_DATE/MDYY AS 'Due Date'
paydate1/MDYY AS 'Date Paid'
diff AS 'Days ,Behind/Ahead'
posneg/A40 AS ''
totearlyinvamt/D12.2CSM AS 'Invoice Amount,Paid Early'
totlateinvamt/D12.2CSM AS 'Invoice Amount,Paid Late'
BY INVOICE_NO AS 'Invoice No.'
HEADING
"Customer No: <+0>&IDENTITY<+0> <NAME "
FOOTING
"Prepared on: <+0>&DATEtrMDYY <+0> "
" Page:<TABPAGENO"
ON TABLE SUBFOOT
"Total number of invoices paid late: <late Total number of invoices paid early: <early "
" Total number of invoices: <totalinvoices "
WHERE ( IDENTITY EQ '&IDENTITY.Customer No:.' ) AND ( STATE EQ 'PaidPosted' );
WHERE SERIES_ID EQ 'CD';
WHERE GROSS_AMOUNT GT 0;
WHERE ( PAY_DATE GE DT(&FromPaidDate.From Date Paid.) ) AND ( PAY_DATE LE DT(&ToDatePaid.To Date Paid.) );
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL' totearlyinvamt totlateinvamt
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
Here you go. Thanks for the help.
WebFocus 7703 Windows 7 Output format: HTML, Excel, PDF
November 18, 2008, 02:23 PM
Darin Lee
I think there are some things missing. Where are your defines? You also reference fields in you subfoot lines that don't appear anywhere. Are they also in the defines?
Without seeing all of the code, here are a couple suggestions: It appears that totearlyinvamt and totlateinvamt are running totals. This would not appear correctly if you are trying to add a column-total to a running total column. Also, I usually use
ON TABLE SUBTOTAL AS 'TOTAL' totearlyinvamt totlateinvamt
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
November 18, 2008, 02:32 PM
Tracie
IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE 0
IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE 0
Here are the defines. The first one for totearlyinvamt and the second one is for totlateinvamt. I tried your suggestion and got the same results.This message has been edited. Last edited by: Tracie,
WebFocus 7703 Windows 7 Output format: HTML, Excel, PDF
November 18, 2008, 02:52 PM
FrankDutch
Tracie
Your defines of the totearlyinvamt and totlateinvamt should be done in the table part and not as predefined fields.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 18, 2008, 02:59 PM
Tracie
How do I create these fields in the Table part using the report painter? Thank you.
WebFocus 7703 Windows 7 Output format: HTML, Excel, PDF
November 18, 2008, 03:17 PM
Darin Lee
quote:
IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE 0 IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE 0
This would not be correct syntax for a define. By this, do you mean: DEFINE FILE xxx totearlyinvamt/D12.2=IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE 0 ; totlateinvamt/D12.2=IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE 0; END
Why are you adding totearlyinvamt back onto itself? And, as this is calculated record by record in the define, that amount will always be zero so it would be the same as
totearlyinvamt/D12.2=IF paydate1 LE DUE_DATE THEN GROSS_AMOUNT ELSE 0; totlateinvamt/D12.2=IF paydate1 GT DUE_DATE THEN GROSS_AMOUNT ELSE 0;
So for each record, it should have either one amount or the other, but not both, correct? so try
DEFINE FILE XXX
totearlyinvamt/D12.2=IF paydate1 LE DUE_DATE THEN GROSS_AMOUNT ELSE 0;
totlateinvamt/D12.2=IF paydate1 GT DUE_DATE THEN GROSS_AMOUNT ELSE 0;
END
TABLE FILE XXX
PRINT
GROSS_AMOUNT
INVOICE_DATE
DUE_DATE
totearlyinvamt
totlateinvamt
BY INVOICE_NO
ON TABLE SUBTOTAL
END
and see what you get.
There are still other pieces of your code that you are not showing that could have some impact on what you are seeing. Without seeing all of it, it's hard to be very exact with a solution.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
November 19, 2008, 06:44 AM
M Cavanaugh
JI - too early in the morning - you do have a profile - sorry about that - Michelle
The number is correct until it hits the first line contains an paid early amount
I come to the conclusion that you need to have a kind of running total for the early and late invoices. If that's the case, then the total values in the ON TABLE SUBTOTAL do not make any sense to me. But that's beside the issue at hand. If you need to have this running total continue even if the date changed back and forth, code the defines as:
IF paydate1 LE DUE_DATE THEN totearlyinvamt + GROSS_AMOUNT ELSE totearlyinvamt
IF paydate1 GT DUE_DATE THEN totlateinvamt + GROSS_AMOUNT ELSE totlateinvamt
Now the total values of the early/late invoice stay intact when the date changes, in stead of being reset to 0.
Hope this helps...
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
November 20, 2008, 01:16 PM
Tracie
That solution worked. Thanks
WebFocus 7703 Windows 7 Output format: HTML, Excel, PDF