Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (3)   |   Related Tips: > 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 Update:






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





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 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

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

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

 


Learn more about SQL Server tools