Crosstab - counting once per group

[hpl2001]

Hi:

I'm very new at crosstabs and can't quite figure out how to make this work. I want to count the number of times various text values turn up in my report, but I only want to count one per grouping. So for example, I have a report that is grouped by Incident ID. For each incident, I want to look at the value of the associated text field and add it to the count of how many times that text field displays. At the moment, if I try to count instances of the various text fields I get an incorrect count because it's counting each time it appears in the dataset, i.e. more than one instance may occur for each incident number. How do I count once per incident number only...

I hope this makes sense - I'm having trouble describing it.

Thanks.

Holly

 

[kenhamady]

First create a formula called Null and save it empty.
Then create a second formula something like this:

if "text" in {field} //whatever condition you are trying to count
then {Incident ID}
else {@Null}

Use this as the summarized field in your cross-tab and do a distinct count. If the incident is a numeric field you will need the last line to be:
else Val({@nul})

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/