Duplicate Values
Printed From: Crystal Reports Book — Forum Name: Talk with the Author
Posted By: Odinsisfet — 15 Dec 2009 at 7:19pm
I am creating a report that uses the Sales Database for a class. I am including the InvoiceNum, InvoiceDate, and AmountBilled. I am grouping by Account Number and Invoice Number. When I add the payment amount field to the report I get duplicate invoice number fields with multiple payment amounts. How can I combine these payment amounts to reflect one payment amount? I need to subtract the payment amount from the billed amount to get the amount due field. I cannot do this effectively without combining all payment amount fields. Any help would be appreciated. Example: < ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 12">< name="Originator" content="Microsoft Word 12"> <>
INUM IDATE AMOUNT PAY_AMT 211030518901
00101 12/16/2002 12:00:00AM
3,455.55 3,450.00
00102 1/18/2003 12:00:00AM
222.44 665.00
00103 3/4/2003
12:00:00AM 4,567.00 4,540.00
00202 12/26/2002 12:00:00AM
5,565.00 700.00
00203 1/30/2003 12:00:00AM
5,666.00 756.00
00204 3/7/2003 12:00:00AM
7,777.00 600.00
00204 3/7/2003 12:00:00AM
7,777.00 700.00
00205 3/23/2003 12:00:00AM
222.00 222.00
33347 11/13/2002 12:00:00AM
8,889.00 2,000.00
33347 11/13/2002 12:00:00AM
8,889.00 1,000.00
33347 11/13/2002 12:00:00AM
8,889.00 500.00
44455 2/28/2003 12:00:00AM
4,523.00 4,546.00
44456 3/16/2003 12:00:00AM
2,223.00 500.00
45646 4/17/2003 12:00:00AM
3,334.00 3,000.00
45646 4/17/2003 12:00:00AM
3,334.00 600.00
55544 1/19/2003 12:00:00AM
4,546.00 3,000.00
56342 12/21/2002 12:00:00AM
1,112.00 1,112.00
88844 12/24/2002 12:00:00AM
4,545.00 4,500.00
Posted By: hilfy — 16 Dec 2009 at 10:58am
Since this is for a class, I can't give you the exact answer. However, to point you in the right direction, you need to look at the Summary functions, in particular, SUM. You can use these in formulas in addition to inserting summaries directly into the report.
-Dell
Posted By: Odinsisfet — 16 Dec 2009 at 12:28pm
I already have a summary field that looks at the payment amount by the group invoice number. It will summarize the payment amount by invoice. I still have three fields in my details section with the exact same summarized amount under the same invoice number. How can I get it to only display one amount per invoice? I enev tried averaging the payment amount and then subtracting the sum form that. Still three fields when I only want one! Here is what it looks like now:
The duplicated fields I only want to display once, so my summary fields in my groop footer are accurate.
| INVOICE NUMBER |
INVOICE DATE |
AMOUNT |
Amount Paid |
AMOUNT DUE |
| 00101 |
12/16/02 |
$3,455.55 |
$3,450.00 |
$5.55 |
| 00102 |
1/18/03 |
$222.44 |
$665.00 |
$-442.56 |
| 00103 |
3/4/03 |
$4,567.00 |
$4,540.00 |
$27.00 |
| 00104 |
3/15/03 |
$7,776.00 |
$0.00 |
$7,776.00 |
| 00202 |
12/26/02 |
$5,565.00 |
$700.00 |
$4,865.00 |
| 00203 |
1/30/03 |
$5,666.00 |
$756.00 |
$4,910.00 |
| 00204 |
3/7/03 |
$7,777.00 |
$1,300.00 |
$6,477.00 |
| 00204 |
3/7/03 |
$7,777.00 |
$1,300.00 |
$6,477.00 |
| 00205 |
3/23/03 |
$222.00 |
$222.00 |
$0.00 |
| 23345 |
6/5/03 |
$4,445.00 |
$0.00 |
$4,445.00 |
| 33347 |
11/13/02 |
$8,889.00 |
$3,500.00 |
$5,389.00 |
| 33347 |
11/13/02 |
$8,889.00 |
$3,500.00 |
$5,389.00 |
| 33347 |
11/13/02 |
$8,889.00 |
$3,500.00 |
$5,389.00 |
| 44455 |
2/28/03 |
$4,523.00 |
$4,546.00 |
$-23.00 |
| 44456 |
3/16/03 |
$2,223.00 |
$500.00 |
$1,723.00 |
| 45646 |
4/17/03 |
$3,334.00 |
$3,600.00 |
$-266.00 |
| 45646 |
4/17/03 |
$3,334.00 |
$3,600.00 |
$-266.00 |
| 56342 |
12/21/02 |
$1,112.00 |
$1,112.00 |
$0.00 |
| 77777 |
7/25/03 |
$254.33 |
$0.00 |
$254.33 |
| 88844 |
12/24/02 |
$4,545.00 |
$4,500.00 |
$45.00 |
|
Customer Balance |
$75,789.32 |
29,391.00 |
|
Suppressing is not enough because it still sums the fields when suppressed. Thus, my totals are off. Edited by Odinsisfet - 16 Dec 2009 at 12:30pm
Posted By: hilfy — 16 Dec 2009 at 12:46pm
On the Database menu, have you tried turning on "Select Distinct Records"?
-Dell
Posted By: Odinsisfet — 16 Dec 2009 at 2:04pm
Yes, I did try that. The workaround I found is to place the information from the details section into the group footer. It doesn't duplicate the data then.
Posted By: hilfy — 17 Dec 2009 at 6:22am
But your totals are still off, correct?
If so, you have to create a formula that eliminates the duplicates. It will look something like this:
If ({table.invoice#} = previous({table.invoice#})) and ({table.amount}) = previous({table.amount})) then 0 else {table.amount}
You then do the summary on this formula instead of on the field. This basically looks to see if the previous record has the same invoice number and amount as the current one, which makes the current one a duplicate. If it's a duplicate, then set the value to 0 so that it's not added in more than once.
-Dell
Posted By: Odinsisfet — 17 Dec 2009 at 7:33am
Actually, all of my totals were correct. I did a running total on the Billed amount field, a summary on the Amount paid field and then a formula on the amount due field that took the running total-amount paid summary. They all came out with appropriate numbers. I submitted the assignment and got an A, so i figured it was all good!
Thank you for your help! Edited by Odinsisfet - 17 Dec 2009 at 7:34am
Posted By: kmeistering — 13 Oct 2010 at 5:07am
I have used this solution to help me in a report, and it worked to eliminate the duplicates, however, now I cannot sum the fields because the formula is a second pass formula. I need to eliminate the duplicates and sum the fields - can someone help?!?
Posted By: hilfy — 13 Oct 2010 at 5:18am
Try using a running total instead of a sum. Set it to evaluate based on a formula, using the same logic of looking at the previous record outlined above - using the example above the formula would look something like this:
{table.invoice} <> previous({table.invoice}) or {table.amount} <> previous({table.amount})
You don't need the "If...then..." contstruct because this formula just needs to evaluate to True or False.
-Dell
Posted By: kmeistering — 13 Oct 2010 at 5:29am
If you were right here, I would kiss you.
Posted By: kmeistering — 13 Oct 2010 at 5:45am
One more question - I'm having issues with my first group in the report, the value isn't showing up for the first line of data. I've resorted the data, and every time it is the top row.
Posted By: hilfy — 13 Oct 2010 at 5:56am
Update your formula to something like this:
(PreviousIsNull({table.invoice}) or {table.invoice} <> previous({table.invoice}) ) and (PreviousIsNull({table.amount} or {table.amount} <> previous({table.amount}))
Note where I put the parentheses and that I check for null first - these are important!
-Dell
|
