distinct count of field where > o 1

[jwaldner]

I have a report that has a totals field that is a distinct count of a particular field in a data set. Works great, but I needed to make changes to the query that resulted in an empty row per client for clients that do not have data. [So clients with no data at least list] Great! but this throws off my distinct count. What I need [I guess] is a distinct count if the message Id's that are not zero. Is there a formula that I could insert as a summary that might help me? [The actual value of the field in the query result is null for clients with not data, but i think Chrystal lists it as zero then counts it in the summary]

Thanks!

 

[xxyyzz49]

Quick answer for the description given. Use an OUTER join to ensure a row for all clients.

 

[jwaldner]

The query does return the right data the problem is that the summary totals are off by a count of each customer that has no message because that customer has a row. I need the distinct count because the results can have more than one row for each message and customer

 

[xxyyzz49]

I tried a running total with a distinct count on at date time field and created an evaluation formula to count only times noon or earlier for a particular day. You should be able to use the same technique for the customer and message number.

 

[jwaldner]

not sure what you mean,

I assume you pick the formula on the summary section, but if my field is an integer and nulls for that field are zeros in the report that would increment the distinct count.

If the fields are

msgId histId
1, 1
1, 2
1, 3
2, 4
2, 5
3, 6
3, 7
3, 8

the distinct count is 3 for msgId if one of them was 0 it would be 4 [witch i know you know, just want to be clear] still not sure how to keep a zero from bumping my distinct count. must be a Friday thing.

 

[pmax9999]

Assuming I understand the situation propertly, a Distinct Count summary on a field from the second table (where there is no data for the client in the first table) should work for you.

Cheers
Pete.

 

[xxyyzz49]

[b]F1			F2[/b]
1			3
2			4
2			5
3			6
3			7
0			8
					
Distinct Count		4			
Distinct Count 
Without  Zero		3

To set this up
[ol 1]
[li]Create a running total with field to summarize as F1 column and type of summary of distinct count[/li]
[li]Under Evaluate on the running total set the radio button for use a formula[/li]
[li]Create a formula as below[/li]
[li]Place running total in report footer[/li]
[/ol]

if {Sheet1_.F1} <> 0
then true
else false

That should do it