Soe Tun

my attempt to solve common tech problems

  Home  |   Contact  |   Syndication    |   Login
  14 Posts | 0 Stories | 216 Comments | 0 Trackbacks

News

Twitter







Archives

Post Categories

Image Galleries

Last week, I needed to call the SQL Reporting Services Web Service to export reports as Excel, PDF, and Word formats.
I could have used the [Add Web Reference...] as the MSDN Documentation Accessing the Report Server Web Service Using Visual Basic or Visual C#, but I decided to use [Add Service Reference...].

If you are wondering what the difference is between Web Reference and Service Reference, please read this blog post by Andrew Tokeley.

Goal

Create a common library project which I could reuse in all of my other projects to call the Reporting Services Web Service API to export reports to various formats

Reason

I am using the Reporting Services Web Service because the Reporting Server is behind the firewall and not visible to the outside.
Only my ASP.NET MVC web application front-end is visible to the outside world.

 

Step 1: Create a Class Library Project with "Add Service Reference"

  1. Create a new Class Library project called ReportingServiceClientLib.
  2. Right-Mouse-Click on References and choose Add Service Reference.
    Add Service Reference

    Add Service Reference Dialog and Settings
     
  3. If you want the generated code to have asynchronous methods, you can do so by clicking on the Advanced button.
    Advanced Settings (e.g., Asynchronous Methods)
     
  4. Once you click OK, Visual Studio will generate ReportExecutionServiceSoapClient class and other necessary classes.
    Additionally, App.config file will also be generated for the WCF Configuration Settings.

 

Step 2: How to Use ReportExecutionServiceSoapClient Class

Okay, when we call the Web Service, we have to do the following tasks.

  1. Specify which Report we want to run.
  2. Pass in Domain Account credentials for the Web Service call since we are in the Active Directory Domain.

Use the class and the enum below as our report execution utility class which will be using the Visual Studio generated ReportExecutionServiceSoapClient class.
The comments I have in it should be more than enough explanation what it does and how the parameters are used.

using System;

using ReportingServiceClientLib.ReportingWebService;

namespace ReportingServiceClientLib
{
	#region enum ExportFormat
	/// <summary>
	/// Export Formats.
	/// </summary>
	public enum ExportFormat
	{
		/// <summary>XML</summary>
		XML,
		/// <summary>Comma Delimitted File
		CSV,
		/// <summary>TIFF image</summary>
		Image,
		/// <summary>PDF</summary>
		PDF,
		/// <summary>HTML (Web Archive)</summary>
		MHTML,
		/// <summary>HTML 4.0</summary>
		HTML4,
		/// <summary>HTML 3.2</summary>
		HTML32,
		/// <summary>Excel</summary>
		Excel,
		/// <summary>Word</summary>
		Word
	}
	#endregion

	/// <summary>
	/// Utility class that renders and exports a SQL Reporting Services report into the specified output format.
	/// </summary>
	public static class ReportExporter
	{
		#region Method: GetExportFormatString(ExportFormat f)
		/// <summary>
		/// Gets the string export format of the specified enum.
		/// </summary>
		/// <param name="f">export format enum</param>
		/// <returns>enum equivalent string export format</returns>
		private static string GetExportFormatString(ExportFormat f)
		{
			switch (f)
			{
				case ExportFormat.XML: return "XML";
				case ExportFormat.CSV: return "CSV";
				case ExportFormat.Image: return "IMAGE";
				case ExportFormat.PDF: return "PDF";
				case ExportFormat.MHTML: return "MHTML";
				case ExportFormat.HTML4: return "HTML4.0";
				case ExportFormat.HTML32: return "HTML3.2";
				case ExportFormat.Excel: return "EXCEL";
				case ExportFormat.Word: return "WORD";

				default:
					return "PDF";
			}
		}
		#endregion

		#region Method: Export( ... )
		/// <summary>
		/// Exports a Reporting Service Report to the specified format using Windows Communication Foundation (WCF) endpoint configuration specified.
		/// </summary>
		/// <param name="wcfEndpointConfigName">WCF Endpoint name</param>
		/// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
		/// <param name="report">Reporting Services Report to execute</param>
		/// <param name="parameters">report parameters</param>
		/// <param name="format">export output format (e.g., XML, CSV, IMAGE, PDF, HTML4.0, HTML3.2, MHTML, EXCEL, and HTMLOWC)</param>
		/// <param name="output">rendering output result in bytes</param>
		/// <param name="extension">output format file extension</param>
		/// <param name="mimeType">output MIME type</param>
		/// <param name="encoding">output encoding</param>
		/// <param name="warnings">warnings (if any)</param>
		/// <param name="streamIds">stream identifiers for external resources (images, etc) that are associated with a given report</param>
		public static void Export(
			string wcfEndpointConfigName, System.Net.NetworkCredential clientCredentials, string report, ParameterValue[] parameters,
			ExportFormat format, out byte[] output, out string extension, out string mimeType, out string encoding, out Warning[] warnings, out string[] streamIds)
		{
			using (var webServiceProxy = new ReportExecutionServiceSoapClient(wcfEndpointConfigName))
			{
				webServiceProxy.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
				webServiceProxy.ClientCredentials.Windows.ClientCredential = clientCredentials;

				// Init Report to execute
				ServerInfoHeader serverInfoHeader;
				ExecutionInfo executionInfo;
				ExecutionHeader executionHeader = webServiceProxy.LoadReport(null, report, null, out serverInfoHeader, out executionInfo);

				// Attach Report Parameters
				webServiceProxy.SetExecutionParameters(executionHeader, null, parameters, null, out executionInfo);

				// Render
				webServiceProxy.Render(executionHeader, null, GetExportFormatString(format), null, out output, out extension, out mimeType, out encoding, out warnings, out streamIds);
			}
		}
		#endregion
	}
}

 

Step 3: Configuring the WCF Settings

So far, we still haven't defined the WCF settings. We are going to use the WCF Service Configuration Editor from the Visual Studio's Tool menu. Lets use it to edit the generated App.config.

Open WCF Service Configuration Editor

Okay, I am going to be lazy and just copy and paste my WCF config section.
Just replace your App.config file section with the following one.
You can play with the WCF Configuration Editor yourself to understand what it does.

Please note that my config file limits the Message Size to be 5MB or less.

<system.serviceModel>
	<services />
	<bindings>
		<basicHttpBinding>
			<!-- maxReceivedMessageSize ===> 5242880 bytes ===> 5MB (Megabytes)
			<binding name="basicHttpBindingConfig" allowCookies="true" maxReceivedMessageSize="5242880">
				<security mode="TransportCredentialOnly">
					<transport clientCredentialType="Ntlm" proxyCredentialType="None" realm="" />
					<message clientCredentialType="UserName" algorithmSuite="Default" />
				</security>
			</binding>
		</basicHttpBinding>
	</bindings>

	<client>
		<endpoint address="http://localhost/ReportServer/ReportExecution2005.asmx?wsdl"
			binding="basicHttpBinding" bindingConfiguration="basicHttpBindingConfig"
			contract="ReportingWebService.ReportExecutionServiceSoap" name="basicHttpEndpoint" /> <!-- the WCF endpoint name in RED is important -->
	</client>
</system.serviceModel>

The above configuration section will be going into the Web.config file of my ASP.NET MVC Web Application.

 

Step 4: Putting It All Together in an ASP.NET MVC File Download Action Method

Here is my MVC Action Method and how I pass in Report Parameters.

using System.Linq;
using ReportingServiceClientLib;
using ReportingServiceClientLib.ReportingWebService;

[HttpPost]
public FileContentResult ExportExcel(string lastName)
{
	IList<ParameterValue> parameters = new List<ParameterValue>();
	parameters.Add(new ParameterValue { Name = "lastName", Value = lastName });

	byte[] output;
	string extension, mimeType, encoding;
	string reportName = "/ReportFolderName/MyReportName";

	ReportExporter.Export(
		"basicHttpEndpoint" /* name of the WCF endpoint from Web.config */,
		new NetworkCredential("userName", "secretPassword", "activeDirectoryDomainName"),
		reportName,
		parameters.ToArray(),
		ExportFormat.Excel,
		out output,
		out extension,
		out mimeType,
		out encoding
	);

	//-------------------------------------------------------------
	// Set HTTP Response Header to show download dialog popup
	//-------------------------------------------------------------
	Response.AddHeader("content-disposition", string.Format("attachment;filename=GeneratedExcelFile{0:yyyyMMdd}.{1}", DateTime.Today, extension));
	return new FileContentResult(output, mimeType);
}

There you have it. That is how you call the Reporting Services Web Service using WCF from ASP.NET MVC Web Application.

Cheers,
Soe

posted on Friday, February 26, 2010 4:20 AM

Feedback

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 2/26/2010 5:09 AM Mihai
You are not storing report images !!!!
HTML rendering won't work

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 2/26/2010 11:55 AM Soe
Hi Mihai,

yes I know it is not a complete solution.
I still need to fine-tune this code because this is a work-in-progress code for my project.

My Reporting Services Reports don't have images yet.
It is just dumping out pure datatables based on filters.


The reason why I blogged about this is because
(1) I couldn't find any resources about "Add Service Reference" and using the Reporting Service Web Service with it.
(2) I was struggling with getting the correct Web.config settings for the WCF.


I will post more about it when I figure out how to include images and other resources in the rendering.

Soe

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 12/7/2010 10:53 PM Ciprian
Thanks very much for sharing this!

I find it hard to get examples on this topic - and your sample is the best I found.

Do you have any updates which would deal with images also?

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 12/8/2010 2:35 PM Soe Tun
Hi Ciprian,

I haven't gotten to fixing it to work for the images.
However, I have researched about and have some clues that may help.


The [ExecutionInfo] class **might** probably returns some kind of "streamID"s for the images in the Report after calling the web service.


So I think we might be able to use the methods.
That's all the info I have for now.

http://msdn.microsoft.com/en-us/library/aa225915(SQL.80).aspx

http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.renderstream.aspx

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 2/23/2011 12:31 PM Daniel Kemper
Excellent article. Thank you for sharing!

Dan

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 5/12/2011 5:03 PM Ken
Is there a way to show the ReportViewer?

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 5/12/2011 10:35 PM Soe Tun
@Ken

I *might* be wrong about this, but I *vaguely* remember someone drag-and-dropped a Report Viewer web control and use it in MVC View.


Or another tedious way would be to try creating an empty Web Forms with a Report Viewer control.
And see what kind of JavaScript and HTML markup get generated and replicate it in your View using an HTML Helper methods.

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 6/28/2011 8:21 PM Dave S
Thanks, this is helpful!

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 11/21/2011 7:40 PM Tom
Thanks for the information. Do you know of some similar methods in the 2010 API (SQL 2008 R2 SSRS API) to export like this, without using ReportViewer control?

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 11/21/2011 8:03 PM Soe Tun
Hi Tom, sorry I don't know any new API yet.
I plan to check if it exists and will make a blog post on it if it is any better than this setup.

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 11/22/2011 12:16 PM Tom
Thank you - the new 2010 API exists for use with SQL Server 2008 "R2" SSRS Report Server. I use it to launch reports in the ReportViewer control in my web app from this version of SSRS. T

he API has changed quite a bit since the previous.

I really like your project. The problem is the new API methods have changed a lot. So for example I need to write different code inside the Export method.

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 11/22/2011 12:59 PM Tom
Looks like the execution endpoint did not change:

Take a look at http://msdn.microsoft.com/en-us/library/ms155398.aspx. Once you make a new proxy to ReportService2010.asmx, you just need to instantiate the ReportingService2010 class and go from there.

ReportExecution2005.asmx is still the execution endpoint (no ReportExecution2010). You make a proxy to the asmx and instantiate a ReportExecutionService to render reports.


# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 12/12/2011 10:00 AM Jose Luis
Excellent post! Thanks for sharing these!
But I'm still having troubles with the report connection to the DataBase. It seems that passed the network credentials I use but when it is going to execute de call to the Dataset for setparameters Methods, throws an sqlSever Exception that shows that: "NT Authority\Anonymous cannot connect to the database".
Changing the DataSource`s connection string and enabling the execution account doesnt seems to work. Can anyone helps me?

Thanks

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 6/28/2012 6:18 PM Sandeep
Very good article matches exactly to my requirement .When I am trying to go with same approach I am getting errors on these

ExecutionInfo ,ExecutionHeader and ReportExecutionServiceSoapClient saying that could not find assembly reference.I think i need create proxy service client for this I am not sure?So any one can help me with this?

Thanks

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 7/15/2013 7:11 AM MariRaja
Thanks for this nice article. I am getting the following error while trying to implement your code,

Write up generation failed.Write up generation failed.The HTTP request is unauthorized with client authentication scheme 'Ntlm'. The authentication header received from the server was 'NTLM'.

Can anyone helps me?


# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 8/21/2013 9:42 AM Daniel Bengtsson
Better late than never:

If anyone else is getting the same issue with ExecutionInfo, ExecutionHeader etc. that Sandeep (and I) had: Check the URL to your service. It's supposed to be the ExecutionService, not ReportingService.

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 10/22/2013 4:04 PM rex campbell
Question, is there a way to pass a specific user name, domain, and password for authentication. This way I don't have to customize SSRS to work with the local user account methods in MVC (since Windows Authentication, NTLM is not really used?)

# Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 11/15/2013 6:37 AM Dinesh
i got the error, Could not find endpoint element with name 'basicHttpEndpoint' and contract 'ReportingWebService.ReportExecutionServiceSoap' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this name could be found in the client element.

# re: Executing Reporting Services Web Service from ASP.NET MVC Using WCF "Add Service Reference" 9/3/2014 10:20 AM Anon
Helped me a lot, thanks !
The default configuration when importing a SSRS webservice isn't working at all, what a mess to debug...

Post A Comment
Title:
Name:
Email:
Comment:
Verification: