Mail-merge alternative: Reports exporting to Word files

CRM 4.0 does offer a lot of new features regarding mail merge, but not all situations are supported by the new features. You can look into 3rd party tools like C360 / MSCRM-Addons or Temptus Wordconnect, but a totally different approach is to use the Reporting Services. This post will dive into how to use Reporting Services to generate word documents.

You can create any report in CRM by using the Report Wizard. Imagine you want a specific invoice which contains a list of the products that are included in the invoice. This report is easily created in the Report Wizard. You can also run the report from CRM, but when you look at the export button, you will see that word is no option. Now you can do two things:
1) Go to Aspose and buy their product. They will allow you to export a report to many text formats including .doc, .docx, .txt etc.
2) Create a piece of code which transforms your report to a .doc yourself. Of course we'll dive deeper on this approach.

You can create an extension to Reporting Services which does the real export to a word document, but this is relatively hard. A way easier approach is to access the ReportinService webservice and get the report in a byte array and send this byte array to the user with a content type set to "application/vnd.ms-word ".

How to do this, is to create a webform which does have nothing in the aspx itself. In the code behind, send instructions to the user that a word file will is approaching.


Response.ContentType = "application/vnd.ms-word ";
Response.AddHeader("content-disposition", "attachment; filename=YourFileName.doc");
Response.BufferOutput = true;


Then initialize the webservice and set the correct values

//Define report service
ReportingService rs = new ReportingService();

//Set Credentials
rs.Credentials = GetCredentials();

//Set URL
string reportServerPath = "http://SRSServer/ReportServer";
rs.Url = reportServerPath + "/ReportService.asmx";

//Define Reporting Services Variables
byte[] reportData;
string[] streamIDs;
string optionalString = null;
string rptNameFullPath = "/ORGNAME_MSCRM/4.0/" + "{02708d0c-28c5-dd11-9398-00155d511c04}"; // the guid is the name of the report in CRM.
ParameterValue[] optionalParams = null;
Warning[] w = null;

//If neccesary define datasource credentials. See note below for more info.
DataSourceCredentials dsc = new DataSourceCredentials();
dsc.DataSourceName = "CRM";
dsc.Password = "{E0E04CEF-04DB-DD11-9418-00155D511C04}";
dsc.UserName = "{DE9347FD-BC01-FD55-5218-045655D51C04}";

// Download the report from the webservice in HTML4.0 format
reportData = rs.Render(rptNameFullPath, "HTML4.0", null,
"/WebApplication1/",
null, new DataSourceCredentials[] { dsc }, null, out optionalString, out optionalString, out optionalParams, out w, out streamIDs);

// Offer download to user
Response.BinaryWrite(reportData);

When you do request this page in IE, then you'll get the report offered to you in a doc format because of the content type settings. Word does understand HTML and will just open the report.

Note: Make sure to check my other post around Reporting Services which might help you: Log In Name and Password required by Report Server

6 comments:

Craig S said...

Thank you for this post, it is very interesting. I took your concept and went a little further.

First, reference this code for creating a report rendering extension:

http://msdn.microsoft.com/en-us/magazine/cc163840.aspx

Now, replace what their render method with this:


public bool Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
{
string strUri = string.Empty;
strUri += "http://localhost/Reports/Reserved.ReportViewerWebControl.axd";
strUri += "?ReportSession=" + reportServerParameters["SessionID"];
strUri += "&FileName=" + report.Name;
strUri += "&ControlId=" + Guid.Empty;
strUri += "&Culture=" + CultureInfo.CurrentCulture.LCID.ToString(CultureInfo.InvariantCulture);
strUri += "&UICulture=" + CultureInfo.CurrentUICulture.LCID.ToString(CultureInfo.InvariantCulture);
strUri += "&ReportStack=1";
strUri += "&OpType=Export";
strUri += "&ContentDisposition=OnlyHtmlInline";
strUri += "&Format=MHTML";

Stream outputStream = null;
StreamWriter streamWriter = null;

try
{
//Output
outputStream = createAndRegisterStream(report.Name, "doc", System.Text.Encoding.UTF8, "application/vnd.ms-word", true, StreamOper.CreateAndRegister);
streamWriter = new StreamWriter(outputStream);

//Input
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(strUri);

//Credentials
request.Credentials = System.Net.CredentialCache.DefaultCredentials;

//Output
HttpWebResponse response = (HttpWebResponse)request.GetResponse();

//Input
Stream inputStream = response.GetResponseStream();
StreamReader streamReader = new StreamReader(response.GetResponseStream());

//Read/Write
streamWriter.Write(streamReader.ReadToEnd());
}
finally
{
if (streamWriter != null)
{
streamWriter.Flush();
}
}

return false;
}


Now you can easily export to word for free. It does a fairly good job, sometimes better than the Aspose converters with regards to positioning, etc.

Andy said...

Thanks Ronald and Craig for the useful posts.

I am using Craig's Code to build a custom renderer. I was able to add the custom renderer to the SSRS also. But when i'm trying to export using the renderer, it is giving me a HTTP 500 error(Internal error @ GetResponse line). I've still not been able to fix it.

Can you please help me in identifying what is going wrong?

Anonymous said...

this line is broken:

strUri += "?ReportSession=" + reportServerParameters["SessionID"];

should be this instead:

strUri += "?ExecutionID=" + reportServerParameters["SessionID"];

Elisabeth said...

Hello,
Thanks for the informative post! I'm trying to write this in VS 2008, however for some reason when I run this command line:

Response.ContentType = "application/vnd.ms-word ";
Response.AddHeader("content-disposition", "attachment; filename=YourFileName.doc");
Response.BufferOutput = true;

I got an error saying that the name 'Response' does not exist in current context.. do I need to put another reference to my application? I have added a web reference of Reporting Service:
using Microsoft.ReportingServices.Interfaces;
using Microsoft.ReportingServices.ReportRendering;
using WebService1.RSWebReference;

Can anyone please help me on this one? Greatly appreciate it!

-Elizabeth

Ronald Lemmen said...

The response is part of the Page object. This code assumes you're using it in the code behind of the aspx page. If you are in a business layer, then you'd need to pass on the Page Context.

Ronald

jan said...

When I try the code exactly as above I get the following security error “No Microsoft Dynamics CRM user exists with the specified domain name and user ID”

This is on a 2008 server and SQL server when I try to get the http response.

Is there a way to impersonate the http request in the context of a rendering extension?

Thanks,
Jan