How to Run SSRS Reports Locally

Introduction

This tip discusses how to generate a report that was created in rdl format, from an ASP.NET website without using SSRS server.

Using the Code

A typical corporate setup would have a website running on one or more (load balanced) servers and SSRS reports running out of a separate Report server (or an Application server or from the Database server itself). In such scenarios, developers make use of Visual Studio Business Intelligence shell to create SSRS reports in .rdl format and deploy them to the SSRS Report server. Such SSRS reports are accessed from the ASP.NET website through Report Viewer.

Code for such a typical scenario (Report server URL and the folder name are fetched from config file):

ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.ReportServerUrl = 
        new Uri(ConfigurationManager.AppSettings["ReportServerURL"]);
ReportViewer1.ServerReport.ReportPath = 
        ConfigurationManager.AppSettings["ReportFolder"].ToString() + "MySSRSReport";

ReportParameter[] repParameters = new ReportParameter[1];
repParameters[0] = new ReportParameter();
repParameters[0].Name = "Par1";
repParameters[0].Values.Add("Value1");

ReportViewer1.ServerReport.SetParameters(repParameters);
ReportViewer1.ServerReport.Refresh();

However, developers could run into situations where once the reports are already created in .rdl format, we get to know that SSRS report server is not available for whatever reasons. In my current project, we found that SSRS report server is behind firewall and server engineers need a month (yup, that's bureaucracy for you!) to open it up and we urgently need to demonstrate the reports to the business team. Other scenarios could be that production SSRS server is not available due to budgetary constraints. In such rare (I wish) situations, we can move those .rdl reports to the web server without having to port them into .rdlc format or rewrite the reports in .rdlc format.

Step 1: Within the web layer of the Visual Studio solution, create a folder. In this example, I have created a root level folder called ‘Reports’ where I will copy all of the .rdl files.

Step 2: Get the data from database and populate it into a DataTable. In this example, I am invoking a Stored Procedure called “MyStoredProc”.

Step 3: Create a ‘ReportDataSource’ and get the data from this newly created Data Table. Add this DataSource to the Report.

In the below line, "DataSet_MyStoredProc" is the dataset in my report. This statement would populate the "DataSet_MyStoredProc" dataset with the data from DataTable dt.

ReportDataSource rds = new ReportDataSource("DataSet_MyStoredProc", dt);

The Report’s processing mode will be set as “Local” instead of “Remote”. The below code snippet shows this new setup.

private void GenerateLocalReport()
{
  ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
  ReportViewer1.LocalReport.ReportPath = "Reports/AdHocExistingRegReport.rdl";
  ReportViewer1.LocalReport.DataSources.Clear();

  ReportParameter[] repParameters = new ReportParameter[1];
  repParameters[0] = new ReportParameter();
  repParameters[0].Name = "Par1";
  repParameters[0].Values.Add("Value1");

  System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection
    (ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString);
  con.Open();
  System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("MyStoredProc",con);
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@Par1", "Value1");

  System.Data.DataTable dt = new System.Data.DataTable();
  dt.Load(cmd.ExecuteReader());

  ReportDataSource rds = new ReportDataSource("DataSet_MyStoredProc", dt);

  ReportViewer1.LocalReport.DataSources.Add(rds);

  ReportViewer1.LocalReport.SetParameters(repParameters);
  ReportViewer1.LocalReport.Refresh();

  con.Close();
}

History

• 9/22/2016: Initial version
• 9/23/2016: Format changes - Improved code indentation and reduced gap between headers and paragraphs