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

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 conti开发者_开发技巧nue 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?

---

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.

---

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.

---

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.

---

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