Pie Chart to Show top5 Collect all others

• grayner 26961

  SSC Enthusiast

  Points: 152

  More actions

  November 17, 2015 at 11:54 am

  #306504

  I am using SSRS 2008 working in Visual Studio. I have a pie chart that I need to display the costs for the top 5 units then collect all the others into a single slice. All I have been able to find is how to use a % or a set number. The costs vary across the different groups of equipment. Shot of the chart data attached.

  How do I display the top 5 units and collect the rest into a single slice.

  Thank you,

• pietlinden

  SSC Guru

  Points: 63421

  More actions

  November 17, 2015 at 1:11 pm

  #1840473

  You could use RANK/DENSE RANK in your query and then create a calculated expression in SSRS.

  IIf(PersonRank>5.6,PersonRank)

  then just group on [PersonRank]

• MMartin1

  One Orange Chip

  Points: 27764

  More actions

  November 23, 2015 at 11:36 am

  #1841808

  I would approach this by using the SQL in your report (preferably the stored procedure that the report calls).

  WITH myTop5 as (

  select top 5 customer

  from myTable

  group by customer

  order by sum(salesAmouns) DESC

  )

  SELECT t1.customer, sum(t1.salesAmount) as totalInSales

  from myTable as t1 inner join myTop5 as t2 on t1.customer = t2.customer

  group by t1.customer

  union all

  SELECT 'All the others', sum(salesAmount)

  from myTable t1

  where NOT EXISTS (

  select t2.customer

  from myTop5 as t2

  where t1.customer = t2.customer

  )

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