Print Page | Close Window

Eliminating duplicates

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8219
Printed Date: 27 Apr 2024 at 12:12pm


Topic: Eliminating duplicates
Posted By: 9morty9
Subject: Eliminating duplicates
Date Posted: 03 Nov 2009 at 10:46pm
Hi,
 
I have a report that uses 3 tables.  1st table is the financial accounts master, 2nd table is the summary financial actual information by by year and period and the 3rd table is the summary financial budget information by by year and period.  I am linking the 1st and 2nd table by 'account index' and the 2nd and third tables by 'account index' and 'period id'.  I am trying to create columns for Month Actual, Month Budget, YTD Actual and YTD Budget.  My problem is that data stored in the 2nd and 3rd tables store the information by period and account.  I am grouping on the account number.  At the detail level I am getting duplicates on amounts by account (i assume because the accounts are duplicated for periods in the 2nd and third tables).  Can anyone suggest how I might remove the duplicates???   I have tried suppressing the duplicates at the field level but am unsure how or if I can suppress the duplicates when I sum at the group level. 


Replies:
Posted By: lockwelle
Date Posted: 04 Nov 2009 at 6:41am

if you suppress values in the report, you will need to use either a formula or a running total.  I use formulas, DBlank uses running totals.  In the formula, you would add the values that are not suppressed and display them in the footer.

Typically, formulas for summing come in groups of three. 1 to reset usually in the group header, 1 to display usually in the group footer, and 1 to increment usually in the detail section.
 
HTH

Posted By: 9morty9
Date Posted: 04 Nov 2009 at 1:37pm
Thanks for that information.  Are you able to give me an idea of what the formula might look like.  I am not sure how to include the supressed function in the formula. 

Posted By: lockwelle
Date Posted: 05 Nov 2009 at 6:08am
the formula for incrementing would look something like:
shared numbervar aVar;
 
if {table.fieldx} (not a duplicate logic) then
  aVar := aVar + {table.fieldy};
 
""//to hide the calculate so that you can place the formula anywhere on te report.
 
The hard part is defining the (not a duplicate logic)


Print Page | Close Window