« 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 Add comments


 

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. § Insurance Lawyers Los Angeles said on : 04/28/10 @ 10:19
    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.
  14. § Hunter Bagwell said on : 09/03/10 @ 05:18
    Really great, practicly explained and Helpful Guidelines.
  15. § Isaac Keeble said on : 09/03/10 @ 08:52
    Your Weblog article is very intersting and fanstic,at the Exact time the Weblog theme is unique and perfect,great job.To your
  16. § wow power leveling Email said on : 09/03/10 @ 10:42
    ryer
  17. § Lupe Motamedi said on : 09/03/10 @ 17:27
    Amazing Article Maki. Like Examining copywriting book from Joe Sugarman or Dan Kennedy. Now I just have to apply the

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)
Contact. ©2010 by Bob Cornelissen. evoCore.
Design & icons by N.Design Studio. Skin by Tender Feelings / Evo Factory.
Entries RSS Comments RSS Log in