By: Dallas Snider | 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?
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.
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".
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.
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".
Right-click on the text box and choose "Expression..." to display the Expression builder window.
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.
We can see the populated text box in the next image.
Click on the "Preview" tab. Notice how the question mark has disappeared and the total number of pages is correct.
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.
Now when we preview the report, we see the current page number with the total number of pages.
Also, check out more tips and tutorials on SSRS MSSQLTips.com.
- SQL Server Reporting Services Tutorial
- SQL Server Reporting Services (SSRS) Repeating Headers On Pages
- SQL Server Reporting Services (SSRS) Controlling Report Page Breaks
- Add a linear trendline to a graph in SQL Server Reporting Services
- Create a Greenbar Report in SQL Server Reporting Services
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
