I'm trying to take our top 10 employees in a chart that would have a sum on sales.
Is there a way without writing a seperate query to sort by the top 10 sales desc in Reporting Services ?
I was thinking that it could be done by either the data tab on the chart, by going to values or by going to filters. However anytime that I type TOP it says unrecognized identifier.
I thought this was quite an interesting question, so I've have a go at it this morning, and think I have a solution.
The problem I found when using a filter in the Chart Properties was that aggregates are not allowed in filters at that level. Also, filters cannot be added into the Series Properties for the data fields.
What I did was create a filter on the Category Group Properties in the category field. I set the expression to be the same as the data field (eg Sum(Fields!SalesAmount.Value)). Then set the Operator to "Top N" and set the Value to be a parameter I was using called @TopN. You could also set this to be =10 though. This gave me the results you were looking for, and changing the TopN parameter confirmed this.
I'll look into this in a bit more depth and hopefully get a blog post up about it soon.
Note I've only tested this on SSRS 2008.
----------------------------------- Waiting for the Great Leap Forwards
Top 10 (Sorting in a Chart)
Posted - 01/20/2009 : 13:27:29
