THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

Working with Reporting Services Filters – Part 3: The TOP and BOTTOM Operators

Thus far in this series, I have described using the IN operator and the LIKE operator. Today, I’ll continue the series by reviewing the TOP and BOTTOM operators.

Today, I happened to be working on an example of using the TOP N operator and was not successful on my first try because the behavior is just a bit different than we find when using an “equals” comparison as I described in my first post in this series. In my example, I wanted to display a list of the top 5 resellers in the United States for AdventureWorks, but I wanted it based on a filter. I started with a hard-coded filter like this:

Expression Data Type Operator Value
[ResellerSalesAmount] Float Top N 5

And received the following error:

A filter value in the filter for tablix 'Tablix1' specifies a data type that is not supported by the 'TopN' operator. Verify that the data type for each filter value is Integer.

Well, that puzzled me. Did I really have to convert ResellerSalesAmount to an integer to use the Top N operator?

Just for kicks, I switched to the Top % operator like this:

Expression Data Type Operator Value
[ResellerSalesAmount] Float Top % 50

This time, I got exactly the results I expected – I had a total of 10 records in my dataset results, so 50% of that should yield 5 rows in my tablix.

image

So thinking about the problem with Top N some  more, I switched the Value to an expression, like this:

Expression Data Type Operator Value
[ResellerSalesAmount] Float Top N =5

And it worked!

So the value for Top N or Top % must reflect a number to plug into the calculation, such as Top 5 or Top 50%, and the expression is the basis for determining what’s in that group. In other words, Reporting Services will sort the rows by the expression – ResellerSalesAmount in this case – in descending order, and then filter out everything except the topmost rows based on the operator you specify.

image

The curious thing is that, if you’re going to hard-code the value, you must enter the value for Top N with an equal sign in front of the integer, but you can omit the equal sign when entering a hard-coded value for Top %. This experience is why working with Reporting Services filters is not always intuitive!

When you use a report parameter to set the value, you won’t have this problem. Just be sure that the data type of the report parameter is set to Integer. Jessica Moss has an example of using a Top N filter in a tablix which you can view here.

Working with Bottom N and Bottom % works similarly. You just provide a number for N or for the percentage and Reporting Services works from the bottom up to determine which rows are kept and which are excluded.

Published Friday, January 07, 2011 4:07 PM by smisner
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ant said:

OMG, you are a life saver! Thank you Stacia!

How much time did I waste on that stupid error:

A filter value in the filter for tablix 'Tablix1' specifies a data type that is not supported by the 'TopN' operator. Verify that the data type for each filter value is Integer.

All I needed was the = character! *smacks head*

May 6, 2011 12:21 AM
 

smisner said:

Glad to help. :)

May 6, 2011 1:09 AM
 

Ant said:

What I found super annoying about the whole experience was that the report worked perfectly in the Preview pane of BIDS but failed with the error when deployed to RS2008 (and/or R2).

It would have been much less confusing (read: frustrating) if it had failed at both ends.

May 6, 2011 1:43 AM
 

smisner said:

And I was just saying yesterday to students in my class that you can't trust the BIDS preview of your report. You really must deploy it to a server to test thoroughly. It's been a while since I've had a problem with the previewer, but your comment makes my case. :)

May 6, 2011 6:14 PM
 

Greg said:

Awesome! Thank you

August 8, 2012 6:53 PM
 

rob said:

thank you!!!!!!!!!  this is very helpful!

August 29, 2012 10:03 AM
 

RD said:

How i can add two parameter for filtration.

December 7, 2012 12:45 AM
 

smisner said:

RD, Your question isn't very clear on what you mean, but perhaps this post is helpful: http://sqlblog.com/blogs/stacia_misner/archive/2011/02/04/33208.aspx

December 9, 2012 6:59 PM
 

Abdelaliz said:

Spot on !! many thanks

April 15, 2013 5:07 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement