Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2014
    Posts
    4

    Unanswered: Grouping within reports

    Hello!

    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.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Make the QUERY breakdown the buckets. the Report should just display the query.
    Create the buckets with iff statement...
    ex:

    SELECT
    Name,
    "#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]
    FROM qsCollection
    GROUP BY Name, "#Cases";

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •