Display 20 records per page in SSRS report

Question

I'm trying to make an ssrs report show 20 rows per page and I've tried using

=Ceiling(RowNumber(Nothing)/20)

THIS DOES NOT WORK.

My rows contain a field that counts a number so it's just making that one field equal 20 for each page. Is there any way to make the tablix object only print a set number of rows per page so it's not reliant on the group by of the dataset?

I've also tried changing that 'nothing' to a field in the dataset but that throws an error.

Here's how my report tablix is set up:

| Part | Part Description |   Part Qty   | Part Weight |  Total Weight   |

|[Part]|[Part Description]|[SUM(PartQty)]|[Part Weight]|[SUM(TotalWeight)]

What I expect to get with the =Ceiling expression:

| Part | Part Description |   Part Qty   | Part Weight |  Total Weight   |

|PART1 |Part 1 Desc       |12             |5.00        |[60.00]
|PART2 |Part 2 Desc       |3              |5.00        |[15.00]
|PART3 |Part 3 Desc       |5              |5.00        |[25.00]
|PART4 |Part 4 Desc       |7              |5.00        |[35.00]

...Continue until 20 rows then page break

This is what I'm getting:

| Part | Part Description |   Part Qty   | Part Weight |  Total Weight   |

|PART1 |Part 1 Desc       |12            |5.00         |[60.00]
|PART2 |Part 2 Desc       |3             |5.00         |[15.00]
|PART3 |Part 3 Desc       |5             |5.00         |[25.00]
--Page Break
|PART4 |Part 4 Desc       |7             |5.00         |[35.00]

The field PartQty will count up to equal 20 then a page break.

Alan Schofield Alan Schofield · Accepted Answer · 2020-02-18T16:26:26

You need to do the following:

Add a new row group and the the "group on" option to =CEILING(RowNumber(nothing)/20). This group should be the outermost group so if you only had a details group you could right click the details group and choose "add group => parent group"
Delete the new column if one was just created - we don't need this.
Right-click the new row group you created in hte row gorup panel at the bottom of the screen and click 'Properties'
In the "Page Breaks" section, set the option to 'Between each instance of a group'

Note: Whilst this will do what you want be aware that it won't actually set the number of rows per page, it sets the number of rows per group and then puts a break between each group. If you set the value in CEILING to 100 for instance, each group probably go over several pages.

Display 20 records per page in SSRS report

1 Answers