Specifying X-Axis categories

  • Thread starter Thread starter ElPresidente
  • Start date Start date
E

ElPresidente

Hi everyone,

One of my reports graphs workload distribution within a given time
range. There are currently 4 employees being given assignments.
However, if someone specifies a date range for 2007 for example,
they'll end up with a graph containing 7+ names because different
people were working on assignments at the time. The result is the
stacked graph becomes unreadable.

I was trying to break up the x-axis categories as Employee1,
Employee2, Employee3, Employee4, and Other. That way the graph stays
manageable regardless of time range.

This is the SQL for the stacked column graph in the report. Can this
be implemented here or should I do it in the underlying query?

TRANSFORM Sum(qryDistributionByTime.[CountOfInvoiceAmount]) AS
SumOfCountOfInvoiceAmount
SELECT qryDistributionByTime.[TimePeriod]
FROM qryDistributionByTime
GROUP BY qryDistributionByTime.[TimePeriod]
PIVOT qryDistributionByTime.[Assignment];
 
I tried doing it in the query as a criteria expression:

IIf(In ("Employee1","Employee2","Employee3","Employee4"),[Assignment])

I just don't know how to make the false statement label the remaining
employees as Other
 
For future reference I figured out the solution. In the underlying
query, I grouped by:

AssignmentAdj: IIf([Assignment] In ("Emp1","Emp2","Emp3","Emp4"),
[Assignment],"Other")
 
Back
Top