Controlling the number of rows per page in Reporting Services
Part three of a four-part series of blogs

Unfortunately, Reporting Services doesn't have a simple setting that allows you to control the number of rows you see on each page. However, you can control this using a clever combination of an expression with a grouping level. Read this blog to find out how!

  1. Controlling the Number of Rows in Reporting Services
  2. Using Groups to Choose the Number of Rows per Page
  3. How the Ceiling and RowNumber Functions Work (this blog)
  4. Using Parameters to Control the Number of Rows

Posted by Andrew Gould on 11 May 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

How the Ceiling and RowNumber Functions Work

In the previous part of this series we used an expression to calculate a value which we then used to group the records in a table.  The expression we used is shown below:


A short but effective expression.


This part of the series explains how this expression works.  To demonstrate the techniques we'll add an expression to the first column of the table, as shown below:

Adding expression

Right-click in the cell in the table and choose Expression... to open the Expression Builder dialog box.


You can now type your expression into the dialog box and click OK to enter it.


You can type an expression into the dialog box, just make sure that it begins with an equals sign!


The RowNumber Function

We used two functions in our expression: RowNumber and Ceiling.  The RowNumber function, as we've already seen, calculates the number of each row in the table.


The values in the first column of this table are calculated with the expression:


Dividing by the Number of Rows you want to see

Once we've calculated the number of each table row we divide it by the number of rows we want to see on each page.  The results of this expression are shown below:

Rownumber divided

The results in the first column are generated with the expression:


Finding the Next Whole Number using the Ceiling Function

The last job in this expression is to calculate the same value for each group of ten records so that we can group the rows.  We can do this using the Ceiling function.  The Ceiling function accepts a decimal value as its input and calculates the lowest whole number that is greater than or equal to the input value.  The results of adding this function to the expression are shown below:


The values in the first column are calculated with the expression:


In the example above the first ten rows all have the value 1 in the first column.  The next ten records have the value 2 and so on.  This means that when the expression is used to create a group the rows are organised in sets of ten.

What's Next?

Now you know everything you need to allow you to control the number of records per page, but what if you want to give your users the same power?  The final part of this blog series explains how to use a parameter to add more flexibility to this technique.

This blog has 0 threads Add post