Controlling the number of rows per page in Reporting Services
Part four 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
  4. Using Parameters to Control the Number of Rows (this blog)

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.

Using Parameters to Control the Number of Rows

Although it's nice to be able to control the number of records per page in the design view of a report, sometimes your users will want to be able to choose how many records are displayed when the report is running.  You can give them this power by linking your group expression to a parameter.

Creating a Parameter

The first step in this example is to create a parameter that allows your users to enter a number corresponding to the number of records they want to view on each page.  The diagram below shows how to add a parameter to a report:


In the Report Data window, right-click the Parameters folder and click Add Parameter...


You can use the following dialog box to configure the parameter you have just created:

Parameter configuration

Use this dialog box to set up the parameter. The numbered steps are described below.

  1. Select the General tab.
  2. Type in a name for your parameter.  We'll use this name in our expression later.
  3. Type in a prompt here.  Your users will see this message so make it as descriptive and helpful as you can.
  4. Choose the data type of the parameter.  For our example we want users to type in whole numbers so we choose Integer.
  5. Make sure the parameter is Visible or your users won't be able to see it!

It's also worthwhile adding a default value to the parameter so that you see a set number of rows per page when you first open the report.  You can do this on the same dialog box, as shown below:

Default value

Use this page of the dialog box to set a default value for the parameter. The numbered steps are described below.

  1. Select the Default Values tab.
  2. Choose to Specify values.
  3. Click the Add button to add a new value.
  4. Type in the value you want to use as the default.
  5. Click OK.

Linking the Parameter to the Expression

The final step in making this technique work is to 'plug-in' the parameter to our group expression.  To do this:

  1. Right-click the group at the bottom of the screen and choose Group Properties...
Group properties

Right-click the name of the group and choose the option shown here.

  1. Select the General tab.
  2. Click the fx button to launch the Expression Builder.
Launch expression builder

Use these options to launch the expression builder.

  1. Select the value you typed into the expression earlier.
Highlight value

Use the mouse to highlight the value you typed into the expression earlier.

  1. At the bottom left of the dialog box click the Parameters category.
  2. Double-click on the name of the parameter you added earlier.
Insert parameter

Double-click the name of a parameter to replace your value with a reference to the parameter you created.

  1. Click OK.

Testing Your System

The final thing to do is check that your system works!  When you preview the report you should see that you can type a number into the parameter box at the top and click View Report to change the number of records on each page.

Using parameter

Type a number into the box at the top and click View Report.

Your parameter won't work if you type in a number that exceeds the maximum number of rows that will fit on the size of page you are using.

This blog has 0 threads Add post