Generating an existing SQL Server Reporting Services (SSRS) report to a PDF via a web URL is a convenient way to distribute SSRS reports, invoices, or anything else from SSRS. This post focuses on an existing report; however, links to the basic set up for a report are included. The code to output an existing report as PDF are given and uses a ReportViewer control.
You can skip to the PDF Output section if your server is all set up with the ReportViewer controls.
The pieces and parts to output an SSRS report as a PDF were culled from several disparate sources. All sources are given appropriate credit for their work.
As an added bonus, VB.NET and C#.NET versions are shown.
Add Report Viewer Redistributable to Server
The ReportViewer controls need to be installed on the server and Microsoft provides the redistributable for that in their download center.
The Microsoft Report Viewer 2008 Redistributable Package includes Windows Forms and ASP.NET Web server controls for viewing reports designed using Microsoft reporting technology.
Add HTTP Handler Web Server
The server has to know what it is dealing with when it is asked to generate a ReportViewer control. To help the server out, add the appropriate handlers.
Brite Global provides some excellent instructions on how to add the handlers.
Create Report on SSRS Server
The “Who Needs SQL Server’s Reporting Services?” article by DataSprings is an excellent tutorial for getting started with your first report.
Add Generic User to Report Server
For security reasons, a generic user will need to be added to the SSRS server. These credentials will be used by the .aspx page to access the report.
Give this user “Browser – May view folders, reports and subscribe to reports” only rights.
This generic (aka anonymous) user’s credentials will be stored in a separate config file and pulled into the web.config. That way if they change, you are not mucking about in the web.config file.
Create a appSetting.config and add the following information:
<appSettings>
<add key="SSRS" value="http://YOUR-REPORTSERVER-URL" />
<add key="rvUser" value="GENERIC-USERNAME" />
<add key="rvPassword" value="GENERIC-PASSWORD" />
<add key="rvDomain" value="YOUR-REPORTSERVER-DOMAIN" />
</appSettings>
Pop it into the web.config as such:
<appSettings file="appSettings.config"> <!-- Could have other keys here --> </appSettings>
The authorization is handled by using the IReportServerCredentials Interface which “allows applications to provide credentials for connecting to a Reporting Services report server.”
PDF Output
Add the ReportViewer control the an .aspx page by adding an assembly reference and the control itself:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="YOUR-FILE_NAME.vb" Inherits="YOUR-INHERITS" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<rsweb:ReportViewer ID="ReportViewer1" runat="server">
</rsweb:ReportViewer>
</div>
</form>
</body>
</html>
The code behind page got a lot of help from Viral at Beyond Relational’s post “Generating and exporting SSRS reports programatically using Report Viewer Control” and using Microsoft’s IReportServerCredentials Interface that was mentioned earlier:
VB.NET
Imports System.Net
Imports System.IO
Imports System.Security.Principal
Imports Microsoft.Reporting.WebForms
Partial Class YOUR_PAGE_CLASS
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
SetReportParameters()
End Sub
Private Sub SetReportParameters()
'Set Processing Mode
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote
' Set report server and report path
ReportViewer1.ServerReport.ReportServerUrl = _
New Uri(SSRS)
ReportViewer1.ServerReport.ReportPath = _
"/YOUR-REPORT-PATH"
Dim paramList As New Generic.List(Of ReportParameter)
Dim pInfo As ReportParameterInfoCollection
pInfo = ReportViewer1.ServerReport.GetParameters()
'if you have report parameters - add them here
paramList.Add(New ReportParameter("PARAM1-EXAMPLE", "1", True))
paramList.Add(New ReportParameter("PARAM2-EXAMPLE", "2", True))
ReportViewer1.ServerReport.SetParameters(paramList)
' Process and render the report
ReportViewer1.ServerReport.Refresh()
'output as PDF
Dim returnValue As Byte()
Dim format As String = "PDF"
Dim deviceinfo As String = ""
Dim mimeType As String = ""
Dim encoding As String = ""
Dim extension As String = "pdf"
Dim streams As String() = Nothing
Dim warnings As Microsoft.Reporting.WebForms.Warning() = Nothing
returnValue = ReportViewer1.ServerReport.Render(format, deviceinfo, mimeType, encoding, extension, streams, warnings)
Response.Buffer = True
Response.Clear()
Response.ContentType = mimeType
Response.AddHeader("content-disposition", "attachment; filename=YOUR-OUTPUT-FILE-NAME.pdf")
Response.BinaryWrite(returnValue)
Response.Flush()
Response.End()
End Sub
Protected Sub Page_Init(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Init
ReportViewer1.ServerReport.ReportServerCredentials = _
New MyReportServerCredentials()
End Sub
End Class
<Serializable()> _
Public NotInheritable Class MyReportServerCredentials
Implements IReportServerCredentials
Public userName As String = ConfigurationManager.AppSettings("rvUser")
Public password As String = ConfigurationManager.AppSettings("rvPassword")
Public domain As String = ConfigurationManager.AppSettings("rvDomain")
Public ReadOnly Property ImpersonationUser() As WindowsIdentity _
Implements IReportServerCredentials.ImpersonationUser
Get
'Use the default windows user. Credentials will be
'provided by the NetworkCredentials property.
Return Nothing
End Get
End Property
Public ReadOnly Property NetworkCredentials() As ICredentials _
Implements IReportServerCredentials.NetworkCredentials
Get
'Read the user information from the web.config file.
'By reading the information on demand instead of storing
'it, the credentials will not be stored in session,
'reducing the vulnerable surface area to the web.config
'file, which can be secured with an ACL.
If (String.IsNullOrEmpty(userName)) Then
Throw New Exception("Missing user name from web.config file")
End If
If (String.IsNullOrEmpty(password)) Then
Throw New Exception("Missing password from web.config file")
End If
If (String.IsNullOrEmpty(domain)) Then
Throw New Exception("Missing domain from web.config file")
End If
Return New NetworkCredential(userName, password, domain)
End Get
End Property
Public Function GetFormsCredentials(ByRef authCookie As Cookie, _
ByRef userName As String, _
ByRef password As String, _
ByRef authority As String) _
As Boolean _
Implements IReportServerCredentials.GetFormsCredentials
authCookie = Nothing
userName = Nothing
password = Nothing
authority = Nothing
'Not using form credentials
Return False
End Function
End Class
C#
using System;
using System.Configuration;
using System.Net;
using System.Security.Principal;
using Microsoft.Reporting.WebForms;
partial class YOUR_PAGE_CLASS : System.Web.UI.Page
{
public string SSRS = ConfigurationManager.AppSettings["SSRS"];
protected void Page_Load(object sender, System.EventArgs e)
{
SetReportParameters();
}
private void SetReportParameters()
{
//Set Processing Mode
//ReportViewer1.ProcessingMode = ProcessingMode.Remote
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
// Set report server and report path
ReportViewer1.ServerReport.ReportServerUrl = new Uri(SSRS);
ReportViewer1.ServerReport.ReportPath = "/YOUR-REPORT-PATH";
System.Collections.Generic.List<ReportParameter> paramList = new System.Collections.Generic.List<ReportParameter>();
ReportParameterInfoCollection pInfo = default(ReportParameterInfoCollection);
pInfo = ReportViewer1.ServerReport.GetParameters();
//if you have report parameters - add them here
paramList.Add(new ReportParameter("PARAM1-EXAMPLE", "1", true));
paramList.Add(new ReportParameter("PARAM1-EXAMPLE", "2", true));
ReportViewer1.ServerReport.SetParameters(paramList);
// Process and render the report
ReportViewer1.ServerReport.Refresh();
//output as PDF
byte[] returnValue = null;
string format = "PDF";
string deviceinfo = "";
string mimeType = "";
string encoding = "";
string extension = "pdf";
string[] streams = null;
Microsoft.Reporting.WebForms.Warning[] warnings = null;
returnValue = ReportViewer1.ServerReport.Render(format, deviceinfo, out mimeType, out encoding, out extension, out streams, out warnings);
Response.Buffer = true;
Response.Clear();
Response.ContentType = mimeType;
Response.AddHeader("content-disposition", "attachment; filename=YOUR-OUTPUT-FILE-NAME.pdf");
Response.BinaryWrite(returnValue);
Response.Flush();
Response.End();
}
protected void Page_Init(object sender, System.EventArgs e)
{
ReportViewer1.ServerReport.ReportServerCredentials = new MyReportServerCredentials();
}
public YOUR-PAGE-CLASS()
{
Init += Page_Init;
Load += Page_Load;
}
}
[Serializable()]
public sealed class MyReportServerCredentials : IReportServerCredentials
{
public string userName = ConfigurationManager.AppSettings["rvUser"];
public string password = ConfigurationManager.AppSettings["rvPassword"];
public string domain = ConfigurationManager.AppSettings["rvDomain"];
public WindowsIdentity ImpersonationUser
{
//Use the default windows user. Credentials will be
//provided by the NetworkCredentials property.
get { return null; }
}
public ICredentials NetworkCredentials
{
get
{
//Read the user information from the web.config file.
//By reading the information on demand instead of storing
//it, the credentials will not be stored in session,
//reducing the vulnerable surface area to the web.config
//file, which can be secured with an ACL.
if ((string.IsNullOrEmpty(userName)))
{
throw new Exception("Missing user name from web.config file");
}
if ((string.IsNullOrEmpty(password)))
{
throw new Exception("Missing password from web.config file");
}
if ((string.IsNullOrEmpty(domain)))
{
throw new Exception("Missing domain from web.config file");
}
return new NetworkCredential(userName, password, domain);
}
}
public bool GetFormsCredentials(out Cookie authCookie,
out string userName, out string password,
out string authority)
{
authCookie = null;
userName = null;
password = null;
authority = null;
// Not using form credentials
return false;
}
}
Now just run your URL in a browser (http://YOUR-DOMAIN.com/YOUR-PAGE.aspx) and the File Download warning box will pop up for the PDF.
PDF Layout Tips and Things to Look Out For
- Set Report PageSize to 8.5in, 11in for portrait or 11in, 8.5in for landscape.
- Set the Report Margins as you would like.
- Make sure your Report Body (white staging area in Visual Studio) does not exceed your Report PageSize minus your margins.
- Remove any unused white space in Report Body no matter what you set the sizes at.
Google+
Twitter
About