Accessing SSRS Reports from SharePoint Site using C#

Introduction

This tip demonstrates how to access SSRS Reports stored on SharePoint site using C#. It primarily focuses on demonstrating this using a Desktop application that can be developed using Windows Forms / WPF.

In this article, we will learn how to :

• Display the SSRS report using the ReportViewer control in .NET.
• Convert the SSRS Report to PDF Attachment that can be used by the SMTPClient object in .NET to send this report as an Email Attachment (PDF).
• Save the report as PDF.

Background

Microsoft Reports can be designed using two different methods - as a Local Report and as a Server Report. For designing the local report, the report DataSet (.XSD file) has to be a part of the .NET Project which is using the report. In case of any changes to the design or data presented by the report, the entire application needs to be re-compiled and re-deployed.

However, if the report is developed as a Server Report, this problem can be eliminated. We will be storing the SSRS report (built locally) on the SharePoint site, and our Desktop Application built using WPF will be accessing this Server Report using C#.

Using the Code

In our example, we have a class named SSRSReport, which has different methods to perform the tasks mentioned in the Introduction section of this article.

/// <summary>
/// Handles frequently used functionalities in ReportViewer Controls to display SSRS reports locally.
/// </summary>
public class SSRSReport
{
    private static String GetReportServerURL()
    {
        DataTable datatable = new DataTable();

    //Execute the stored procedure to get the Report Server URL from the database.
        DBConnect.FillDataTable("GetSSRSReportServerURL", datatable, null);
        if (datatable == null || datatable.Rows.Count == 0)
            return null;
        else
            return datatable.Rows[0]["PARAMETER_VALUE"].ToString().Trim();
    }

    /// <summary>
    /// Open the SSRS report based on the name of the report specified.
    /// </summary>
    /// <param name="reportViewer">ReportViewer
    /// object used to render the SSRS report on screen.</param>
    /// <param name="reportPath">Name of the Report
    /// (.rdl) data uploaded on the server.</param>
    public static void DisplayReport(ReportViewer reportViewer, String reportPath)
    {
        try
        {
            reportViewer.ProcessingMode = ProcessingMode.Remote;
            ServerReport serverreport = reportViewer.ServerReport;
            ICredentials credentials = CredentialCache.DefaultCredentials;
            ReportServerCredentials rscredentials = serverreport.ReportServerCredentials;
            rscredentials.NetworkCredentials = credentials;
            serverreport.ReportServerUrl = new Uri(GetReportServerURL());
            serverreport.ReportPath = reportPath;

            reportViewer.ShowParameterPrompts = false;
            reportViewer.ShowPrintButton = true;

            reportViewer.Refresh();
            reportViewer.SetDisplayMode(DisplayMode.PrintLayout);
        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// Open the SSRS report based on the name of the report and Report Parameters specified.
    /// </summary>
    /// <param name="reportViewer">ReportViewer
    /// object used to render the SSRS report on screen.</param>
    /// <param name="reportPath">Name of the Report
    /// (.rdl) data uploaded on the server.</param>
    /// <param name="reportParameterList">
    /// List of Report parameters.</param>
    public static void DisplayReport(ReportViewer reportViewer,
        String reportPath, List<ReportParameter> reportParameterList)
    {
        try
        {
            reportViewer.ProcessingMode = ProcessingMode.Remote;
            ServerReport serverreport = reportViewer.ServerReport;

            ICredentials credentials = CredentialCache.DefaultCredentials;
            ReportServerCredentials rscredentials = serverreport.ReportServerCredentials;
            rscredentials.NetworkCredentials = credentials;
            serverreport.ReportServerUrl = new Uri(GetReportServerURL());
            serverreport.ReportPath = reportPath;

            reportViewer.ShowParameterPrompts = false;
            reportViewer.ShowPrintButton = true;

            if (reportParameterList != null)
            {
                foreach (ReportParameter param in reportParameterList)
                {
                    serverreport.SetParameters(param);
                }
            }

            reportViewer.Refresh();
            reportViewer.SetDisplayMode(DisplayMode.PrintLayout);
        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// Convert the SSRS report on the local report viewer to an Attachment.
    /// This can be used to attach the PDF to an email.
    /// </summary>
    /// <param name="reportViewer">ReportViewer control.</param>
    /// <param name="fileName">Name of the PDF data.</param>
    /// <returns>PDF File as an Attachment that
    /// can be attached to an email using SMTPClient.</returns>
    public static Attachment ConvertToPDFAttachment(ReportViewer reportViewer, String fileName)
    {
        try
        {
            byte[] data;
            if (reportViewer.ServerReport != null)
                data = reportViewer.ServerReport.Render("PDF");
            else
                data = reportViewer.LocalReport.Render("PDF");
            Attachment att = new Attachment(new MemoryStream(data), fileName);
            return att;
        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// Saves the report from the local reportViewer as PDF.
    /// To execute this method, the reportviewer needs to already contain the SSRS report.
    /// </summary>
    /// <param name="reportViewer">ReportViewer
    /// control that displays the report.</param>
    /// <param name="filePath">Path of the file
    /// to which the report should be stored as PDF.</param>
    /// <returns>True,if saved successfully ; False,otherwise.</returns>
    public static Boolean SaveAsPDF(ReportViewer reportViewer, String filePath)
    {
        try
        {
            byte[] data;
            if (reportViewer.ServerReport != null)
                data = reportViewer.ServerReport.Render("PDF");
            else
                data = reportViewer.LocalReport.Render("PDF");

            FileStream fs = new FileStream(filePath, FileMode.Create);
            fs.Write(data, 0, data.Length);
            fs.Close();
            return true;
        }
        catch(Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// Saves the report from the SSRS Report Server as PDF.
    /// </summary>
    /// <param name="reportViewer">ReportViewer
    /// control that displays the report.</param>
    /// <param name="filePath">Path of the file
    /// to which the report should be stored as PDF.</param>
    /// <param name="reportPath">Name of the Report
    /// (.rdl) data uploaded on the server.</param>
    /// <returns>True,if saved successfully ; False,otherwise.</returns>
    public static Boolean SaveAsPDF(ReportViewer reportViewer, String filePath, String reportPath)
    {
        try
        {
            DisplayReport(reportViewer, reportPath);
            byte[] data;
            if (reportViewer.ServerReport != null)
                data = reportViewer.ServerReport.Render("PDF");
            else
                data = reportViewer.LocalReport.Render("PDF");

            FileStream fs = new FileStream(filePath, FileMode.Create);
            fs.Write(data, 0, data.Length);
            fs.Close();
            return true;
        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// Saves the report from the SSRS Report Server as PDF.
    /// </summary>
    /// <param name="reportViewer">ReportViewer
    /// control that displays the report.</param>
    /// <param name="filePath">Path of the file
    /// to which the report should be stored as PDF.</param>
    /// <param name="reportPath">Name of the Report
    /// (.rdl) data uploaded on the server.</param>
    /// <param name="reportParameterList">List of Report parameters.</param>
    /// <returns>True,if saved successfully ; False,otherwise.</returns>
    public static Boolean SaveAsPDF(ReportViewer reportViewer,
        String filePath, String reportPath, List<ReportParameter> reportParameterList)
    {
        try
        {
            DisplayReport(reportViewer, reportPath,reportParameterList);
            byte[] data;
            if (reportViewer.ServerReport != null)
                data = reportViewer.ServerReport.Render("PDF");
            else
                data = reportViewer.LocalReport.Render("PDF");

            FileStream fs = new FileStream(filePath, FileMode.Create);
            fs.Write(data, 0, data.Length);
            fs.Close();
            return true;
        }
        catch (Exception)
        {
            throw;
        }
    }
}

Now we will see an example of how this class can be used:

public class SSRSReportViewer
{
	private ReportViewer _reportViewer;

	//Display Report with NO parameters.
	SSRSReport.DisplayReport(_reportViewer, ReportPath);

	//Display Report WITH Parameters.
	List<ReportParameter> paramList = new List<ReportParameter>();
	paramList.Add(new ReportParameter("param1", param1);
	paramList.Add(new ReportParameter("param2", param2);
	SSRSReport.DisplayReport(_reportViewer, ReportPath ,paramList);

	//Convert the Report to PDF Attachment.
	//This Attachment object can be used along with SMTPClient 
	//object to send the report as a PDF attachment with an email.
	Attachment att = SSRSReport.ConvertToPDFAttachment(this._reportViewer, fileName);

	//Save Report with NO parameters already displayed on the reportViewer as PDF.
	SSRSReport.SaveAsPDF(_reportViewer, filePath);

	//Save Report with NO Parameters as PDF without displaying it in the ReportViewer.
	SSRSReport.SaveAsPDF(_reportViewer, filePath, ReportPath);

	//Save the Report WITH parameters as PDF.
	SSRSReport.SaveAsPDF(_reportViewer, filePath, ReportPath, paramList);
}

Points of Interest

Nothing discovered so far. It seems a pretty straightforward process.

History

This is the first version of the DLL. There will be more updates as we come across bugs and/or additional functionality.