Skip to main content

Display Top N Rows

In SQL Server Reporting Services, you can dynamically limit the number of rows that are displayed in a table or a matrix. Here are the steps to create a sample report showing this.

1. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query:
SELECT DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResellerSales.SalesAmount) AS SalesAmount
FROM DimEmployee INNER JOIN
FactResellerSales ON DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey
WHERE (FactResellerSales.OrderDateKey = 20011001)
GROUP BY DimEmployee.FirstName, DimEmployee.LastName

2. Add a report parameter named “NumberPpl” of data type integer.

3. Add a table to the body of the report with the data fields of firstName, lastName, and salesAmount. Sort by the column salesAmount from ‘Z to A’.

4. Set the Filters option of the table as shown below. (This is where the real work is happening!)

5. Preview the report, modifying the value for the parameter NumberPpl to see the number of rows change. It should look similar to the reports below.



Version: SQL Server 2008 RC0

Comments

Anonymous said…
This comment has been removed by a blog administrator.
yOOrek said…
Thanks, this has helped me a lot. I was trying to achieve this in SQL statement with a parameter but it didn't work. The way you described it is perfect.
Thanks,
yOOrek
Hi Jessica,
This same case should I apply to RS 2005 but I get an error that says:

"An error occurred during local report processing.
An error has occurred during report processing.
Failed to evaulate the FilterValue."

Thanks for the help.
Jessica M. Moss said…
Hi Hugo,

The same logic should work for 2005 as well. Double check the parameter name in the Value field, and ensure that you are using brackets, an at symbol, and the exact name of the Parameter.

HTH, Jessica
This mostly works, except in my particular case I'm sorting on a column which has a few identical values, so when I say to give me the "Top 5", it gives me 8, since the last 4 all have the same value.
Further - my solution was to use Top N on another unique column at the same time.
Jessica M. Moss said…
Hi David,

Try using the alternate method: http://jessicammoss.blogspot.com/2008/09/display-top-n-rows-alternate-method.html. Make sure you order by an extra column, so that you can guarantee which of the four rows will be shown.

Good luck!
Jessica
Anonymous said…
Jessica,

Thanks! Google pointed me right to this. It is exactly what I was looking for!

Steve
Anonymous said…
Hugo, that error occurs if you create the parameter with the wrong data type - such as text. Change it to an integer and it should work
Gilbert P said…
Awesome! Thanks for this.
Anonymous said…
Awesome. This worked perfectly.

Thanks,
Michael
Anonymous said…
Hi Jessica,

when Im trying this, I get an error
Microsoft Sql Server Repport Designer

value is not a decimal.

even though in parameter i have changed the data type to interger. I have also tried to change it to float as well.
JimC said…
Hi, Jessica

Thanks for the blog. I was wondering if there is a way to report the SUM of Top N's Amount?

Thanks,
Jim
Jessica M. Moss said…
Hi Jim,

You may want to use the alternative top n method found here: http://jessicammoss.blogspot.com/2008/09/display-top-n-rows-alternate-method.html. That will allow you to sum.

Jessica
Andrea Grimaldi said…
I'm completely new at this. Would you please tell me how to create a report parameter? Please....
Jessica M. Moss said…
Andrea,

Please research "Creating report parameters in SSRS" in Google or Bing for information on creating report parameters.

Jessica
Anonymous said…
Thank you so much!
Everything perfect is simple!
You are the best.
Anonymous said…
Very Nice.
Unknown said…
Hello, you say that top n rows will be displayed, what about downloaded data? it also will be filtered or unfiltered data will be rendered?? thank you
Jessica M. Moss said…
Hi Irina,

All rows are queried/downloaded from the source. Only the top N rows will be visible. If you only want to query/download N rows, try this method: http://jessicammoss.blogspot.com/2008/09/display-top-n-rows-alternate-method.html.

HTH,
Jessica
Jatin Shah said…
I have same kinda thing but in value I specified expression based on multivalued text type parameter which I am using as an input

=IIF(Parameters!Task.Count > 2,Parameters!Task.Value(2),"")

If I am spcifying "string" instead of Parameters!Task.Value(2) it works fine. I also tried by using CStr function but its not helping.
Jessica M. Moss said…
Hi Jatin,

The IIF function evaluates both the true and false parts, so I'm wondering if it's failing because there are less than 3 items? Try adding a few more elements in the task parameter and see if that helps.

HTH,
Jessica
CG said…
You first posted this years ago and it is still valuable information! Setting the filter on the tablix property was the only way that "SELECT TOP (@Parm) * ..." would work for me in the dataset. Thank you, thank you, thank you!!!
Anonymous said…
the same I am trying to do but its give me following error message
"A filter value in the filter for tablix specified data typethat is not supported by the 'TopN' operator. verify that the data type for each filter value is integer.
Unknown said…
Excellent, this approach is better than having "top(x)" directly in data source (sql statement). i.e. I have a data source common for few tablix-es, each tablix with it's own order. Setting the "top x" in tablix offers the necessary flexibility.
Marie Chase said…
Hi ggreat reading your post

Popular posts from this blog

SQL Server 2016 versus 2014 Business Intelligence Features

Hello, SQL Server 2016 Yesterday, Microsoft announced the release of SQL Server 2016 on June 1st of this year: https://blogs.technet.microsoft.com/dataplatforminsider/2016/05/02/get-ready-sql-server-2016-coming-on-june-1st/ .  Along with performance benchmarks and a description of the new functionality, came the announcement of editions and features for the next release. Good-bye, Business Intelligence Edition The biggest surprise to me was the removal of the Business Intelligence edition that was initially introduced in SQL Server 2012.  Truthfully, it never seemed to fit in the environments where I worked, so I guess it makes sense.  Hopefully, fewer licensing options will make it easier for people to understand their licensing and pick the edition that works best for them. Feature Comparison Overall, the business intelligence services features included with each edition for SQL Server 2016 are fairly similar to SQL Server 2014.  Nothing has been "...

Is Data Science a Buzzword? aka: My first Coursera Course

Data science and data scientists are all the rage right now in the information technology space. Every company wants one; every job candidate touts they are one. But what actually does that mean to companies and potential employees? I decided to take a course on data science to see if I could find out! My co-worker, Gabriella Melki, recommended the Coursera Data Science specialization by John Hopkins Bloomberg School of Public Health. The entire specialization contains a set of 9 courses, but you can take each one individually. I started with the first course, called "The Data Scientist's Toolbox". Over the four week timeframe, I was able to view lectures and perform the assignments at my own pace. I've listed below my thoughts on the course and what I learned about data science. Week 1: Introduction to Data Science Data science is about data , specifically about answering questions, and science , following a method to discover an answer. A data scientist is the ...

Accidental SharePoint Designer 101

I fully profess to know little to nothing about SharePoint, but I occasionally get pulled into setting up little sites or adding web parts for some of my reporting and business intelligence work.  Each time, I have to relearn the start-up steps to create what is needed!  So I decided to record a few of my go-to places so I can remember next time.  I used SharePoint 2010 to document the below steps, but the directions may be applicable to other versions.  Also, these steps assume you have full control of your site. Getting Started The first step is start editing the page rather than looking at it like an end user.  Do this by: Select the Page tab at the top of the screen Click the Edit Page button/drop down list Select the Edit Page option PS. When you're done, do these same steps, except select the "Stop Editing" button. Content Creation You may need to create a document library, a list, or another type of container.  I ...