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 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! 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 Using Parameters to Control the Number of Rows (this blog)

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!

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
Using Parameters to Control the Number of Rows (this blog)

Posted by Andrew Gould on 11 May 2012

In this blog

Creating a Parameter
Linking the Parameter to the Expression
Testing Your System

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:

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

Select the General tab.
Type in a name for your parameter. We'll use this name in our expression later.
Type in a prompt here. Your users will see this message so make it as descriptive and helpful as you can.
Choose the data type of the parameter. For our example we want users to type in whole numbers so we choose Integer.
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:

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

Select the Default Values tab.
Choose to Specify values.
Click the Add button to add a new value.
Type in the value you want to use as the default.
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:

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

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

Select the General tab.
Click the fx button to launch the Expression Builder.

Use these options to launch the expression builder.

Select the value you typed into the expression earlier.

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

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

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

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.

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.

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 Using Parameters to Control the Number of Rows (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using Parameters to Control the Number of Rows

Creating a Parameter

Linking the Parameter to the Expression

Testing Your System

Head office

London

Manchester