posted 11/12/2009 by MikeDavis - Views: [23412]
I had a Client recently that wanted to show the top N of a query on a report and have the rest separated into another group. The top N is easy but the bottom N would changed based on the number of rows in the query. So I had to figure out a way to calculate this number. I created two groups on the report. The first I placed a filter of “top 10”.
In the second group I placed a filter of bottom N, but I had to calculate the N by subtracting the count of the rows in the dataset.
I used the count function on a field in the dataset and subtracted 10 to remove the top 10 rows. So the results look like so:
Hi Mike,
Your SSRS 2008 webinar was great this morning! I am trying to do something similar to your blog post in SSRS 2005. The main difference is that I don't need to show the contents of the Others catch all category. I'm using patient city and encounter count from my SSAS cube and have created a report with a table object. I simply want to show the top 20 zip codes / cities with encounter count for each, along with an Others catch all category and a grand total. Can you step through how this is accomplished in SSRS 2005 using a SSAS cube as my data source?
Thanks,
Sid
Schilders,
I am not sure what else you need except for the instructions I gave in the blog. If you don't want to show the others group then don't create it. Other than that I am not sure what else you are asking.
while the use of top N and bot N is appreciated, what is the value of this report? results appear as though they are just an alphabetical listing of products within a given category, showing M - Z as the top and A - L as the bottom. I'd expect any value to come from showing top 10 and others based on a given product sales amount. This report would start with Sport-100 Helmet Red - $78K and work it's way down. That would be more useful and more of a challenge.
Nitz,
Yes top N by product name is not very useful. But I could have selected Top N by Sales Amount, or a measure that was important to my company.
I want this same situation for Char
I want to Migrate Crystal to SSRS .
In crystal there is facility for top N and others , but in SSRS I dont find Others option..
Hi Mike
What a innovative approach, Cool!
Cheers!
found this page through google. just getting started with ssrs 2008. tried to follow the pictures, but no luck. do you have a more detailed step by step directions on how you designed the report above or maybe the same report to download. thanks.
Drew9,
I will work on a more complete blog covering this topic. Several issues can cause this technique not to work. Sorting and duplicate items cause most of the issues.
I wrote anther blog cover some of the issues with this method.
http://www.bidn.com/blogs/MikeDavis/ssis/1875/ssrs-top-n-and-bottom-n-reporting-with-duplicates