Count with conditions

[nlaliberte]

I'm using Crystal XI and it's being fed from a SQL Server via an OLE DB connection.

What I have is a data set that has a list of companies and the amount of dollars in their account. There are multiple types of accounts, therefor a company may be listed on more than one record.

What I need is a Distinct Count of companies with an account greater than 0.

Normally I would write a formula that tagged records with an account greater than 0 with a 1, and then take a sum of that formula. However since this is a Distinct Count, this will not be correct if the same company has more than one account greater than zero.

Is there a way to put conditions on the COUNT function in order to do this?

 

[lbass]

You can do this one of two ways. You can insert a running total that does a distintcount of companies, with an evaluation formula of:

{table.acctamt} > 0

Or you can create a formula {@null} by opening and saving a new formula without entering anything. Then create a formula:

if {table.acctamt} > 0 then
{table.companyID} else
{@null}

Then insert a distinctcount on this formula. This assumes the ID field is a string. If a number, the default should be:

tonumber({@null})

-LB