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.

Reed

Din't Work for me! in SQL server 2012. – Ribband

This does not work in SQL Server 2016. – Apogeotropism

@HuseinRoncevic Sorry man, I wrote this post in 2014, I havent got chance to use SSRS 2016......... – Reed

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).

Reed

@Reed Din't Work for me! in SQL server 2012 – Ribband

This increments the page number when going back a page. – Sawbuck

It isn't work for me. Although a textbox in header displays TRUE but A variable still be 0. But a variable will be change if I click at 'Print Layout' button in toolbar. – Nimmons

Answer 2

If you are using SQL Server 2016 Report Builder, this expression worked with me.

=Globals!PageNumber.ToString() +"/" + Globals!TotalPages.ToString()

Boomerang

The question is about SSRS 2008. – Untread

Can you explain how is it working for you? – Counterglow

Put the expression above in the "Expression" field of a box. I'm using Microsoft Report Builder tool. – Boomerang

Answer 3

Here is working expression:

=Microsoft.VisualBasic.Interaction.Switch(Parameters!LANGUAGE.Value = "en-US", "Page " + code.PageNumber().ToString() + " of " + code.TotalPages().ToString(), Parameters!LANGUAGE.Value = "es-MX", "Hoja " + code.PageNumber().ToString() + " de " + code.TotalPages().ToString(), 1 = 1, "Page " +code.PageNumber().ToString() + " of " + code.TotalPages().ToString())

Archdeaconry

Answer 4

In Visual Studio 2013 Report variables will be Visible under Report menu.

From Main Menu - Report > ReportProperties > Variables > Add

Chlamydeous

Answer 5

First Follow the steps 1 below to do pagination:
1)
1.1. Click the Details group in the Row Groups pane.
1.2. From the Tablix member Properties pane, expand “Group”-> “PageBreak”.
1.3. Set the “BreakLocation” to “End” and set the “Disable” property to the expression like below:

=IIF(rownumber(nothing) mod 40=0,false,true)  

The above point 1 is use to do pagination in Report output(Display only 40 records per page in output)

2) use custom code:

Public Function PageNumberno(val as integer) as String
     Dim str as String
     str =(val/40)
     Return str
End Function

3) Create Calculated column in Dataset and enter =0 in expression

4) In 2 Calculated Column
1)Pageno
2)No
in Dataset

In Report Body use Expression for PageNo :

=code.PageNumberno(Rownumber("DataSet1"))

use Expression for No :

 =IIF(Instr(code.PageNumberno(Rownumber("DataSet1"))
,".")<>0,
(Left(code.PageNumberno(Rownumber("DataSet1")),
(Instr(code.PageNumberno(Rownumber("DataSet1")),".")-1))+1)
,code.PageNumberno(Rownumber("DataSet1"))
)

5)Right click and insert column on Right Side and in Column name add code in Text box

=ReportItems!No.Value

Note: No is calculated Field column name.

6)Under AdvancedMode IN Row Group select Static and Set RepeatOnNewPage Properties to True

In the Above Column created under point 5 will display correct page no in every page in body of the report

I have Tried and its working Fine..Try it.

Hackett

I am getting this when running report in RB: The expression used for the calculated field 'No' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions. – Counterglow