Post reply

TOP(10) works but returns wrong results to ReportViewer

  • June 20, 2012 at 7:11 am

    #263116

    Hi all, I am using VS2010 c# to build a project which is working fine..... until now. I have added a report that I want to take the TOP(10) operators and count how many calls they have raised.

    This is my query

    SELECT TOP (10) Operator_Name, COUNT(Docket_Id) AS Qty

    FROM DocketTB

    WHERE (Docket_Status = 'CL')

    GROUP BY Operator_Name

    ORDER BY Qty DESC

    With VS2010 this works giving the right result, and this is SQL also return correct results

    SELECT TOP (10) Operator_Name, COUNT(Docket_Id) AS Qty

    FROM SLADB.dbo.DocketTB

    WHERE (Docket_Status = 'CL')

    GROUP BY Operator_Name

    ORDER BY Qty DESC

    In report viewer however the result are incorrect when I try Count(Docket_Id) it always list 1.

    Full select

    SELECT Docket_Id, Docket_Number, Docket_Machine, Docket_Status, Docket_EngineerName, Docket_Category, Docket_SubCategory, Duration, Module, Section, Waittime,

    Operator_Name, Monitor_Time, spare8, Docket_EngStart, Docket_EngFinish, Docket_DateRaised, Docket_Date, Con1, Con2, Con4, Con3, Con5, Con7, Con6, Contract,

    Con10, Weekend

    Do I have to pass all these to the reportviewer Select ? maybe this could cause me the problem, if so how do I pass them with my query.

    Thanks

  • June 20, 2012 at 7:19 am

    #1503393

    Are you doing this in the detail section by any chance? If so, the count is one (for each row).


    Rick Krueger

    Follow @dataogre

  • June 20, 2012 at 7:22 am

    #1503400

    Hmmm that would make sense I guess, how to get count for total ?

    Thanks

  • June 20, 2012 at 11:57 am

    #1503623

    I think you are looking for the RowNumber Function in SSRS: http://msdn.microsoft.com/en-us/library/ms159225.aspx


    Rick Krueger

    Follow @dataogre

  • June 21, 2012 at 1:33 am

    #1503796

    hi,

    I think you can use two select statements

    1) for select top 10 results with count

    2) wanted columns list from tables using where cluase above Query.

    SELECT TOP (10) Operator_Name, COUNT(Docket_Id) AS Qty

    FROM SLADB.dbo.DocketTB

    WHERE (Docket_Status = 'CL')

    GROUP BY Operator_Name

    ORDER BY Qty DESC

    ----------------

    SELECT Docket_Id, Docket_Number, Docket_Machine, Docket_Status, Docket_EngineerName, Docket_Category, Docket_SubCategory, Duration, Module, Section, Waittime,

    Operator_Name, Monitor_Time, spare8, Docket_EngStart, Docket_EngFinish, Docket_DateRaised, Docket_Date, Con1, Con2, Con4, Con3, Con5, Con7, Con6, Contract,

    Con10, Weekend from SLADB.dbo.DocketTB where (SELECT TOP (10) Operator_Name

    FROM SLADB.dbo.DocketTB

    WHERE (Docket_Status = 'CL')

    GROUP BY Operator_Name

    ORDER BY Qty DESC)

    Prasad.N
    Hyderabad-India.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply