I
ilia
I'm trying to work out this problem, and wanted to see if others have
come up with a solution.
What I have is a pivot table report in Excel 2007, which shows a pay
period and amounts paid in overtime for each employee. The list is
grouped by department. Here is an example:
Dept Name OT Amount
200000 Smith, John $1,900.00
Jones, Jack $525.00
Busch, David $1,275.00
200000 total $3,700.00
There are 9 non-exempt departments. What I want to do is set up a
data bar that shows who gets paid most in overtime, to assist managers
in controlling overtime scheduling. Here is the problem: two of the
departments have built-in (i.e. regularly scheduled) overtime, and so
they will normally have this. Other departments, on the other hand,
should not be using any overtime at all - their amounts are generally
very low (like $50 per employee, and even less on average).
Therefore, I cannot apply the formatting rule to the entire pivot
table (that would negate the benefit of visual comparison for non-
overtime departments). The alternative - doing it the old way - is
highlighting each range (for each department separately) instead of
using a pivot table rule. One benefit of this is, of course, that I
can use different color bars for different departments. However,
because staffing changes, this creates a lot of manual work, which
doesn't bother me but a pain for our Controller and CFO.
So... what I'm trying to do is:
1. Use a table-wide formatting rule for data bar
2. Base the "shortest bar" on a number generated by a formula along
the lines of GETPIVOTDATA("Min of OT Amount",$A
$4,"Department","200000")
3. Base the "longest bar" on a similar formula for maximum.
Right now I'm struggling with making this at least a semi-automated
process. Anyone have ideas or suggestions? Perhaps I should be using
a shortest/longest number value using a database function from the
source table instead? Of course I could always generate a separate
report for each department, but at this point it's an academic
curiosity.
come up with a solution.
What I have is a pivot table report in Excel 2007, which shows a pay
period and amounts paid in overtime for each employee. The list is
grouped by department. Here is an example:
Dept Name OT Amount
200000 Smith, John $1,900.00
Jones, Jack $525.00
Busch, David $1,275.00
200000 total $3,700.00
There are 9 non-exempt departments. What I want to do is set up a
data bar that shows who gets paid most in overtime, to assist managers
in controlling overtime scheduling. Here is the problem: two of the
departments have built-in (i.e. regularly scheduled) overtime, and so
they will normally have this. Other departments, on the other hand,
should not be using any overtime at all - their amounts are generally
very low (like $50 per employee, and even less on average).
Therefore, I cannot apply the formatting rule to the entire pivot
table (that would negate the benefit of visual comparison for non-
overtime departments). The alternative - doing it the old way - is
highlighting each range (for each department separately) instead of
using a pivot table rule. One benefit of this is, of course, that I
can use different color bars for different departments. However,
because staffing changes, this creates a lot of manual work, which
doesn't bother me but a pain for our Controller and CFO.
So... what I'm trying to do is:
1. Use a table-wide formatting rule for data bar
2. Base the "shortest bar" on a number generated by a formula along
the lines of GETPIVOTDATA("Min of OT Amount",$A
$4,"Department","200000")
3. Base the "longest bar" on a similar formula for maximum.
Right now I'm struggling with making this at least a semi-automated
process. Anyone have ideas or suggestions? Perhaps I should be using
a shortest/longest number value using a database function from the
source table instead? Of course I could always generate a separate
report for each department, but at this point it's an academic
curiosity.