Bulk generating SSRS reports by C# application

We send a specific report to our customers every month. This report is generated by SQL Server Reporting Services on SQL Server 2014. The report has a parameter called Category which determines a project for which is the report generated. This parameter is dynamically generated because we send reports only for project with some activity in last month. That’s why we cannot use SSRS standard subscription function. The only standard way how to generate the report for all projects is clicking it by hand. This is time-consuming because there are usually more than one hundred projects to report. The other problem is that user could make an error and skip some project. This error would be hard to track in further process.

I decided to solve this by C# command line application. This application loads all active projects in last month. Then it generates the report in XLS format for each project. The generated files are stored in a program’s directory. The program is quite simple as you can see from the following source code.

I setup server name into lines 15 and 42. At the line 42 I also set a database which contains values for the Category parameter.  I get values for the Category parameter by running usp_SELECT_ParamValues procedure. Results are in a Case column. Of course you can also use view or table. I run the procedure with two parameters – Year and Month. The reports are always generated for the previous month. Then I use while loop to go through all lines of the procedure’s output. Inside the loop I create parameters array and I put an instance of ParameterValue on 0 position. If you need to add more parameters just add more values to parameter array. Finally I specify the output file.

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using GenerateTimesheetsForCustomers.zeus;

namespace GenerateTimesheetsForCustomers
{
  class Program
  {
    static void Main(string[] args)
    {
      ReportExecutionService rs = new ReportExecutionService();
      rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
      rs.Url = "http://SERVERNAME/reportserver/ReportExecution2005.asmx";

      // Render arguments
      byte[] result = null;
      string reportPath = "/PATH/TO/REPORT";
      string format = "EXCEL";
      string historyID = null;
      string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

      DataSourceCredentials[] credentials = null;
      string showHideToggle = null;
      string encoding;
      string mimeType;
      string extension;
      Warning[] warnings = null;
      ParameterValue[] reportHistoryParameters = null;
      string[] streamIDs = null;

      string category = String.Empty;

      ExecutionInfo execInfo = new ExecutionInfo();
      ExecutionHeader execHeader = new ExecutionHeader();

      rs.ExecutionHeaderValue = execHeader;

      execInfo = rs.LoadReport(reportPath, historyID);

      string connectionString = "Server=SERVERNAME;Database=DATABASENAME;Integrated Security=SSPI;";
      SqlConnection connection = new SqlConnection(connectionString);
      connection.Open();

      SqlCommand cmd = new SqlCommand("usp_SELECT_ParamValues", connection);
      cmd.CommandType = CommandType.StoredProcedure;

      int month = Int32.MaxValue;
      int year = Int32.MaxValue;

      if (DateTime.Now.Month == 1)
      {
        month = 12;
        year = DateTime.Now.Year - 1;
      }
      else
      {
        month = DateTime.Now.Month - 1;
        year = DateTime.Now.Year;
      }

      cmd.Parameters.Add(new SqlParameter("@Year", year));
      cmd.Parameters.Add(new SqlParameter("@Month", month));

      try
      {
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
          // iterate through results, printing each to console
          while (rdr.Read())
          {
            category = rdr["Case"].ToString();
            ParameterValue[] parameters = new ParameterValue[1];
            parameters[0] = new ParameterValue();
            parameters[0].Name = "Category";
            parameters[0].Value = category;
            rs.SetExecutionParameters(parameters, "en-us");

            result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
            execInfo = rs.GetExecutionInfo();

            string filename = category + ".xls";
            FileStream stream = File.Create(filename, result.Length);
            Console.WriteLine("Created file: " + filename);
            stream.Write(result, 0, result.Length);
            stream.Close();

            Console.WriteLine("Execution date and time: {0}", execInfo.ExecutionDateTime);
          }
        }
      }
      catch (Exception e)
      {
        Console.WriteLine(e.Message);
      }

      Console.WriteLine("Generating is finished!");
      Console.ReadKey();

    }
  }
}

Leave a Reply

Name and email address are required. Your email address will not be published.

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

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 )

Connecting to %s

You may use these HTML tags and attributes:

<a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <pre> <q cite=""> <s> <strike> <strong> 

%d bloggers like this: