After export into Excel from SSRS, incorrect Page Number in the footer
Hi, I have defined "page x of y" in my report footer that shows correctly in SSRS. After export the report to Excel, I got "page 1 of 1", in Excel Footer, instead. How to correct it? Thanks.
October 3rd, 2008 11:00am
Hi, For this issue, you may try: 1. Check Printer Settings in Excel file. Please try to Print Preview in Excel, then check your current Page size for printing. 2. Compare data. How many data have been export to Excel file? Whether these data are same as your output. 3. In Report Designer, one report contains Page Header, Body and Page Footer. Please be sure that page number box is in Page Footer part. Hope these are helpful for you and waiting for your feedback.
October 8th, 2008 6:34am
Thanks Alfredl. let me give some more info regards this problem. 1. The data has exported to Excel without problem so that same amount data in both SSRS and Excel. 2. In Report Designer, the report has Body and Footer. Since the data in the Header caused cell merge in Excel, the data in Header have been moved into the Body. 3. The Page number has been defined as: ="Page " + CStr(Globals!PageNumber) + " of " + CStr(Globals!TotalPages). Have tried to break above from one textbox into two but it did not work. Any help will be much appreciated.
October 8th, 2008 9:25am
Hi, Based on the further information you provide, you may change your Expression where in Page Number Textbox as below. Your current Expression: ="Page " + CStr(Globals!PageNumber) + " of " + CStr(Globals!TotalPages) Please change it to: ="Page " & Globals!PageNumber & " of " & Globals!TotalPages If it works, please feel free let me know. J
October 9th, 2008 6:33am
Hi AlfredL, Thank you for the help. Unfortunately, the change you offered does not solve my problem. But one interesting point has been found as: After report to exported into Excel, in Excel window: 1. the "File --> Print preview" will show the page number as "Page 1 of 1" 2. the "Page Setup --> header/Footer" and then click "Page 1 of ?" in the Footer and then click "Ok" button to close "Header/Footer" popped up window. Then, the"File --> Print preview" will show the page number as "Page 1 of 40". Any idea to get correct the page number without going thru #2 each time a new report be exported? Thanks.
October 9th, 2008 10:56am
Hi, OK. Lets focus on #1 issue. I had tested above changed Page Number Expression on my computer and it worked. For your situation, let me check more detail information please. 1: Please check your current SQL Server Edition and Version. If you are using SQL 2005, please make sure it was updated to SP2. If you use others, please let me know.For checking these, please refer this link: http://support.microsoft.com/kb/321185/en-us2: For this new Expression, does it work fine in Report Designer when previewing it? If no, what is the detail symptoms?3: During the design, deploy and export processes, is there any error or warning information?4: If all of above are working fine, please try to separate Page Number and Total Pages items into two Text Boxes as below in Page Footer. After that, please export this report to Excel and check whether it works fine. Test Box 1: =Globals!PageNumber Test Box 2 =Globals!TotalPages5: If it doesnt work, please try to create new report contains more than one page data. After you do that, following Step 4.6: Check your EXCEL Version.
October 10th, 2008 6:12am
Hi AlfredL, #1, SQL Server 2008 and Visual Studio 2005 #2, Report Designer and SSRS have not error at all #3, No Error at all #4, Tried it and it does not work #5, No sure what do you ask? The report return 8 pages data in SSRS and 40 pages data in Excel (after click "page 1 of ?" in Header/Footer of "File-->Page Setup") #6, Excel 2000 (9.0.7616 SP-3) Thanks.
October 10th, 2008 11:31am
Hi, In your environment, I suggest that you use this tool of SQL Server 2008 Business Intelligence Development Studio, instead of Visual Studio 2005, to design your report. You may use the expression below to get Page Of Total Pages for Excel 2000. =format(Globals!PageNumber & "of" & Globals!TotalPages) If it still doesnt work, please try to upgrade your Excel program to latest version and see how it works.
October 16th, 2008 6:55am
I am having the same issue in RS 2008. The behaviour is different than in RS 2005. I created a blank report in RS 2008 with nothing in it except a footer with one textbox with the expression =Globals.PageNumber. I then exported it to excel. When I look at the page setup window in excel and click on the Custom Footer button under header/footer I see the value 1. I should see &[Page] in order for correct paging in excel. Then I did the same thing in RS 2005. Created a blank report with the same value in the footer. When I export that to excel (file test_RS2008.xls), I see &[Page] in the footer, which is what were looking for. So i get different behaviiour in RS 2005 and RS 2008. RS 2005 is the desired behaviour.
October 23rd, 2008 8:42am
Guys I want to add lil more complexity in the issue I have recently migrated my reports from 2005 to 2008 and when I export them to PDF I get incorrect page no. that is because after exporting the report there are few pages in PDF which dont have headers and those pages are not counted by the PDF any of you have any idea y this is happning.....PLS help me out
March 30th, 2009 10:34am
Hi Amitkk,Just as an FYI to everybody, the above behaviour in Excel was filed and fixed, and will be included in a future release.As for your issue, from 2005 to 2008, there were significant changes made to the pagination logic in PDF. If you can describe your situation with a little more detail, I'd be happy to help. -GerryThis posting is provided "AS IS" with no warranties, and confers no rights.
March 30th, 2009 1:27pm
Gerry:I have a report which has groupslet me write the format for u Headergroup 1 Group 2 ------------------------------ ------------------------------ ------------------------------ Sub total Group 2 ------------------------------- ------------------------------- sub total Group 2 Total Group 1 when I export this report to PDF hedder is supposed to be on every page then the body of the report but in between few pagesbodykeeps on moving on diff page without hedder being displayed on that page that causes the diff in no. of pages displayed on the report and counted by the PDF also as header is not displayed it is another issue.RegardsAmit
March 30th, 2009 2:55pm
Hi Amitkk,First thing I want you to check, is that header in your tablix? Or is that a report page header? If it is a tablix header, you wont see it on any pages that are paginated horizontally.If it is infact a report header,are you using any conditional visibility on these header items? Is it possible there is no data to display?-GerryThis posting is provided "AS IS" with no warranties, and confers no rights.
March 30th, 2009 7:37pm
Hi Gerry,The header is report header there is no conditional visibality item in the header and data for the header are lables which always have somevalues, also there are 640 pages in the report hedder is not showing up on 4-5 pagesRegardsAmit
March 31st, 2009 9:01am
Gerry:the detail row of the report keeps on moving to next page without headerRegardsAmit
March 31st, 2009 10:36am
Try this =Globals!PageNumber & " of " & Globals!TotalPagessan
April 30th, 2009 4:23pm
Hi there, I have created a fairly simple report for quarterly income analysis of a company, it is having one fixed column and quartery columns are dynamically generated depending data availability for quarter.I am exporting this report in excel. Prob 1: when i try to generate report for single quarter, header is just perfect as page width. but when all four quarters are generated the header expands and goes on second page also, which looks really bad. the problem is all the quarter coumns are on first page only then y this header is extending to second blank page and showing only header end lines.(ps: header is having border) prob 2: header is not repeating on every page as it repeats in PDF. it comes only on first page. Prob 3: footer is showing 1 of 1 on all the pages. Prob 4: cell height is not increasing to fit the data, data is hidden in the cell, i am not able to see the text completely.(ps: can grow property is not working , i am no able to find Interactive size property any where in header property, is there any way of increasing height in excel)urgent help is needed!!!!!!!!
December 29th, 2009 10:36pm
Hi Sadaf,Prob 1: when i try to generate report for single quarter, header is just perfect as page width. but when all four quarters are generated the header expands and goes on second page also, which looks really bad. the problem is all the quarter coumns are on first page only then y this header is extending to second blank page and showing only header end lines.(ps: header is having border)Ans: I think the problem with Layout of the report, Report Menu ->Report Properties -> Layout Tab, try to change width and height and then test the report.prob 2: header is not repeating on every page as it repeats in PDF. it comes only on first page.Ans: Header should be repeat in all the pages since your using Matrix control by default. Even, we have created a report which is containing both data for Months and querters wise and also getting header in all pages becase it's the nature of matrix.Thanks,Rama
January 25th, 2010 12:34pm
This works for me ="Page " & Globals!PageNumber & " of " & Globals!TotalPages Thanks ALFRED.L
February 24th, 2010 4:58am
For .rdlc , assign text box value as <Value>="Page " & Globals!PageNumber & " of " & Globals!TotalPages <Value> For .rdl , assign text box value as <Value>="Page " & Globals!PageNumber & " of " & Globals!TotalPages</Value>
February 24th, 2010 6:38am
I'm having issues when I export to excel: Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString() display's: "1 of 1" for all my pages in excel, however it renders properly in the report viewer??? Any suggestions would be helpful. Thanks, Mike
December 22nd, 2010 5:57pm
its working ="Page " & Globals!PageNumber & " of " & Globals!TotalPages
July 28th, 2012 12:33pm