sql server - How to Display Page Number in Report Body of SSRS 2008 R2?

Question

I think a lot of developers are facing the problem of try to display page numbers by using SSRS 2008 R2.

There is an alternative solution which requires SSRS 2010 + version. Otherwise you will get 1 all the time.

Go to "Report" -> "Report Properties" -> "Code"

In the Custom Code section, enter the following:

Public Function PageNumber() as String
     Dim str as String
     str = Me.Report.Globals!PageNumber.ToString()
     Return str
End Function

Public Function TotalPages() as String
     Dim str as String
     str = Me.Report.Globals!TotalPages.ToString()
     Return str
End Function

Now you will be able to access these functions anywhere in the report (header, body, or footer). So, to output the page number and total pages in a textbox located in the body simply enter this for the value:

="Page " + Code.PageNumber() + " of " + Code.TotalPages()

This solution DOES NOT work with SSRS 2008 R2.

However there is a workaround, it will work with any version higher than 2008 R2 (include 2008 R2). I will post as an answer, hope it will help some people whoever struggling with this issue.

Answer 1

First you need to use report variables: right click on the empty space of report -> Variables -> Create a variable such as PageCount (set default value to 0)

Then in you header or footer -> create a textbox and set expression ->

=Variables!PageCount.SetValue(Variables!PageCount.Value+1)

It will automatically increase for each page. (IMPORTANT: DO NOT hide it from header or footer, the SetValue WON'T work if you hide the box, so change the font to 1 or text to white, do whatever, just DO NOT hide it (it will print 'True' as the setting took places))

Then you can use:

=Variables!PageCount.Value

at any part of your report body to access the page number.

IMPORTANT: Please NOTE that I tried to use Globals!PageNumber to set the variable but ends up it was NOT accessible from report body. So, it has to be something both accessible from Header/Footer OR Body.

In my case, I have to reset the Page number per each instance of my Group. So I just set a trigger at the end of the group. (e.g. I check if I have my Total value returns, because i know for each end of my group i will have a Total display.

Because of in function IIF both True and False part will be processed, so if you put setters in IIF such as below:

=IIF(IsNothing(ReportItems!TotalBox.Value),Variables!PageCount.SetValue(Variables!PageCount.Value+1),Variables!PageCount.SetValue(0))

 ) 

you will ends up have value 0 all the time, because the report will Check the True Part then the False part, both setters will be executed (value will be set twice)

so we need 2 boxes and something like: (You have to hide unnecessary box your checking conditions)

=IIF(IsNothing(ReportItems!TotalBox.Value),Variables!PageCount.SetValue(Variables!PageCount.Value+1),"")
)

You need to hide this box when NOT IsNothing(ReportItems!TotalBox.Value)

=IIF(NOT IsNothing(ReportItems!TotalBox.Value),Variables!PageCount.SetValue(0),"")
)

Again you need to hide this box when IsNothing(ReportItems!TotalBox.Value)

Of course you could use some other way to determine the end of a group instance, like: make a textbox which ONLY contains a fixed value at the end of your group table. and hide it. when you checking the trigger just do the similar approach as I do.

It works fine for all versions above 2008 R2 (included).