none
Report builder 3 group by and order by question RRS feed

  • Question

  • Hello

    I am working on a ticket tracking system and need to supply a report that displays the top ten types of tickets based on number of tickets per subject.

    I wrote query that uses Top, count, group by and order by desc that does what I want.

    But I am not sure how to convert that into a report that does what I want, as I have to remove the count and group by and let the report do the counting.

    I tried to remove the count and group by from the query but left the top and order by and than let the report do the counting and grouping but the sort did not work correctly

    this is what my query looks like which works, but not sure how to get the same results in report builder 3, thanks in advance for any assistance.

    SELECT so.serv_off_n 'Service Offering Name'
          ,COUNT(i.incident_id) 'Ticket Count'
    FROM incident i -- start with ticket data
      INNER JOIN inc_data id          ON id.incident_id         = i.incident_id          -- link ticket to incident data
      LEFT OUTER JOIN serv_off so   ON so.serv_off_id         = id.serv_off_id         -- link to Service offerings
    GROUP BY so.serv_off_n
            ,s.sla_n
    ORDER BY 3 DESC


     

    Wednesday, January 16, 2019 4:11 PM

All replies

  • Hi Chucky99

    You could just the query you have for the dataset query .(remove the group by and order by )

    SELECT so.serv_off_n 'Service Offering Name'
          ,COUNT(i.incident_id) 'Ticket Count'
    FROM incident i -- start with ticket data
      INNER JOIN inc_data id          ON id.incident_id         = i.incident_id          -- link ticket to incident data
      LEFT OUTER JOIN serv_off so   ON so.serv_off_id         = id.serv_off_id         -- link to Service offerings

    Then add the group for the Tablix , and set the sorting for the group.

    If you still have issue about your question , you could offer the detailed report structure ,requirement reports screen shot and the data sample to us for more further research.

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    • Proposed as answer by Eric Liu001 Tuesday, January 22, 2019 6:07 AM
    Thursday, January 17, 2019 5:58 AM