Counting distinct with conditions

[redlynne]

Hello, I have a problem with crystal reports' distinctcount,
so I'm looaking for an alternative approach.

(grouped under username)
(fieldnames) misseddeadlineflag Status Name

(crystal data) 1 Daily
1 Daily
1 Daily
0 Weekly
1 Monthly
What would I do to get the number of missed deadlines (flag is 1)for each distinct status name? I should have a value of "2" only for the 1-Daily and 1-Monthly combinations.
These data are grouped per username.

 

[GJParker]

Create a 2nd group for status name and add your summaries in there.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[akabatman2]

I would recommend performing your SQL on the server rather than using Crystal's "Select Distinct" function.

In the database:

Select Distinct
Misseddeadlineflag
, StatusName
From TableName /*Whatever table/tables the fields are from*/

Now, I don't know if you are showing all the data involved in your above example, as you've mentioned that there is a 'grouping' on username.

If you have something like:

MDLFLAG StatusName UserName

1 Daily Bob
1 Daily Ish
1 Daily Yuri
0 Weekly Uncle
1 Monthly Guy

If you try to do a Select distinct on this, you will return all rows, because each Record(consisting of the 3 fields MDLFLAG, StatusName, and UserName) is Distinct.

In order to only return:

1 Daily
1 Monthly

You would have to Select Distinct on only the MDLFLAG and StatusName. As soon as you add the UserName, the whole record becomes Distinct as a record, that's logic for you.

What is in the dataset you are returning is the issue.

Hope that helps.

Tom

 

[kenhamady]

I don't think you need a distinct count, I think you need a sum for each group. Group by status, and sum the flag (if it is numeric). if this flag is really a boolean, and won't sum correctly, write a formula that says:

If {flag}
then 1
else 0

And then sum the formula

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[redlynne]

thanks guys you provided me with a lot of insights on how to approach this problem. Thanks again.....