cancel
Showing results for 
Search instead for 
Did you mean: 

Count() function with selective criteria?

Former Member
0 Kudos

I'm struggling with what I would expect to be a fundamental reporting concept in CR.

Suppose I have the following EMPLOYEE table:

u2022 EMPLOYEE.ID

u2022 EMPLOYEE.GENDER_CODE

u2022 EMPLOYEE.MANAGER_FLAG

I need to generate a statistical summary report containing the following:

u2022 Total number of Employees

u2022 Number of Male Employees

u2022 Number of Management Employees

This would be easy if I could just use a Count() function in the Function Workshop which

contained selective criteria. For example: Count ({EMPLOYEE.GENDER_CODE} = 'M')

But I can't figure out how to do this without getting a CR error message.

Record Selection doesn't work, because I need the whole data set.

Group Selection with Summaries doesn't work, because the gender and management

attributes are not mutually exclusive.

One solution that seems awkward to me is to create additional SQL commands in the

Database Expert using COUNT(*) and WHERE criteria to get the number of Males

and number of Managers. But I have to believe that there is a better way. Plus this

approach causes problems elsewhere in my report.

Am I missing something?

Thanks,

Bill

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Thanks Raghavendra!

The good news is that I was able to create a "1 or 0" formula and then sum the resulting values.

The bad news is that I'm only able to get this to work for part of my report.

I am joining two tables. As an example COMPANY and EMPLOYEE.

I can use "1 or 0" formulas on all of the employee statistics.

But I cannot do the same for company statistics, because the number of company records being assigned a 1 is being inflated by the join between the two tables.

I have achieved a successful result by using Running Total Fields at the end of the report.

But I want to place these summary statistics at the begining of my report. (Such as Total Companies in the Western Region.)

I tried to use "COMPANY.NAME = previous(COMPANY.NAME)" logic in my function, but then I was not allowed to summarize it.

Any ideas?

Thanks,

Bill

Former Member
0 Kudos

You can use a global array, as described in [this thread|;.

HTH,

Carl

Answers (1)

Answers (1)

Former Member
0 Kudos

you have different ways to calculate the count

1) use sql expressions

2) use running totals

3) use summaries.

try creating the formula like this

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

1

else

0

Now insert summary on this formula which gives the count fo male employees.

regards,

Raghavendra.G

Edited by: Raghavendra Gadhamsetty on Nov 11, 2009 12:14 AM

Former Member
0 Kudos

P.S., you will want to SUM() Raghavendra's formula, not COUNT() it...

HTH,

Carl