Tuesday, 12 April 2011

Generating PDF or Excel or Image reports programmatically(C#, VB, .Net Same code can be Changed) using SQL Server Reporting Services in C#

Introduction

SQL Server Reporting Services (SSRS) 2005 is the latest version of the reporting technology from Microsoft. This article explains a way to create PDF reports programmatically using web services exposed by SQL Server Reporting Services 2005 in C#.

Using the code

First create a report which accepts boolean as its input parameter, and deploy that to the reporting server. The code below explains how to render the report in PDF format programmatically, using C#.
  • Step 1: Create and deploy the report.
  • Step 2: Add a web reference to the web services exposed by Reporting Services 2005, i.e., ReportExecution2005 and ReportService2005.
  • Step 3: Declare the following variables:
    private rs2005.ReportingService2005 rs;
    private rsExecService.ReportExecutionService rsExec;
  • Step 4: Initialize the web services and set the authentication for the web services.
    // Create a new proxy to the web service
    
    rs = new rs2005.ReportingService2005();
    rsExec = new rsExecService.ReportExecutionService();
    
    // Authenticate to the Web service using Windows credentials
    
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
    rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
    
    // Assign the URL of the Web service
    
    rs.Url = http://yourSSRSserver/ReportServer + 
             "$REPORTSERVER2005/ReportService2005.asmx";
    rsExec.Url = http://yourSSRSserver/ReportServer + 
                 "$REPORTSERVER2005/ReportExecution2005.asmx";
  • Step 5: Write code to render the report in PDF format.
    // Prepare Render arguments
    
    string historyID = null;
    string deviceInfo = null;
    string format = "PDF";
    Byte[] results;
    string encoding = String.Empty;
    string mimeType = String.Empty;
    string extension = String.Empty;
    rsExecService.Warning[] warnings = null;
    string[] streamIDs = null;
    
    // Default Path;
    
    string fileName = @"c:\samplereport.pdf";
    
    // Define variables needed for GetParameters() method
    
    // Get the report name
    
    string _reportName = @"/MyReports/Report";
    string _historyID = null;
    bool _forRendering = false;
    ParameterValue[] _values = null;
    DataSourceCredentials[] _credentials = null;
    ReportParameter[] _parameters = null;
    
    try
    {
      // Get if any parameters needed.
    
      _parameters = rs.GetReportParameters(_report, _historyID, 
                    _forRendering, _values, _credentials);
    
      // Load the selected report.
    
      rsExecService.ExecutionInfo ei = 
            rsExec.LoadReport(_reportName, historyID);
    
      // Prepare report parameter.
    
      // Set the parameters for the report needed.
    
      rsExecService.ParameterValue[] parameters = 
             new rsExecService.ParameterValue[1];
    
      // Place to include the parameter.
    
      if (_parameters.Length > 0 )
      {
        parameters[0] = new rsExecService.ParameterValue();
        parameters[0].Label = "verzamelgroepAP";
        parameters[0].Name = "verzamelgroepAP";
        parameters[0].Value = "true";
      }
      rsExec.SetExecutionParameters(parameters, "en-us");
      results = rsExec.Render(format, deviceInfo, 
                out extension, out encoding,
                out mimeType, out warnings, out streamIDs);
    
      // Create a file stream and write the report to it
    
      using (FileStream stream = File.OpenWrite(fileName))
      {
        stream.Write(results, 0, results.Length);
      }
    }
    catch (Exception ex)
    {
      MessageBox.Show( ex.Message);
    }

For generating Other Report Formats(Eg: Excel, Images etc) one can simply change the beow code
string format = "PDF";

string fileName = @"c:\samplereport.pdf";

1 comments:

Anonymous said...

Hello there! This post could not be written much better! Going through this article reminds
me of my previous roommate! He constantly kept talking
about this. I'll send this post to him. Fairly certain he's going to have a very good read.
I appreciate you for sharing!

My homepage airplane game

Post a Comment