Aug 24 2012

SSRS Report with VB.NET or C#.NET using Visual Studio

Generate SQL Server Reporting Services (SSRS) Report as PDF from URL with VB.NET or C#.NET

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">
<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

  1. Set Report PageSize to 8.5in, 11in for portrait or 11in, 8.5in for landscape.
  2. Set the Report Margins as you would like.
  3. Make sure your Report Body (white staging area in Visual Studio) does not exceed your Report PageSize minus your margins.
  4. Remove any unused white space in Report Body no matter what you set the sizes at.

Reff: http://www.jensbits.com/2012/01/23/generate-sql-server-reporting-services-ssrs-report-as-pdf-from-url-with-vb-net-or-c-net/

Advertisements
By simplemsexchange • Posted in C#, VB.NET
0

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Google+ photo

You are commenting using your Google+ account. ( Log Out / Change )

Cancel

Connecting to %s