1 Reply Latest reply on Feb 5, 2014 9:42 AM by smitha

    SSRS Top 10 Query

    Apprentice

      Morning all,

       

      Been having a bit of an issue creating a report that displays the Top 10 Call Originators based on Site Location.  I can get the query to return most of the information but not when I add the WHERE clause.  The data is not correct for the date range. 

      SELECT     i.ID, i.Title, i.Originator, i.Status, i.Assignee, i.CreationDate, i.Site, ti.OrigCount

      FROM         vw_Incident_Site AS i JOIN                         

                           (SELECT     TOP (10) PERCENT Originator, COUNT(Originator) AS OrigCount                            

                               FROM          vw_Incident_Site                            

                               GROUP BY Originator                            

                          ORDER BY OrigCount DESC) AS ti ON i.Originator = ti.Originator

      WHERE (i.site = 'Site Name') and (CreationDate >= @Start) AND (CreationDate <= @End)

      ORDER BY ti.OrigCount DESC, ti.Originator

       

      I am using a view that I have built that returns all incidents based on the colums you want...

       

      Also, when this is displayed in the Report the ordering is totally messed up, not displaying in order 1-10 as per the query.

       

      Any help would be appreciated

        • 1. Re: SSRS Top 10 Query
          Apprentice

          Request -

          SELECT DISTINCT                       vw_rpt_requests_1.ID, vw_rpt_requests_1.Title, ti.Originator, ti.OrigCount, vw_rpt_requests_1.Status, vw_rpt_requests_1.Assignee, vw_rpt_requests_1.CreationDate,                       vw_rpt_requests_1.usr_location AS Site

          FROM         (SELECT     TOP (10) Originator, COUNT(Originator) AS OrigCount                       

          FROM          (SELECT DISTINCT ID, Originator                                               

          FROM          vw_rpt_requests                                               

          WHERE      (CreationDate >= @Start) AND (CreationDate <= @End) AND (usr_location = 'Site Name')) AS x                       

          GROUP BY Originator                       

          ORDER BY OrigCount DESC) AS ti INNER JOIN                       vw_rpt_requests AS vw_rpt_requests_1 ON ti.Originator = vw_rpt_requests_1.Originator WHERE     (vw_rpt_requests_1.CreationDate >= @Start) AND (vw_rpt_requests_1.CreationDate <= @End)

           

           

          Incident -

           

           

          SELECT DISTINCT vw_Incident_Site_1.ID, vw_Incident_Site_1.Title, vw_Incident_Site_1.Status, ti.Originator, vw_Incident_Site_1.CreationDate, ti.OrigCount

          FROM         (SELECT     TOP (10) Originator, COUNT(Originator) AS OrigCount                       

          FROM          (SELECT DISTINCT ID, Originator                                               

                         FROM          vw_Incident_Site                                               

                         WHERE      (CreationDate >= @Start) AND (CreationDate <= @End) AND (Site = 'Site Name')) AS x                        GROUP BY Originator                        ORDER BY OrigCount DESC) AS ti INNER JOIN                       vw_Incident_Site AS vw_Incident_Site_1 ON ti.Originator = vw_Incident_Site_1.Originator WHERE     (vw_Incident_Site_1.CreationDate >= @Start) AND (vw_Incident_Site_1.CreationDate <= @End)

          ORDER BY ti.OrigCount DESC, ti.Originator