Limit number of pages displayed in a SSRS Report

Question

I have seen many posts regarding limiting number of rows displayed on one page in a SSRS Report. But I want to display 50 rows in one page and would like to limit the report to 5 pages. Is this possible ? Can I limit the number of pages displayed by report?

That is not an option in my case since query might return 100 records but I will still have to show five pages. — Programmermid, Sep 26 '16 at 18:32
You can use page breaks to produce five pages, but what will you show in the next three pages if your report returns 100 rows? — alejandro zuleta, Sep 26 '16 at 18:36

score 1 · Accepted Answer · answered Sep 26 '16 at 19:51

The Top 250 is the answer for 50 rows per page and always show five pages, what you need to also do is in your result set for your dataset, make it always return 250 rows where whatever is under 250 has a blank row.

Here is an example:

Create table #mytable
(
  firstname varchar(200),
  lastname varchar(200)
)

insert into #mytable
  select 'person','lastname'
union
select 'person2','lastname'

declare @totalrows int
declare @blankrows int
declare @currentrow int
set @totalrows = count(*) from #Mytable

set @blankrows = 250 - @totalrows
set @currentrow = 1

while @currentrow<=@blankrows
    begin

    insert into #MyTable
    SELECT
      '',
      ''

    end

Your table should now always have 250 rows.

score 0 · Answer 2 · answered Sep 26 '16 at 19:52

I would try to use the workaround for exporting more than 65k rows to excel. Use this as your group function for your page break and then create a sequence and left outer join to Top(250) so that there are always 250 rows no matter what.

=Int((RowNumber(Nothing) - 1) / 50)

Source for page break on row numbers.

score 0 · Answer 3 · answered Sep 26 '16 at 20:05

Applicable to Tablix report only.

This method will make 50 record per page or even you can customize 100 records to 20 records per page. (However you can make it 250 records anyway by adding blank rows)

Steps:

Create a group with below expression:

=ceiling(rownumber(nothing)/50)
New group with column will be added, delete the column not group
Delete the Sorting option from the created group
Go to Group Properties > Page Breaks> Check Between each instance of group
You are also required to change the page setup from the Report properties as the default number of rows is 43. Make the height to default 11 to 14.

Limit number of pages displayed in a SSRS Report

3 Answers3