I'm trying to generate a sales by rep report. Currently, I am able to get a report to print with the employee name, number of orders that fall within a certain date, and that fall within a certain category (One on number of items in the order, and one on the cost of the order, each one within a single set of values). What I would like is the report to kick out different buckets ($1-50, $51-100, $101-500, etc) on the same report.
I already have the bucket limits set up in a shiny new form (They want it dynamic, so the variable pulls from the table). What I can't figure out is how to make it display all those buckets in the report.
Make the QUERY breakdown the buckets. the Report should just display the query.
Create the buckets with iff statement...
"#Cases" AS Fld,
Sum(IIf([Days]>=0 And [Days]<=30,1,0)) AS [0_To_30 days],
Sum(IIf([Days]>=31 And [Days]<=60,1,0)) AS [31_To_60 days],
Sum(IIf([Days]>=61 And [Days]<=90,1,0)) AS [61_To_90 days],
Sum(IIf([Days]>=91 And [Days]<=120,1,0)) AS [91_To_120 days],
Sum(IIf([Days]>120,1,0)) AS [Over_120 days]
GROUP BY Name, "#Cases";