Selecting Unique Records

building reports crystal reports
1

I have a report that contains multiple IncidentIDs. I can use the Distinct Count function to add the IncidentID only (and obviously) if it is Distinct. But here’s my dilemma. I am using a formula to determine how many records have a method reported by telephone:

If {METHOD_REPORTED}= “TELEPHONE”
then 1
else 0

The above formula is still counting every instance where the method reported=telephone - even when there is a duplicate incidentID. Using Select Distinct Methods is not working Please help me! I’m desperate! Thank you!

Gingerly (BOB member since 2011-03-24)

2

Are you distinct counting the incidentID’s or some other field? It seems it should work.

Anyhow if the report is sorted by IncidentID then you might check to see if previous(IncidentID) <> IncidentID as a Evaluation formula.

kevlray :us: (BOB member since 2010-06-23)

3

You could also do something like this:

If {METHOD_REPORTED}= “TELEPHONE”
then {IncidentID}

With no “Else” on the statement. Then do a distinct count of these incident ID’s to get your total (you may have to subtract 1 from the total to get the right count, but I don’t think so.)

-Dell

hilfy :us: (BOB member since 2007-04-16)

4

I was Distinct Counting the IncidentIDs but I’m not sure how I can use that population for the rest of my formulas. The Distinct count itself works on the field but how do I make sure that my formulas are being calculated on the Distinct Incident IDs and not on all of the Incident IDs, many of which are duplicates? Thanks for your response?

Gingerly (BOB member since 2011-03-24)

5

That sounds like it may be it! Trying it now - thank you so much!

Gingerly (BOB member since 2011-03-24)

6

I’m still missing something. Is there a way to use the result of a Distinct Count field in a formula? One of my formulas is a percentage of how many incidents were reported by telephone and when I use:

IncidentsByTelephone/TotalIncidents

it is including all of the duplicate IncidentIDs.

Gingerly (BOB member since 2011-03-24)

7

I would use a couple of formulas:

{@Telephone Incidents}
If {METHOD_REPORTED}= “TELEPHONE”
then {IncidentID}

{@PctTelephone}
If DistinctCount({IncidentID}) > 0 then
DistinctCount({@Telephone Incidents})/DistinctCount({IncidentID})
else 0

Is this what you have that’s not working?

hilfy :us: (BOB member since 2007-04-16)

8

Sure sounds like that would work. The caveat here is that I only need to count Incident IDs with a status of OPEN. That’s why I did a subreport and first only included the opened Incident IDs then I did the check for Telephone. Thanks for your input.

Gingerly (BOB member since 2011-03-24)