Post reply

Show top 5 customers in pie chart and other customers as "Other"

  • January 14, 2015 at 7:28 pm

    #313749

    Hello,

    I am using SSRS 2013 I have a pie chart that shows many slices and I don't want that I want to show 5 separate (top customers) pie slices and the rest of the slices as one slice and label as "other" how do I do that? Thank you in advance.

  • January 14, 2015 at 9:07 pm

    #1770317

    If you want to group the non-top 5 into one group, then you could do it in T-SQL and then create your report based on that.

    Here's a quick sample (sorry, I used Itzik Ben-Gan's TSQL2012 windowing functions database... just easier to understand!) -- I also used Freight instead of sales, but the idea is the same...

    Basically what I did was find the customers in the top 10, and then I grouped the rest into "11".

    SELECT NewCustID

    , SUM(TotalFreight) AS TotalFreight

    FROM (

    SELECT CASE WHEN SalesRank<=5 THEN CustID ELSE 100000 END AS NewCustID

    , SalesRank

    , TotalFreight

    FROM

    (

    SELECT custID

    ,RANK() OVER (ORDER BY SUM(Freight) DESC) AS SalesRank

    ,SUM(Freight) AS TotalFreight

    FROM Sales.Orders

    GROUP BY CustID

    ) x

    ) y

    GROUP BY NewCustID

    ORDER BY TotalFreight DESC;

    That's the only way I could think of doing it... no idea how to do it if you can't write your own stored procedures...

    HTH,

    Pieter

  • January 15, 2015 at 1:51 pm

    #1770563

    SSC Eights! Thanks! I will give that a shot. Thanks again.

Viewing 3 posts - 1 through 2 (of 2 total)

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