none
Top 5 Sales People RRS feed

  • Question

  • Hi All

    Is there a way to get the top 5 sales people by sales made and displaying the rest of the Sales people as "Other"?

    Thanks

    Wednesday, June 2, 2010 8:49 AM

Answers

  • Hi heanery,

    In this case, we can use Transact-SQL(T-SQL) to get the top 5 rows, and then in SQL Server Reporting Service, use "Calculated Field" to set the rest of the sales perple as "Other".
    1. Modify the query statement as below:
     SELECT   [columns...], ROW_NUMBER() OVER(ORDER BY <column> DESC)  as rn FROM <Table>
    2. Double click the dataset to open the Dataset properties dialog.
    3. Go to "Fields"
    4. click "Add" >> "Calculated Field"
    5. Type a "Field Name" for this new filed.
    6. In the "Filed Source" textbox, type the following expression:
     =IIF(Fields!rn.Value < 6, Fields!SalePeople.Value, "Others")

    For more information about Calculated Field, please see:
    http://msdn.microsoft.com/en-us/library/ms345330(sql.100).aspx

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Thursday, June 3, 2010 11:32 AM
    Moderator

All replies

  • Is there any reason no implementing it in SQL query? If no, try to write query only return 6 rows. Maybe we could implement it by combining a tablix and textboxes in RS, but it's not straightforward.
    Have fun with Reporting Service
    Wednesday, June 2, 2010 8:58 AM
  • select

     

    *, 'Other' as category from sale

    where

     

     sale_rep_id not in

     

    (select sale_rep_id from (SELECT top 5 * FROM sale

     

    order by sale_amt desc) e)

    union

    SELECT

     

    top 5 *, 'sales made' as category FROM sale

    order

     

    by sale_amt desc

     

     

    Wednesday, June 2, 2010 10:14 AM
  • I think  you will need using UNION ALL to combain  TOP 5 sales people and 'Others'
    Wednesday, June 2, 2010 10:56 AM
  • I think  you will need using UNION ALL to combain  TOP 5 sales people and 'Others'
    Wednesday, June 2, 2010 10:57 AM
  • No, His question was all are under sales people only.

    But who ever done good sale choose top 5 people and make them as a "sales_made"

    Other than all sales people will make them as a "others"

    union is enough in this case.

    Wednesday, June 2, 2010 11:10 AM
  • Hi heanery,

    In this case, we can use Transact-SQL(T-SQL) to get the top 5 rows, and then in SQL Server Reporting Service, use "Calculated Field" to set the rest of the sales perple as "Other".
    1. Modify the query statement as below:
     SELECT   [columns...], ROW_NUMBER() OVER(ORDER BY <column> DESC)  as rn FROM <Table>
    2. Double click the dataset to open the Dataset properties dialog.
    3. Go to "Fields"
    4. click "Add" >> "Calculated Field"
    5. Type a "Field Name" for this new filed.
    6. In the "Filed Source" textbox, type the following expression:
     =IIF(Fields!rn.Value < 6, Fields!SalePeople.Value, "Others")

    For more information about Calculated Field, please see:
    http://msdn.microsoft.com/en-us/library/ms345330(sql.100).aspx

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Thursday, June 3, 2010 11:32 AM
    Moderator