This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Controlling the Number of Rows in Reporting Services Using Groups to Choose the Number of Rows per Page How the Ceiling and RowNumber Functions Work (this blog) Using Parameters to Control the Number of Rows

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!

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

Posted by Andrew Gould on 11 May 2012

In this blog

The RowNumber Function
Dividing by the Number of Rows you want to see
Finding the Next Whole Number using the Ceiling Function
What's Next?

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:

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:

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.

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