SSRS 2008 R2 - Excel output not formatting to page size
excel, formatting, reporting-services, sql-server, ssrs-2008-r2
Solution
The short answer is that you can't exactly do what you want with the Excel renderer. Some workarounds that come to mind:
- Filling an Excel template with data might be an option, but is more of a job for SSIS, not reporting services.
- Send the report in PDF for printing, and if needed in Excel as well.
- Re-layout the report so it plays well with the default printing of Excel. This won't be very pretty, you'd need to either make columns much smaller (and perhaps rotate headers using the WritingMode property) or turn columns into row groups somehow.
- (hack warning!) create an Excel macro or something alike for your users, that does some printing-quick-fixes.
Some background Unfortunately SSRS gives you only a small bit of control over how the report is rendered in the various rendering extensions. There's this MSDN page on rendering extensions (additional emphasis mine) with some useful info:
Soft page-break renderers: Soft page-break renderers maintain the report layout and formatting. The resulting file is optimized for screen-based viewing and delivery, such as on a Web page. The available soft page-break renderers are: Microsoft Excel, Microsoft Word, Web archive (MHTML), and HTML.
Hard page-break renderers: Hard page-break renderers maintain the report layout and formatting. The resulting file is optimized for a consistent printing experience, or to view the report online in a book format. The available hard page-break renderers are supported: TIFF and PDF.
So, if you want to optimize for printing experience, you should probably use the PDF export. You can then play around with the page size and margins to fit as much info as possible on a page, and let the client program (probably Adobe Reader) worry about printing it nicely.
Problem
I have a batch of reports that are set up to print very nicely in landscape on A4 page. But when I set the default format to Excel, the resulting spreadsheet, when printed without changing anything in the print setup, is wider than an A4 page so of course it gets broken up over mulitple pages (i.e: each page is 2 pages wide rather than 1) Most of our users just want to print these as soon as they arrive via email (but they still want Excel format so they can re-sort, cut and paste, etc) so how can I make Excel keep the print format defined in the report in SSRS so the users don't have to mess about with print settings? (These are daily reports so this is driving our users mad as some of them may get 4 or 5 reports!) Do I have to use an Excel template (can this even be done?) or is there a way to acheieve what I want via SSRS? TIA for any help.... Mike