Sum total of distinct counts of employees grouped by department

[flaaquileo]

My report is grouped by department. There can be many departments. The report contains a distinct count of the number of employees within each department. I need to sum those distinct counts. For example: department A has 8 distinct employees; department B has 20 distinct employees, department C has 10 distinct employees, etc. I need to sum the distinct count of each department and get a grand total. In this example, it would be 38. Employee x may counted in both departments A and C.

I can't do a distinct count on all employees, since some employees may be counted in more than one department.
I tried doing a formula with the following and got the message: 'this field cannot be summarized'...

Sum(DistinctCount ({uv_OVERTIME.EMPLID}, {uv_OVERTIME.DEPT}))

Has anyone done this or know if it can be done?

 

[PatriciaObreja]

Please take a look at this topic:

http://www.tek-tips.com/viewthread.cfm?qid=1104233&page=1

You need to use variables.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[flaaquileo]

Thanks PatriciaObreja. It worked perfectly!!