almerkaasschieter
July 4th, 2006, 06:01 AM
Hi,
I have a problem with the summary functions; I can't seem to figure out how to calculate certain averages. Here is my test case:
I have departments and employees. Each employee is in a department. An employee has a field called 'haircolor' which is either 'red', 'black', 'brown'. (not to discriminate blonds, but for the sake of example ;-))
I need, in a report, given a certain department_id, the following data:
- The number of people with brown hair in this department
- The average number of people with brown hair in all departments
- The maximum number of people with brown hair in a single department
- The minimum number of people with brown hair in a single department.
All of this in percentages. The main report query retrieves all employees from all departments, so the main query returns things like:
department_id employee_id haircolor
--------------------------------------------------
1 1 brown
1 2 brown
1 3 black
2 4 red
2 5 red
The summary in this case for department 1 would be:
brownhaired blackhaired redhaired
-------------------------------------------------------------------------------
department 1: 66% 33% 0%
average 40% 20% 40%
max 66% 33% 100%
min 0% 0% 0%
In other words, max redhaired is 100% because the maximum number of reds on any department is 100% (department 2). Minimum is 0 because there are departments where none of the colors occur.
As you may see from the example, the goal of this report is to compare the numbers of a department with other departments.
I managed to get the first line working, and the second too, using running totals. This prevents me from adding the max and min however. I have the feeling that I should use the Average, Maximum and Minimum summary functions, however, I can only find examples that use these methods on numerical values, while in this case I need averages on 'if color = red' kinda constructs.
Does anybody have an idea how to built such a report?
I have a problem with the summary functions; I can't seem to figure out how to calculate certain averages. Here is my test case:
I have departments and employees. Each employee is in a department. An employee has a field called 'haircolor' which is either 'red', 'black', 'brown'. (not to discriminate blonds, but for the sake of example ;-))
I need, in a report, given a certain department_id, the following data:
- The number of people with brown hair in this department
- The average number of people with brown hair in all departments
- The maximum number of people with brown hair in a single department
- The minimum number of people with brown hair in a single department.
All of this in percentages. The main report query retrieves all employees from all departments, so the main query returns things like:
department_id employee_id haircolor
--------------------------------------------------
1 1 brown
1 2 brown
1 3 black
2 4 red
2 5 red
The summary in this case for department 1 would be:
brownhaired blackhaired redhaired
-------------------------------------------------------------------------------
department 1: 66% 33% 0%
average 40% 20% 40%
max 66% 33% 100%
min 0% 0% 0%
In other words, max redhaired is 100% because the maximum number of reds on any department is 100% (department 2). Minimum is 0 because there are departments where none of the colors occur.
As you may see from the example, the goal of this report is to compare the numbers of a department with other departments.
I managed to get the first line working, and the second too, using running totals. This prevents me from adding the max and min however. I have the feeling that I should use the Average, Maximum and Minimum summary functions, however, I can only find examples that use these methods on numerical values, while in this case I need averages on 'if color = red' kinda constructs.
Does anybody have an idea how to built such a report?