Forcing a SQL Reporting Services 2005 table to show a certain number of rows

Question

I've got a SQL Reporting Services 2005 report that includes a table on the first page. I have enough room on the first page to show the first 5 items from a list. If there are more than 5 items, I want the list to continue to another table on the second page of the report.

I also want the tables to have a fixed number of rows. For example, the table on the first page always shows 5 rows even if no items exist in the list. This allows the border to still be visible so that the page layout isn't messed up.

Any thoughts on the best way to get this working?

Answer 1

I think that this is best done in the Query / Stored Proc that returns the data rather than in SSRS.

You can do something like this

SELECT TOP 5 FROM
(
    SELECT Top 5 *
    FROM DummyOrBlankDataFillerView
    UNION
    SELECT TOP 5 *, Row_Number() over (order by YourColumns) as OrderByClause 
    FROM ActualQueryThatBringsBackRecords
)
ORDER BY OrderByClause

OrderByClause is ordered by your columns and will have (1,2,3,4,5) and DummyOrBlankDataFillerView should have a column that you get back that has values in the same column as (6, 7, 8, 9, 10).

Then, between the order by, and the `top 5' you should have what you need to display.