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 two 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 (this blog) How the Ceiling and RowNumber Functions Work Using Parameters to Control the Number of Rows

Controlling the number of rows per page in Reporting Services

Part two 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 (this blog)
How the Ceiling and RowNumber Functions Work
Using Parameters to Control the Number of Rows

Posted by Andrew Gould on 11 May 2012

In this blog

Step 1 - Create the Basic Report
Step 2 - Add a Grouping Level
Step 3 - Remove the Automatic Sorting
Step 4 - Add a Page Break Between Each Group

Using Groups to Choose the Number of Rows per Page

The most accurate way to control the number of records on a page in a report is to combine grouping with a simple expression. This part of the blog series explains exactly how to make this technique work.

Step 1 - Create the Basic Report

For our example we'll create a simple report with a table containing a list of 263 films. Our aim is to make each page of the report display only 10 rows. The design of this report is shown in the diagram below:

This is the basic design of the report. For demonstration purposes we've added an expression to calculate the row number of each record in the table.

The expression that we've used in the Row column is shown in the diagram below:

The RowNumber function calculates the number of each row in the current table. We don't need to include it here but it helps to demonstrate the effect.

The results of running this report are as follows:

The report contains 6 pages. The first 5 pages contain 48 rows and the final page contains 23 rows, making a total of 263 records.

Step 2 - Add a Grouping Level

The next step is to add a grouping level to the table using the groups panel at the bottom of the screen:

Right-click on the (Details) row and choose Add Group > Parent Group...

On the dialog box that appears, click the button shown in the diagram below to launch the Expression Builder dialog box:

Click the fx button shown here to launch the Expression Builder.

In the next dialog box, enter the calculation shown below and click OK.

The number 10 in this expression ensures we see 10 rows per page, but you can change this value if you want to.

Click OK once more to apply the grouping level to the table.

Click OK again to apply the grouping to the table.

Step 3 - Remove the Automatic Sorting

If you try to preview the report at this stage you'll receive an error message similar to the one shown below:

The error message you'll see when you preview the report.

To prevent this error from happening we need to remove the automatic sort that has been applied to the group. Start by right-clicking on the group in the panel at the bottom of the screen:

Right-click on the group name and choose Group Properties...

Complete the dialog box which appears as shown below:

The numbered steps are described below.

Click on the Sorting tab.
Select the sort that you want to remove.
Click on the Delete button to delete the sort level.
Click OK.

You should now find that when you preview the report you can see all of the data in groups of 10 rows.

Each set of 10 rows has a different value in the Group1 column.

The next step is to put each group on a separate page.

Step 4 - Add a Page Break Between Each Group

To add a page break between each group, right-click on the group name in the panel at the bottom of the screen:

Right-click the group name and choose Group Properties...

Complete the dialog box which appears as shown below:

The numbered steps are described below.

Select the Page Breaks tab.
Check the box shown here.
Click OK.

When you preview the report you should find that you have the specified number of records per page.

We get the exact number or records we require on each page, at the cost of having more pages in the report.

To tidy things up you could delete the first two columns in the example shown above - neither are required to make this technique work.

At this point you know everything you need to make this technique work for any number of records per page (up to the maximum that the page size can handle). If you're interested in how the expression we've used actually works you can read the next part of this blog series to find out!

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

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 Groups to Choose the Number of Rows per Page

Step 1 - Create the Basic Report

Step 2 - Add a Grouping Level

Step 3 - Remove the Automatic Sorting

Step 4 - Add a Page Break Between Each Group

Head office

London

Manchester