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.

Using .net dll into COM

Some time back I had a situation where I had to use a .Net dll into an classic ASP application. The questin was how the .net dll will be identified by asp application.

Here are the steps to follow to make a dll work in ASP application:
1. Add [ComVisible(true)] attribute to the class, method or at assembly level to make that code COM interop enabled. Or simply go to the project properties tab-> Application  -> Assembly Information and check the ComVisible checkbox.

2. Sign the assembly with strong name key. If you are very new to the .net world and have no idea how to sign an assembly with strong name key, please read my blog Signing an assembly with strong name key.

3. Move the assembly to the deployment machine and regiter into GAC with command gacutil -i [assembly path] on visual studio command prompt.

 2. Run the command regasm [assembly path]" on deployment machine on visual studio command prompt.

Yes, your dll ir ready for COM interop and to be used with ASP application.  

Moreover you can write these commands into a batch file and execute with a single click and keep for future use as well.

The Intricacies of WPF resolution independence

WPF boasts of one of its substantial feature called resolution independence. But it is really subtle to understand it in the way it is provided by WPF.
Does WPF really provide resolution independence? or Microsoft lies about it? To know it exactly you need to go deeper into it. For instance if you create a wpf application and run it and then change the resolution of your machine. What would you expect?  Your wpf application window should remain of the same size, isn't it? But that does not happen actually. Your window size will change as you change the resolution. OK let's try something else. You change the dpi settings of your screen and observe the window size. Again you will see the difference. So what is resolution independence in wpf then?
I will not keep puzzling you more. I explained these scenario so that you have an initial understanding of it. The wpf resolution independence feature says that the dimensions are measured in inches in wpf not in pixels and the logical unit in wpf is 1/96 of an inch. This scale may be chosen because most of the desktops usase resolution of 96 dpi, where dpi is Dots Per Inch.
So when you change the resolution or dpi setting of your screen, say for example, your device display is based on 96 dpi and you change it to 100 dpi, wpf will now calculate the dimensions of its UI elements based on 100 dpi but the device display supports only 96 dpi and so the window size of your wpf application will decrease. If you decrease the resolution of the screen to less than 96 dpi, your window size will increase.

Now, take a device that has a base resolution of 112 dpi and another device that has a base resolution of 72 dpi and run the wpf application on both the devices without changing the resolution. You will see that the window size is same on both the screens.
These all three instances explains that wpf does not go for the physical dpi setting of the device but sees only what is set for the operating system and according calculates the ratio of its measurement scale and the device dpi setting to show the exact dimensions of its UI elements in inches.

Concludingly, we can say that wpf supports resolution independence such that it will show the same dimensions of its UI elements for different devices that have different resolutions provided that the resolution setting are the same as the device's base resolution.

If you want to read this concept in more detail, you can read the detailed article
Is WPF Really Resolution Independent? by Cal. 

.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.