SSRS charts: How can I display the n biggest slices / bars?

Question

Hi,

I know how to group chart slices / bars below a certain threshold together into one bar. But if the data displayed in the chart contains lots of small slices, collecting slices below 5% or so results in a huge "other" slice / bar. On the other hand, if I collect only slices below a very small treshold, the chart could contain too much data to be readable.

So, I'd like to set a hard upper limit for the amount of slices, show up to n biggest slices and collect everything else into one "other" slice.

How can I do that with SSRS 2008?

Thanks,

Adrian

Answer 1

I would usually handle such scenarios in my query. (I never used 2008 so I am not sure if it has better capabilities)

Select into temp table top N categories/slices.

Insert into temp table category called "Other" with value equal to sum of all categories no already in the temp table.

Answer 2

It is not a good idea to handle this kind of issues in SSRS designer. in most cases these kind of issues handled in Storeprocedures or other Queries in Database.

if you had another scenario it would be a good idea to control data on Designer but in this case you send lots of waste data to server and this will take a long to process and user should wait. but if you do this in DB, user will face with his/her report sooner than your scenario. and thats why you need to handle it on DB.

Answer 3

I'm pretty sure this is possible to do in SSRS without rewriting your query. I think it will be easier to rewrite your query. However, given your parameters, I'll carry on.

I think this will take some serious experimentation on your part. Take a look at points #17 and #18 at http://www.ssw.com.au/ssw/standards/rules/rulesToBetterSQLReportingServices.aspx and see if it helps. They demonstrate a good way to set the scale of a chart based on the values in the row. It seems like you could combine this with a hard maximum using IIF(Max(MyColumn.Value) > @MyLimit, @MyLimit, Max(MyColumn.Value))

This gets you half the equation, because now you know your limit. Now the trick is to stuff all values below a certain scale into the "Other" bucket. This can also probably be solved by some creative IIFing: Set the value for the label column = IIF(MyValueColumn.Value < @Threshold, "Other", MyLabelColumn.Value)

Like I said, you'll have to do some experimentation and it will likely be easier to rewrite your query, but if you're stuck with SSRS only, at least you've got someplace to start looking.