Totaling

[JennyO]

I'm rather new to Report Building in Cognos, and have taught myself through trial and error.

I'm trying to figure out who to run a list report that does a count in two different ways.

Count 1: Number of employees by position.
Count 2: Number of employees by department.

I have the report set to group by position within the group by department. What I keep getting is the correct count for position, but then I get a total for ALL departments, when I just want a total/count of the number of employees by department as well as by position.

So if Department A has 4 different positions, and each of those positions have 5 employees, I want the report to tell me:
Position A (count) 5
POsition B (Count) 5
and so on
and then I want it to tell me:
Department A (count) 20
Department B (count) 35
and so on

How do I accomplish this?

[tjohnson3050]

There is an argument for the count (or any aggregation) that let's you specify the level of the aggregation.  That argument is 'FOR'.

For example:

count([employee id] for [department])

[JennyO]

It's still not doing what I want it to do.

When I enter the argument specified, it's only putting it at the summary line, instead of after each department. And then when I do it for the position it's doing it at the department level.

I must be missing something but for the life of me I can't figure it out.

[bi4u2]

I think you want to group your list report by Department first, then by Position. Then add summary calculation on the employee column and instead of selecting 'Total' select 'Count'. This should give you a footer row for Position then Department and the calculution will be 'Count'.

[JennyO]

Thanks for your help. I finally got it to do what I wanted. :D

[JennyO]

Last Question

I got the counting to work, but I would love the resulting count to display in the colum it is counting. i.e. Count for Department, in the same column as the department. Right now, it is displaying in the column I'm using for the count. ie. count([Position]for[Department]) is showing in the position column, when I would really love if it showed in the Department column.