Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ZMike
Posting Yak Master
110 Posts |
 Posted - 2009-01-20 : 13:27:29
|
| Hopefully this comes across the correct way.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. |
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 2009-01-20 : 14:37:44
|
| Well I'd still be curious to know for future reference but for now I went ahead and made another data set to tie into. |
 |
|
|
revelator
Starting Member
32 Posts |
Posted - 2009-01-21 : 06:24:54
|
| 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 |
|
|
|
revelator
Starting Member
32 Posts |
Posted - 2009-01-21 : 06:33:08
|
| Forgot to mention, using the same expression in the Sorting tab will allow you to sort the data descending if you choose Order...Z to A.-----------------------------------Waiting for the Great Leap Forwards |
|
|
|
revelator
Starting Member
32 Posts |
Posted - 2009-01-21 : 09:55:11
|
| Blogged: [url]http://sqlserver.posterous.com/ssrs-2008-using-the-top-filter[/url]-----------------------------------Waiting for the Great Leap Forwards |
|
|
|
|
|
|
Top 10 (Sorting in a Chart)