« SQL CPU running at 100 percent on SCOM Reporting ServerFastCGI PHP on Windows 2008 »
Mar 17

SQL Reporting Services Render PDF in A4 paper size instead of Letter

SCOM, SQL


 

Update 17 december 2009: This procedure was written on SQL 2005. For SQL 2008 I have added some lines at the bottom of the post, so if you are on 2008 please also check there before applying and it will work for you as well.
End of update.

Everywhere I come to deploy SCOM a common question is to get lots of reports out of it. However after creating the reports and scheduling them for delivery it mostly stops. However as soon as somebody tries to print the reports the printers usually hang asking for Letter size paper.

The default behavior for the PDF rendering in SQL Reporting Services is to render it to Letter paper size. So how to get it to render to A4 paper size?

After some searching with a DBA colleague of mine we came across some MSDN pages explaining the options to create custom rendering options.

What to do:
On your reporting server go to the Rsreportserver.config file located in the reportserver directory. In my case this was at C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer . We need to edit this file, so open it with Notepad or something like that (something that does not break up the XML formatting in the file).

Scroll down to the section starting with Render.

You will see entries like this:

Code:

<extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport,Microsoft.ReportingServices.ImageRendering">
      <extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering">

What we want to do here is for instance add a custom PDF extension. So here goes. Create some space below the PDF entry and paste something like the following:

Code:

<Extension Name="PDF (A4 Landscape)" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering">
        <OverrideNames>
            <Name Language="en-US">PDF in A4 Landscape</Name>
        </OverrideNames>
        <Configuration>
            <DeviceInfo>
                <OutputFormat>PDF</OutputFormat>
                <PageHeight>8.27in</PageHeight>
                <PageWidth>11.69in</PageWidth>
            </DeviceInfo>
        </Configuration>
</Extension>
<Extension Name="PDF (A4 Portrait)" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering">
        <OverrideNames>
            <Name Language="en-US">PDF in A4 Portrait</Name>
        </OverrideNames>
        <Configuration>
            <DeviceInfo>
                <OutputFormat>PDF</OutputFormat>
                <PageHeight>11.69in</PageHeight>
                <PageWidth>8.27in</PageWidth>
            </DeviceInfo>
        </Configuration>
</Extension>

This adds two entries to your report server export drop down list.
Rules:
- make sure your name is unique (Extension name).
- The overridename entry is what you see in the drop down list.
- pageheight and pagewidth must be defined in Inches like in the example above.
- A4 is defined mostly as 8.3in x 11.7in , but in some cases the result is better when using 8.27in x 11.69 in.

Save the file and immediately you can use these formats. No restarting of services needed here if you are on the SQL 2005 version.
Good luck!

Original page on MSDN covering the topic.

Update 17 december 2009 on SQL 2008 version:

In the lines of code please use "PDFRenderer" in stead of "PDFReport". Also you must restart IIS on the box before it will show in reporting services. This is the difference with the 2005 version.
Thanks to Peter Yang from Microsoft for pointing out these changes.

As it seems to be case sensitive I just placed the SQL 2008 Reporting Services code block below for you to cut and paste.

Code:

<Extension Name="PDF (A4 Landscape)" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering">
         <OverrideNames>
             <Name Language="en-US">PDF in A4 Landscape</Name>
         </OverrideNames>
         <Configuration>
             <DeviceInfo>
                 <OutputFormat>PDF</OutputFormat>
                 <PageHeight>8.27in</PageHeight>
                 <PageWidth>11.69in</PageWidth>
             </DeviceInfo>
         </Configuration>
</Extension>
<Extension Name="PDF (A4 Portrait)" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering">
         <OverrideNames>
             <Name Language="en-US">PDF in A4 Portrait</Name>
         </OverrideNames>
         <Configuration>
             <DeviceInfo>
                 <OutputFormat>PDF</OutputFormat>
                 <PageHeight>11.69in</PageHeight>
                 <PageWidth>8.27in</PageWidth>
             </DeviceInfo>
         </Configuration>
</Extension>

17 comments

  1. § Adam said on : 03/31/09 @ 05:30
    Thanks very much. This fixed a gripe I'd had with Reporting Services that I hadn't really looked in to fixing until now.

    Appreciate your effort in spelling it out so simply.
  2. § Stewart Anderson said on : 06/02/09 @ 00:21
    Awesome, fixed it perfectly, thanks :)
  3. § Glenn Email said on : 10/28/09 @ 04:09
    Great stuff. This worked. Can you also export to Word?
  4. § Bob Cornelissen® Email said on : 11/16/09 @ 18:58
    Rendering to Word is not included in SQL 2005 reporting services. There is a third party solution at http://www.aspose.com/categories/ssrs-rendering-extensions/aspose.words-for-reporting-services/default.aspx but it looks expensive.

    However it is included in SQL 2008 Report Services. I have not yet looked into possible Letter to A4 differences with that one, but it should be possible in the same way as mentioned above I assume.
  5. § Kapila Email said on : 12/16/09 @ 11:06
    I tried doing the same thing as suggested above but could not see the custom PDF extension in the export drop down list .
  6. § Bob Cornelissen® Email said on : 12/17/09 @ 13:15
    This seems to not work with SQL 2008 version of reporting services. I am trying to find out why. As soon as I know I will share.
  7. § Bob Cornelissen® Email said on : 12/17/09 @ 15:13
    Added update for SQL 2008 version. Remember to change that one word in the extension line and to restart IIS completely.
  8. § Markus Email said on : 01/14/10 @ 15:35
    Have an issue when rendering to PDF on the Dev box the pdf looks great. When rendering from share point the rendering to pdf is making columns go to 2 lines. using an arial norrow 5 font.

    Any idea?
  9. § Bob Cornelissen® Email said on : 01/18/10 @ 08:33
    Hi Markus, are you using a sharepoint integration like in http://msdn.microsoft.com/en-us/library/bb326358.aspx ? I will play with the sharepoint integration again soon on my dev boxes and will certainly try and see what happens. I do not have an idea why this would happen.
  10. § Dipen Parekh Email said on : 03/11/10 @ 16:23
    Another possible way of rendering the pdf in the required size is by using the url access feature of SSRS.

    Note: This applies only if you have very few report parameters since URL length limit in SSRS is 256 characters.

    All you have to do is add a textbox in the header area of the report, give some text value as "Export to PDF" and in the 'Action' section of the textbox select 'Go to URL' and give the URL expression as below

    =Globals!ReportServerUrl + "?"
    + Globals!ReportFolder + "/"
    + Globals!ReportName + "&rs:Command=Render"
    +"&rs:Format=PDF"
    +"&rc:PageWidth=11 in"
    +"&rc:PageHeight= 15 in"
    +"&Param1=" + CStr(Parameters!Param1.Value)
    +"&Param2=" + CStr(Parameters!Param2.Value)

    The values for PageWidth and PageHeight can be made even more dynamic by getiing it from an internal Parameter which can be varied based on some condition in the report, then the URL expression will look like

    =Globals!ReportServerUrl + "?"
    + Globals!ReportFolder + "/"
    + Globals!ReportName + "&rs:Command=Render"
    +"&rs:Format=PDF"
    +"&rc:PageWidth=" + CStr(Parameters!PDFPageWidth.Value) +"in"
    +"&rc:PageHeight=" + CStr(Parameters!PDFPageHeight.Value) + "in"
    +"&Param1=" + CStr(Parameters!Param1.Value)
    +"&Param2=" + CStr(Parameters!Param2.Value)

    You may also want to hide the text "Export to PDF" in the PDF genereated then in that case use another hidden parameter (it should be an hidden parameter and not an internal parameter) and then set the visibility of the text box to Show Hide based on an expression and give the paramter for its value parameter name,
    then the URL expression will look like

    =Globals!ReportServerUrl + "?"
    + Globals!ReportFolder + "/"
    + Globals!ReportName + "&rs:Command=Render"
    +"&rs:Format=PDF"
    +"&rc:PageWidth=" + CStr(Parameters!PDFPageWidth.Value) +"in"
    +"&rc:PageHeight=" + CStr(Parameters!PDFPageHeight.Value) + "in"
    +"&HideExporttoPDF=true"
    +"&Param1=" + CStr(Parameters!Param1.Value)
    +"&Param2=" + CStr(Parameters!Param2.Value)
  11. § davida Email said on : 03/26/10 @ 14:28
    Hi Dipen, i am trying to use your solution but, the report is not opening a new window or displaying in the adobe reader window. i used your example to display as text and can copy the url, paste into a browser and get the desired results. i am not sure if the action is not firing or if i have missed something.
    thanks
  12. § Dipen Parekh Email said on : 04/08/10 @ 16:31
    Hi davida,

    If the text renders as a hyperlink (which it should since you have attached an action to it.) then it should work. Also this will not work in Preview mode, it works only after you deploy it to a report server

    Please make sure if you have attached an action to the textbox by going to Text Properties then select 'Action' from left then select Go to URL' and enter the value as I mentioned previously.

    Thanks,
    Dipen
  13. § dominique Email said on : 06/07/12 @ 07:09
    Hello Bob,
    I did the change on the Data Warehouse server, IISRESET and restart SQL Reporting Services. Now I am checking the RMS but I don't see the two new options...
    These are two different servers (DW1 and RMS1) did I miss a step?

    Thanks,
    Dom
  14. § dominique Email said on : 06/07/12 @ 19:33
    *****
    Hello Bob,

    Sorry I missed the comment Update 17 december 2009 on SQL 2008 version:

    In the lines of code please use "PDFRenderer" in stead of "PDFReport

    it works like a charm now.,..

    Thanks,
    DOm
  15. § dominique Email said on : 06/07/12 @ 19:42
    Hi Bob,

    The change works fine... BUT the report content remains the same it just have more blank on the right side of the page as it is in landscape instead of portrait !!! I was expecting the chart to expand on the landscape format to make it less crowdy within the 8" ... expanded to 11"

    Thanks,
    Dom
  16. § Bob Cornelissen® Email said on : 06/15/12 @ 10:05
    HI Dom,
    this is a change in the SSRS config file followed by an iisreset. If you than run another report you should be able to export it in those formats. Otherwise shoot me a private email and we'll see how to solve it.
  17. § Anneliese Denos said on : 02/10/13 @ 03:30
    I blog often and I truly thank you for your information. This great article has really peaked my interest. I'm going to take a note of your website and keep checking for new information about once per week. I subscribed to your Feed as well.

This post has 1 feedback awaiting moderation...

Leave a comment


Your email address will not be revealed on this site.
PoorExcellent
(Line breaks become <br />)
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)
Contact / Help. ©2013 by Bob Cornelissen. evoCore.
Design & icons by N.Design Studio. Skin by Tender Feelings / Evo Factory.
Entries RSS Comments RSS Log in