Remove Question Mark and Show Correct Total Number of Pages in SSRS Report


By:   |   Updated: 2015-05-29   |   Comments (4)   |   Related: > Reporting Services Formatting


Problem

I am creating a new SQL Server Reporting Services report in Visual Studio. When I am in Preview mode in Visual Studio or when I view the report on the server, the total number of pages is incorrect and there is a question mark behind the total number of pages. How can I fix this problem like the one shown below?

Preview of report with total page number issue
Solution

We have to force SSRS to calculate the total number of pages. In the image below, we see our report in the Design window in Visual Studio. Note that we do not have a header or footer on this report.

Design of report with total page number issue

We need to add a header or a footer to the report. On the menu bar, we can select "Report" and the select "Add Page Header" or "Add Page Footer". For this tip we will choose "Add Page Header".

Add page header

The empty report header now appears in the report designer window. We will drag the report title up to the header and reduce the height of the header.

Empty header

Now we need to insert a text box into the header. We need to right-click on an empty space of the header. Select "Insert" and then "Text Box".

Insert text box

Right-click on the text box and choose "Expression..." to display the Expression builder window.

Select expression

In the Expression builder, choose "Built-in Fields" in the "Category" box. Double-click "OverallTotalPages" in the Item box. This will populate the "Set expression for: Value" text box. Click on "OK" to close the Expression builder window. This can only be done in a text box that resides in a header or footer.

Add the OverallTotalPages

We can see the populated text box in the next image.

Populated text box shown

Click on the "Preview" tab. Notice how the question mark has disappeared and the total number of pages is correct.

Preview the report to see the issue has been resolved
Next Steps

We can make this look neater by adding the current page along with the total number of pages. The Expression builder below shows how to do this.

Display Page x out of y in Expression builder

Now when we preview the report, we see the current page number with the total number of pages.

Display Page x out of y in Preview mode

Also, check out more tips and tutorials on SSRS MSSQLTips.com.



Last Updated: 2015-05-29


get scripts

next tip button



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





Comments For This Article




Friday, October 18, 2019 - 7:26:44 AM - Ian Back To Top (82819)

Lol, MS reporting services are so... quirky.

Thanks for this tip!


Wednesday, June 15, 2016 - 4:20:28 PM - subash Back To Top (41704)

 Great it works well. Since, we don't want to show the same information twice, we can also set the textbox at header as Visible=False in its properties

 


Tuesday, June 02, 2015 - 9:34:52 AM - Dan Back To Top (37358)

I also found some reports that did not have the page numbers in the header.  in that case, I just inserted a bogus IIF statement (like the xample below) in the header, so it would be evalusted but not displayed.  It seems to have worked too!

 

Example:   =IIF(Globals!OverallTotalPages < 0, Nothing, Nothing)

 


Tuesday, June 02, 2015 - 9:23:49 AM - Dan Back To Top (37357)

Thanks for the tip!  I just redeployed 49 RDL files, all of which were using "Globals!TotalPages" instead.  It's the little things :-)

 



download





Recommended Reading

SQL Server Reporting Services (SSRS) Controlling Report Page Breaks

Alternate Row Background Color in SQL Server Reporting Services Tablix and Matrix

Display a fixed number of rows per page for an SSRS report

Creating a Tabbed Report in SSRS

Conditional Formatting for SQL Server Reporting Services Reports








get free sql tips
agree to terms


Learn more about SQL Server tools