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

Probably not exactly the answer you are looking for but you could limit the query or data source the first table is bound to to 5 items or whatever. Then the second table would be bound to a query or data source with just the remaining items.

I don't think there is a way in the report to do this with a property or anything like that.

Answer 2

I don't think there's an easy way to do this. AFAIK, SSRS won't help you here. You could change your query logic so that it pads out the resultset with a number of 'dummy' rows if the actual number of rows returned is < 5. However this seems like a messy solution.

Answer 3

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.

Answer 4

You will need to union in some blank data when there is none.

Add a calculated row to the dataset called rowcount for example

=rownumber("datasetname")

Then filter the first table for rowcount < 6