Sunday, September 14, 2008

Display Top N Rows - Alternate Method

In a previous post, I showed how to dynamically limit the number of rows shown in a table or matrix. This methodology used a filter on the table to only show the desired number of rows. The disadvantage with this method is that all rows are returned, even if only a small subset of rows is actually desired.

To alleviate this problem, you can use a query parameter to restrict the number of rows returned at the query level. This does introduce a different problem. Every time the report is run with a different Top N parameter, the query will be rerun to bring back the correct number of rows. You will need to determine which method is optimal for your situation.

Here are the steps to create a sample report to show this new method.

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

2. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query:
SELECT TOP(cast(@TopN as int)) DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResellerSales.SalesAmount) AS SalesAmount
FactResellerSales ON DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey
WHERE (FactResellerSales.OrderDateKey = 20011001)
GROUP BY DimEmployee.FirstName, DimEmployee.LastName
ORDER BY SUM(FactResellerSales.SalesAmount) DESC
Assign the @TopN query parameter to use the @NumberPpl report parameter.

Note that this differs from the original query in two ways:
A. Includes the TOP clause to restrict the rows
B. Includes a descending order by for the SalesAmount to ensure the highest SalesAmounts are shown

Also note that the TopN query parameter is cast to an integer. This is because all query parameters are created as string and the TOP clause expects an 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’. This sort is now only for display purposes and is not necessary to return the correct data.

4. Preview the report, modifying the value for the parameter NumberPpl to see the number of rows change. It should look similar to the report below and the reports using the first method.

Version: SQL Server 2008 RTM


Anonymous said...

Jessica, I have a situation and I am hoping that you will have an answer. I have a matrix and it returns different number of rows based on a parameter selected. I would love to have the first page with the first 9 results and the remaining in the next page limiting the page display to 9 results till the end of the rowset. Think you can help me out. I cannot restrict the rows returned by using a parameter. However, I can assign a number to the display rows as you have shown in your example. This I am gussing is an extension of your solution. Think you can help me.

Jessica M. Moss said...

Hi Anon,

I would recommend posting your question on the MSDN forum - then multiple people can help.