Another COUNT Question 1

[elizabethj]

Hi, I'm fairly new to Crystal Reporting (and very new to this forum) so please go easy on me.

I'm trying to get a count of certain items within a single field. This field (Status) can be equal to one of four things: Open, Closed, Cancelled, On Hold. I want to get a count of the total Opens, Closed, etc... (but don't want the Grand Total). I'm sure this is a very simple thing, but I've looked all over and just can't seem to figure it out. Any help is very much appreciated.

Thanks!
Elizabeth

 

[tonyp145]

I'm pretty new myself so this is fun finding a question I can actually answer You want to group on that field. Start the report expert, connect to your database, skip to the group tab and select the Status field. In the Total tab select Status again, CR defaults to "max" change it to "count" and hit finish. Switch to design mode, right-click on the details section and suppress it. I usually suppress the group header too. Switch back to preview mode you should have just your 4 groups and number of records. Hope that helps.

 

[elizabethj]

Ok, that put me a little closer to what I want. However, I want something that looks like this: (hopefully this will format on here ok)

name open cancelled closed on hold total
---------------------------------------------------------
person1 2 5 3 0 10
person2 1 3 6 2 12

etc.. Where Open, Cancelled, Closed, and On Hold are all in the Status field. I want to have it count the number of each item for a single person, and then total the 4 items for each person.

I hope this makes sense. Thanks for the help! I'm getting closer!

-Elizabeth

 

[kenhamady]

Try a cross-tab by person by status. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[AlphyraTSG]

Hi there, I am pretty new at this as well, but once I have created the reports my company wants, they will send me on a course!!!

Anyway, the problem you talk about is one I had, I found this worked for me....

1. Create a report based on the right table.
2. Create a group base on 'person'
3. you can leave the details blank, or add the fields, no matter as you need to supress the section anyway.
4. Place the 'person' field in the 'person footer.
5. Create a formula like:

if {status} ="open" then 1 else 0

6. create more formula as above, but replace 'open' with your other status codes.
7. Now pop each formula in to the details section, then use that to create a sub or grand total which sums each formula
8. To get the grand total, simply count the status field
9. Now just supress the group header and the detail sections.


This works great as long as you dont have any conditions. I am working on a report much like yours, each person has a code, starting in 'I'(internal staff) or 'E' (external) and each job could have multiple records with different fields. I need to count the unique jobs only if owned by internal staff! This is made harder as I have a number of staff locations and I need to produce this count grouped by location, not person!

Anyway, hope this helps

Tony

 

[sheelashankar]

Hi Tony,

I am also new to Crystal reports ( started yesterday) and I am also developing a report similar to what has been described earlier and I was wondering if you could send me the Crystal reports rpt file with the group , and the summary fields etc.
You could email it to me at sheela.shankar@ivorygroup.com

Thanks
Sheela

 

[kenhamady]

This technique is called a manual cross-tab.
It is described in my FAQ on common formulas in the general forum.

In most cases, however, CR can build the cross-tab table for you. You should try the menu options Insert-Crosstab. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/