COUNT() with Selective Criteria

[SkiBum]

I am struggling to figure out how to design what I think are basic calculations in Crystal Reports. I have read the CR User Guide and bought a reference book, and keep on failing to find the solution.

As an illustrative example, imagine the source database that I'm reporting from contains two tables: DEPARTMENT and EMPLOYEE. For this example, there is a one-to-many relationship: Each DEPARTMENT can have one or more EMPLOYEEs, but an EMPLOYEE must have one and only one DEPARTMENT. The EMPLOYEE table has a foreign key reference to the DEPARTMENT table.

At the top of my report, I want to summarize counts of different attributes for each table:

• Total DEPARTMENTS
• Number of DEPARTMENTS that are currently hiring.
• Number of DEPARTMENTS in the Main Street office.
• Total EMPLOYEES in all departments.
• Number of Male EMPLOYEES in all departments.
• Number of Female EMPLOYEES in all departments.
• Number of Telecommuting EMPLOYEES in all departments.

This would be very simple to do if I could just go into the Formula Workshop and create Count() or DistinctCount() functions with selective criteria. For example, DistinctCount({EMPLOYEE.TELECOMMUTE_FLAG} = ‘Y’). But I haven’t figured out how to make this work.

I can’t use Record Selection (Selection Expert), because I need the whole data set to count total departments and total employees. I can’t figure out how to make Group Selection work for me, because if I start grouping and subgrouping then a higher level group selection limits the number of records available to the subgroups. I also could not figure out how to use Summaries, because they don’t support selective criteria as well.

My solution (which doesn’t feel like the way I should do this) is that I’ve created numerous SQL queries within the Database Expert containing COUNT(*) and WHERE clauses to achieve these calculations. Intuitively this feels inefficient, because I’m querying the same databases over and over, instead of bringing the whole joined dataset into CR and performing the calculations within the report.

Having successfully achieved the top section of my report, I now want to list the following details for every department:

• DEPARTMENT.NAME
• Number of EMPLOYEES in the department.
• Number of Male EMPLOYEES in the department.
• Number of Female EMPLOYEES in the department.
• Number of Telecommuting EMPLOYEES in the department.

It seems obvious that I would group by DEPARTMENT.ID as the basis for each detail record. But then how do I “join” each of my individual calculation queries to get the details such as number of male employees in the Accounting department?? I have tried restructuring my “COUNT(*) / WHERE clause” queries into retrieving a record set using the same selection criteria that also contains the DEPARTMENT.ID, but when I join the results of this query to the original DEPARTMENT table, CR stops working.

I know that this is a lot of detail, but hopefully you get the gist of what I’m trying to accomplish. I really don’t know how to proceed.

Thanks,
Bill

[SkiBum]

Let me simplify my example using only one table = EMPLOYEE.
Here's the table definition:

EMPLOYEE.ID
EMPLOYEE.GENDER_CODE
EMPLOYEE.MANAGEMENT_FLAG

Let's say I want to report three different counts:

>> Total number of Employees
>> Total number of Male Employees
>> Total number of Management Employees

Is there any way that I can select just the EMPLOYEE table in Database Expert, and then create Count() functions with selective criteria within the report? (ie. EMPLOYEE.GENDER_CODE = 'M')

I don't believe I can use Group Selection with Summaries to accomplish this, because GENDER_CODE and MANAGEMENT_FLAG are not mutually exclusive.

Thanks,
Bill

[jggtz]

I think that you can get what you want using several Running Total fields with differents conditions according to your requirements
Group the data by Department
After you finish with your tests, don't forget to Suppress all your sections except Report Footer where you are going to insert the Running Totals fields

[SkiBum]

Here's what I learned from various sources, in case others are looking for the same solution:

There are different ways to calculate the count:

1) use sql expressions
2) use running totals
3) use summaries
4) use custom formulas

I was successful by creating a formula like this:

if ({EMPLOYEE.GENDER_CODE} = 'M')
then 1
else 0

Then I created a Summary on this formula (sum, not count) which gives the number of male employees.

-Bill