Technical Questions: Avoiding Duplicates in Sum/Count

Avoiding Duplicates in Sum/Count

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7077
Printed Date: 06 May 2024 at 6:37am

Topic: Avoiding Duplicates in Sum/Count

Posted By: davidj028
Subject: Avoiding Duplicates in Sum/Count
Date Posted: 17 Jul 2009 at 10:27am

Hey everyone. I am new to Crystal Reports and have a question.

I will try to explain it the best I can:

I have 2 tables.

Probsummarym1 is the Record which contains a primary key NumberPRGN.

Activitym1 is linked to this table via NumberPRGN, but it is not the primary key for Activitym1.

(I am also using the table IncidentsA1, but it is not causing the problem, because there is a 1 to 1 relationship with Probsummarym1)

Below is my Formula to pull in the records I need:

If
((((Date({PROBSUMMARYM1.OPEN_TIME})< mailto:%7b@Date - {@Date 11}) and
{PROBSUMMARYM1.ASSIGNMENT}="SECURITY" and
{INCIDENTSA1.ASSIGNMENT}="SECURITY" ) and
{ACTIVITYM1.TYPE}="Closed") and
{PROBSUMMARYM1.CLOSE_TIME}>{@Date 11} and
{PROBSUMMARYM1.CLOSE_TIME}< mailto:=%7b@Date - ={@Date 12}) then 1 else
If
(({ACTIVITYM1.TYPE}="Reassignment"
and {ACTIVITYM1.DESCRIPTION} like '*to SECURITY*') and
{INCIDENTSA1.ASSIGNMENT}<>"SECURITY" and
(((Date({ACTIVITYM1.DATESTAMP})< mailto:%7b@Date - {@Date 10}) and
({PROBSUMMARYM1.CLOSE_TIME}>{@Date 10}) and
(Date({PROBSUMMARYM1.CLOSE_TIME})< mailto:=%7b@Date - ={@Date 11}) and
{PROBSUMMARYM1.ASSIGNMENT}="SECURITY"))) then 1 else 0

I then have a formula that Sums this formula.

This formula pulls in 62 records accurately. However, because a few of Records have multiple identical activities, these records get counted twice. There are actually only 58 records with unique NumberPRGN values.

I need to be able to count the record only once. That is, I should not have multiple records with the same NumberPRGN.

Once I can get a formula to accurately give me the number 58, that value will need to go into a chart.

I am using Crystal Reports 9. I would really appreciate any help! Thanks!

Replies:

Posted By: DBlank
Date Posted: 17 Jul 2009 at 10:51am

you can use a Running Total in a number of ways. Here is one way.

Do a Distinct Count of NumberPRGN

Evualuate as a formula (use your 0-1 formula from above but exclude the if then):

((((Date({PROBSUMMARYM1.OPEN_TIME})< mailto:%7b@Date - {@Date 11}) and
{PROBSUMMARYM1.ASSIGNMENT}="SECURITY" and
{INCIDENTSA1.ASSIGNMENT}="SECURITY" ) and
{ACTIVITYM1.TYPE}="Closed") and
{PROBSUMMARYM1.CLOSE_TIME}>{@Date 11} and
{PROBSUMMARYM1.CLOSE_TIME}< mailto:=%7b@Date - ={@Date 12}) then 1 else
If
(({ACTIVITYM1.TYPE}="Reassignment"
and {ACTIVITYM1.DESCRIPTION} like '*to SECURITY*') and
{INCIDENTSA1.ASSIGNMENT}<>"SECURITY" and
(((Date({ACTIVITYM1.DATESTAMP})< mailto:%7b@Date - {@Date 10}) and
({PROBSUMMARYM1.CLOSE_TIME}>{@Date 10}) and
(Date({PROBSUMMARYM1.CLOSE_TIME})< mailto:=%7b@Date - ={@Date 11}) and
{PROBSUMMARYM1.ASSIGNMENT}="SECURITY")))

Reset as NEVER.

Place in Report Footer (RTs do not work in headers).

With this process you do not need the other formulas at all.

Posted By: davidj028
Date Posted: 17 Jul 2009 at 11:04am

That's EXACTLY what I need.

Thanks a bunch!

Posted By: davidj028
Date Posted: 17 Jul 2009 at 1:03pm

I'm afraid I spoke to soon... :D

I am getting the correct count values now. However, I'm having trouble getting this onto a chart.

Previously, when I was using the formulas, in the chart expert I would select For All records and then add each formula to the Show Value(s) box.

However, when I select the RT, the little button to add it to the Show Value(s) box is greyed out.

If I change For all records to For each record, it will let me add them to the box, but when i then run the report, it runs extremely slow and the chart is never generated.

Could you help me get this data onto a graph?

Thanks!

Posted By: DBlank
Date Posted: 17 Jul 2009 at 1:08pm

Are there any groups in this report?

What kind of Chart are you using?

Posted By: davidj028
Date Posted: 17 Jul 2009 at 1:09pm

There are no groups.

I am using a Bar graph.

Posted By: DBlank
Date Posted: 17 Jul 2009 at 1:19pm

In your DATA tab in the Chart Expert use "On Change of" then add in either a field that is allways the same for all rows in your records or use a formula field as a string that does this for you...Add a formula field as "BarChart Name" or whatever...as "PRGN Count" ...or whatever text you want.

Your RT will now be available for use in teh show value.