Reading and opening SSRS Report through c#.Net

Hi Friends, Today I am writing one more technical encounter I had few days back. I had a requirement such that I had to call a SSRS service through classic ASP application. Now, the simple way was to just hit SSRS report url through javascript. But the problem was, the report also needed authentication and the credentials should not be exposed in the client side script. I searched through internet but it did not seem possible in server side scripting in ASP.
So, here is the solution I adopted:
I coded the solution in c#.net and then made that dll COM visible and refrenced in ASP application.

C#.Net code 

The first step is to add following web service references to your project:

http://[Report Server Name or IP]/ReportServer/ReportService2005.asmx

http://[Report Server Name or IP]/ReportServer/ReportExecution2005.asmx

[ComVisible(true)]
public class SSRSAgent
{
private rs2005.ReportingService2005 rs;
private rsExecService.ReportExecutionService rsExec;

/// <summary>
/// Reads a report from the server for given parameters and returns a byte array.
/// </summary>
/// <param name="paramList">Report parameters as name value paires delimitted by & and = like in query string.</param>
/// <param name="serverName">Name of the reporting server</param>
/// <param name="reportPath">Path of the report (The url part without server name)</param>
/// <param name="format">Report display format PDF or Excel</param>
/// <param name="uid">User ID used to authenticate on Reporting server</param>
/// <param name="pwd">Password used to authenticate on Reporting server</param>
/// <param name="pwd">Domain name used to authenticate on Reporting server</param>
/// <returns></returns>
[ComVisible(true)]
public byte[] OpenReport(string reportParams, string serverName, string reportPath, string format, string uid, string pwd, string domain)
{
string[] nameValueArray = null;
if (!string.IsNullOrEmpty(reportParams))
{
    nameValueArray = reportParams.Split(new char[] { '&' }, StringSplitOptions.RemoveEmptyEntries);
}
// 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 = new NetworkCredential(uid, pwd, domain);//System.Net.CredentialCache.DefaultCredentials;
rsExec.Credentials = new NetworkCredential(uid, pwd, domain);// System.Net.CredentialCache.DefaultCredentials;

// Assign the URL of the Web service
rs.Url = "http://"+ serverName +"/ReportServer/ReportService2005.asmx";
rsExec.Url = "http://" + serverName + "/ReportServer/ReportExecution2005.asmx";

// Prepare Render arguments
Byte[] results;

string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
rsExecService. Warning[] warnings = null;
string[] streamIDs = null;

// Get the report name
string _historyID = null;
bool _forRendering = false;
ParameterValue[] _values = null;
SSRSReportReader.rs2005.DataSourceCredentials[] _credentials = null;
SSRSReportReader.rs2005.ReportParameter[] _parameters = null;

try
{// Get if any parameters needed.
_parameters = rs.GetReportParameters(reportPath, _historyID, _forRendering, _values, _credentials);
// Load the selected report.
rsExecService.ExecutionInfo ei = rsExec.LoadReport(reportPath, historyID);// Prepare report parameter.
// Set the parameters for the report needed.
rsExecService.ParameterValue[] parameters = null;
// Place to include the parameter.
if (_parameters.Length > 0)
{
parameters = new rsExecService.ParameterValue[_parameters.Length];
if (nameValueArray == null || _parameters.Length > nameValueArray.Length)
{    throw new Exception("Insufficient parameters.");
}for (int i = 0; i < _parameters.Length; i++)
{    string[] paramNameValue = nameValueArray[i].Split(new char[] { '=' });
    if (paramNameValue.Length > 1)
    {
        parameters[i] = new rsExecService.ParameterValue();
        parameters[i].Label = paramNameValue[0].Trim();
        parameters[i].Name = paramNameValue[0].Trim();
        parameters[i].Value = paramNameValue[1].Trim();
    }
}

}

rsExec.SetExecutionParameters(parameters, "en-us");
results = rsExec.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

return results;
}catch (Exception ex)
{

}return null;
}
}

Explaination

The method OpenReport() returns a byte array, you can write it on the disk as a pdf file or write it to the response in the asp page.

Here is a sample call to this method.

byte[] result = SSRSReportReader.Agent.OpenReport("AppID=2&Name=John&ID=36643", "69.20.33.30", "/NewReports/Questionnaire", "PDF", "", "", "");
File.WriteAllBytes("d:\\test_ssrs.pdf", result);

The first argument to this method is ReportParams this is a string that contains all the parameters, that are to be passed to the report, separated by '&' and '='.

The second argument is the report server IP or it can be the server name.

The third argument is the relative path of the report on server.

The fourth argument is the report rendering formate it can be 'PDF' or 'Excel'

Integration with ASP Application

If you have observed, I have added an attribute to the class as [ComVisible(true)]. If you compile this code with the class having this attribute, your class will be enabled for COM interop. This code will work for any .net managed application without even atteching this attribute but to make this dll work in ASP application this alone is not sufficient. To see in detail how to make it work for classic asp see my blog use .net dll with COM.

.NET Resource File to Excel File Converter

Introduction

This application exports data from a .NET resource file to an Excel file. The code uses a COM component named Microsoft Excel 11.0 object library that provides APIs to manipulate Excel applications. So before using this code, you need to add the reference to this DLL as shown in Figure 1 above. This article shows how you can manipulate Excel files as well as .NET resource files through programming.

Background

There is a functionality called Localization in .NET that most of the Web application developers in ASP.NET must be familiar with. I had to implement the same functionality in one of my projects and for that I needed to create a resource file for different languages. I made one resource file for the English language but I also needed the values in different languages for the same keys, so I had to give the key value pairs in an Excel file to a designated person who was going to write the corresponding values for other languages in the same Excel file. Now the task of manually exporting resource file data to Excel files seemed quite time consuming to me as there were a large number of resource files (more than 100). So I decided to develop a small tool first to do the task for me. Apart from getting my task done easily, I find it very interesting as I got a lot of learning out of it. After that, I also developed a tool for importing data back from an Excel file to a resource file. You can read it in my article, Excel to Resource file conversion.

Code

Add following reference to your project:

This is the Excel application initialization code you can put in page load of the form:

// reference to Microsoft Excel object
Excel.Application exlObj = null;

// initializing the Excel application
exlObj = new Excel.Application();

// Showing the error message if any problem occurs in starting the Excel application
if (exlObj == null)
{ MessageBox.Show("Problem in starting Excel.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); System.Windows.Forms.Application.Exit();
}

// you can keep the Excel application visible or invisible while writing to it
exlObj.Visible = false; 

How To Create an Excel Workbook and Worksheet to Work On? 

// creating Excel workbook
Excel.Workbooks workbooks = exlObj.Workbooks;

// "workbook.Add" method returns a workbook with specified template
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

// getting the collection of Excel sheets from workbook. 
// However right now there is only one worksheet in the workbook
Sheets sheets = workbook.Worksheets;

// getting the worksheet from Sheet's collection. 
// Keep in mind that in Excel, the index starts from 1
// and index 1 always gives the newly added worksheet
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1); 

Adding a New Worksheet to a Workbook  

// This method adds a new sheet to Sheet collection of workbook
// here Missing.Value signifies that these arguments are unknown
// we can also pass appropriate arguments if we wish
sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); 

Naming or Renaming the Worksheet

// naming the worksheet. The name of the worksheet should not exceed 31 characters
if (fileName.Length > 30)
{
worksheet.Name = fileName.Substring(0, 29);
}
else
{
worksheet.Name = fileName;
} 

Working with Data on Sheet and Formatting 

// get_range gives the object of a particular row from index1 to index2
Range range = worksheet.get_Range("A1", "B1");

// applying text format on a particular range. These can be applied on 
// the whole worksheet too
range.Font.Bold = true;

// writing text to a cell
range.set_Item(1, 1, "KEY");
range.set_Item(1, 2,"VALUE");

// applying format on worksheet
worksheet.Columns.ColumnWidth = 40;
worksheet.Rows.WrapText = true;
worksheet.SaveAs(txtTargetPath.Text + "\\"+fileName +".xls" 
    , 1, null, null, false, false, null, null, null, null);

Interesting Points

The basic ideas used in this code are manipulating an Excel file and a .NET resource file. The sample I have presented here fits my requirements and I implemented only those things which were needed out of my explorations. You can take this article as a starting point for further exploration in this subject as there are a lot of manipulations you can do with Excel files and even some with *.resx files.