What's the best way to count?

[Belky]

Hi,
I have a field which has many status's in it. For the one person they may have 6 status's (tasks with status's) of Complete and 5 status's of Tentative etc. I need to display ;

CT NAME No. Tasks complete No. Tasks Tentative
Belinda 3 5

etc.
What is the best way to do this as it is the one field that I need to extract the counts of these status values?
Sorry this may be simple but advice would be greatly appreciated.

 

[seema3]

Select count(tasks) from table where status == 'Complete' for user.
Select count(tasks) from table where status == 'Tentative' for user.
Was this what you reqd.

 

[Naith]

Group by Name.

In the group header:

WhilePrintingRecords;
NumberVar Complete := 0;
NumberVar Tentative := 0;

//Suppress that formula.

In the details section:

[code]WhilePrintingRecords;
NumberVar Complete;
NumberVar Tentative;

If {Status} = 'Complete' 
Then Complete := Complete + 1
Else Tentative + 1;

This assumes that all your statuses can either only be 'Complete' or 'Tentative'. This may need amending, because I'm afraid I don't see how the "6 statuses" and "5 statuses" translates into your example.

Suppress the details section.

In your group footer

WhilePrintingRecords;
NumberVar Complete;

and another formula in the same section:

WhilePrintingRecords;
NumberVar Tentative;

Naith

 

[Belky]

So where am I do put these
Select count(tasks) from table where status == 'Complete' for user.
Select count(tasks) from table where status == 'Tentative' for user.

Do I create these in formula's? If so could you help me out a little with the crystal syntax please.
thanx

 

[Belky]

Thank you Naith,
I tried your way and it seems to have worked however another question. As I'm having some issues brining back data for the one date at the moment in my stored procedure, if I have for instance;
NAME DATE COMPLETE INCOMPLETE
Belinda 31-OCT 12 3

Is this how it will display? At the moment its displaying

NAME DATE COMPLETE INCOMPLETE
Belinda 31-OCT 1 0
1 -NOV 2 0
2 - Nov 3 0.

 

[Naith]

Belky,

At the moment, I guess you're grouping by person, and then by a date field on a 'daily' occurence. Go into your Change Group option, and set it to 'weekly' - or 'monthly', if that's more appropriate.

Naith