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!)
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
Thanks,
yOOrek
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.
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
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
Thanks! Google pointed me right to this. It is exactly what I was looking for!
Steve
Thanks,
Michael
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.
Thanks for the blog. I was wondering if there is a way to report the SUM of Top N's Amount?
Thanks,
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
Please research "Creating report parameters in SSRS" in Google or Bing for information on creating report parameters.
Jessica
Everything perfect is simple!
You are the best.
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
=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.
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
"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.