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 page. 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 displayed 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.

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.

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

In that article the author has 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 is as follows.

Using the code

A brief description of how to use the article or code. The class names, the methods and properties, any tricks or tips.

Blocks of code should be set as style "Formatted" like this:

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)

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 workaround with Footers of the Report