560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
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!
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:
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:
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.
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|
25 Aytoun Street