Field cannot be summarized

[mwordenu8]

thread767-805592
I want to count for each user tickets/records entered. I need the report to also show those users that did not issue any tickets in the period so the count or distinct count would be 0 or a Null value.

How do I get Crystal to return a value of 0 where no records exist to be counted for a user?

 

[mwordenu8]

I forgot to mention I have attempted to follow other posts that detail using the following two formulas:

@NullTestIf IsNull({Customer}) then 1 else 0

@DistinctCountIf Sum({@NullTest}) >0 then DistinctCount({Customer})-1 elseDistinctCount({Customer})

When I save the @DistinctCount formula I recieve the message "Field Cannont be Summarized" referring to the @NullTest formula field.

Ideas?

 

[lbass]

What version of CR are you using?

-LB

 

[Madawc]

First, what's the data and how are you handling it? If it is something like 'User' and 'Tickets', you need a left-outer to get users without tickets.

For counts, your method is needlessly complicated. Group by User. Do a summary count of Users. Do a Running Total that requires that at least one Ticket to exist (or whatever it is for you).

The use of Crystal's automated totals is outlined at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[mwordenu8]

I'm using Crystal Reports IX.

I've done lots of grouping and summaries to count the records present. Displaying the users that don't do much work, that have no records to be counted is where I'm failing.

 

[lbass]

There are a number of approaches to this. If there weren't that many users, you could use running totals specific to each user in the report footer. Assuming that is not the case, you could do as Madawc suggested, and use a left join FROM a users table TO a tickets table, with no selection criteria on fields from the tickets table.

The cleanest solution would be to use a command where you use do the left join but add the tickets criteria in the FROM clause instead of the WHERE clause. This would preserve the left join so that all users showed up while only those tickets that met the criteria would. It would look something like this:

Select user.ID, ticket.no
From user left outer join ticket on
user.ID = ticket.user_ID and
ticket.date >= {?Start} and
ticket.date < {?End} and
ticket.location = 'Boston'

Notice there is no where clause. You might have to put parens before user.ID and after 'Boston' depending upon your datasource, and the punctuation/syntax depends upon that too.

-LB