SQL Reporting Services Tips with Footer Text Problem

Introduction

One among the issues with SQL Reporting Services is handling the page footer, the way you wanted it. There are quite a few restrictions for the page footer of the Report Designer. So in order to make the footer available the way you wanted, some tweak coding is required. Here we will discuss such an issue and the solution for that.

Background

A common approach followed by most of us is to set the RepeatOnNewPage property to false for the footer. This will display the footer only on the first page, but not on all the first pages. To make it clear, let us consider an example where my stored procedure returns 10 records and I have designed the report in such a way that each record spans upto 2 pages. When I set this RepeatOnNewPage property of the footer to false, it displays footer only on the first page of the first record, but not on first page of all the 10 records. I hope this might have given you a clear idea of the problem.

Using the Code

So a solution is badly needed for this issue to be fixed. We had a similar problem and we found the solution in the following way. An article which we found on the net was more than enough for us to get rid of this issue.

The link for that article is http://blogs.msdn.com/ChrisHays/.

In that article, the author followed the grouping concept of reporting services to segregate the records coming from the database which if not followed will end up showing all the records as a single report. The same concept, with a tweak coding will solve the issue for us. The steps to be followed to solve this issue are as follows:

Step 1

We have to take a textbox in the report which contains the group expression, i.e., the field which we use to segregate the records coming from the database. (For example, if you are grouping based on a field from database, say PatientID, then the textbox will contain "=Fields!PatientID.Value" as expression. Name the textbox as Category.)

Step 2

Add the following code to your Code section of the report. This code section is available at Report--> Report Properties --> Code.

Shared offset as Integer
Shared currentgroup as Object

Public Function GetGroupPageNumber(group as Object, 
         pagenumber as Integer) as Object
 If Not (group = currentgroup)
     offset = pagenumber - 1
     currentgroup = group
 End If
 Return pagenumber - offset 
End Function

Step 3

Use the following as visibility expression for the textbox in the page footer:

=Code.GetGroupPageNumber(ReportItems!Category.Value, Globals!PageNumber)

(This is the textbox that contains the footer text.)

Points of Interest

The most important aspect of this piece of code is that we are able to set the page number for the report, which will help in a lot of cases where we work around with Footers of the Report.

http://www.sakasolutions.com

History

• 11^th January, 2008: Initial post