2006/07/21

Reporting Services & MS Word format (.doc) rendering.

Problem description: Feature request One of the most frequently asked questions in Reporting Services (RS) forums and newsgroups is: Why did not RS include the option to render documents into MS Word (.doc) format? According to BOL, Reporting Services 2005 has the ability to render/output documents into:

  • CSV
  • Acrobat (PDF) file
  • Excel
  • Web archive
  • TIFF File
  • XML

Why did I marked XML? Because RS is not only able to output the results to raw XML format but also can make transformations based on XSLT stylesheets! That means that you can render into ANY format that you want, provided you feed Reporting Services with the proper transformation file. This document is based (and extends) the idea from CarlosHM and his blog document: WordML in Reporting Services.

The solution In order to illustrate the process, we will render a report into MS Word format.

1. Create your template using Microsoft Word 2003. You can make it as complex as you need/want. When you reach the point where data fields are expected to be filled, just write the name of the fields surrounded by special characters (to be able to find them afterwards). For instance, if there will be a field called company that should be on bold, arial 16, left justified, etc... just write «Company» and format that piece of text as needed. Add every fixed text as usual (I suppose you know how to work with Word). If you want a new page for every record returned by Reporting Services, remember to add a hard break (CTRL+Enter) at the end of the format.

Screen capture of the template inside MS Word 2003

2. Save the document When you have your layout ready, Save document as... XML document. and name it TemplateDocument.xml. Just for your information, you can see that, despite the fact that its extension is .xml, its icon is not the standard for an XML file and it is still linked to Word. That is because in the inside of it there is a line:

<?mso-application progid="Word.Document"?>

3. Reformat the generated .xml file. MS Word generates XML files without indenting (smaller file size), thus making them hardly human readable. Since we need to manually modify this file, we will reformat it and indent it to avoid making mistakes. For this you might need tidy, a little command line program to reformat html/xml files or use your favourite xml editor.

tidy -utf8 -xml -i -wrap 999 -output TidyOutput1.xml TemplateDocument.xml

Note that if your input document has embedded images, using -i option will break them. There is no problem with this since they can be repaired later, calling tidy again without passing -i parameter.

4. Modify the formatted .xml in order to achieve .xsl Now we have a human readable .xml file. Let's use it in order to create the .xsl file we need to upload to RS. First, make a copy of it, renamed it to .xsl and open it with Notepad:

copy TidyOutput1.xml TidyOutput1.xsl
notepad TidyOutput1.xsl

We will need to do some search and replaces of some blocks of text. Search for the line that starts with <o:DocumentProperties> and remove everything from the top of the file up to that particular line (not included). In place of the removed text, insert this one:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:w10="urn:schemas-microsoft-com:office:word"
xmlns:sl="http://schemas.microsoft.com/schemaLibrary/2003/core"
xmlns:aml="http://schemas.microsoft.com/aml/2001/core"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:wsp="http://schemas.microsoft.com/office/word/2003/wordml/sp2"
xmlns:st1="urn:schemas-microsoft-com:office:smarttags">
  <xsl:output method="xml" media-type="text/xml" version="1.0"
  encoding="UTF-8" standalone="yes" indent="yes"
  omit-xml-declaration="no" />
  <xsl:template match="/">
    <xsl:processing-instruction name="mso-application">
    progid="Word.Document"</xsl:processing-instruction>
    <w:wordDocument xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:w10="urn:schemas-microsoft-com:office:word"
xmlns:sl="http://schemas.microsoft.com/schemaLibrary/2003/core"
xmlns:aml="http://schemas.microsoft.com/aml/2001/core"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:wsp="http://schemas.microsoft.com/office/word/2003/wordml/sp2"
xmlns:st1="urn:schemas-microsoft-com:office:smarttags"
w:macrosPresent="no" w:embeddedObjPresent="no" w:ocxPresent="no"
xml:space="preserve">
<xsl:apply-templates />
</w:wordDocument>
  </xsl:template>
  <xsl:template match="Report">
    <o:SmartTagType o:namespaceuri="urn:schemas-microsoft-com:office:smarttags"
    o:name="PersonName" />

Now go to the end of the file. You will find that the last tag is:

</w:wordDocument>

Delete it and insert these two lines:

  </xsl:template>
</xsl:stylesheet>

Now save your document. Let's check if it is well-formed yet:

tidy -utf8 -xml -i -wrap 999 -output TidyOutput2.xsl TidyOutput1.xsl

If you see No warnings or errors were found in the output, you can continue (using TidyOutput2.xsl). If any error appears, revise the previous steps to see where the error was. You need to have a valid (well-formed) XSL file in order to continue.

5. Modify .xsl file to include the database fields. In step 1, when we created the template using MS Word, we named the fields as «FieldName». Now we will use that names to search and replace every field that you entered with the correct xsl command to replace it with the value of the Reporting Services returned xml file. Open TidyOutput2.xsl with Notepad and do a search and replace:

Search: «FieldName»
Replace: <xsl:value-of select="@FieldName" />

Repeat the search & replace for every field. When you think you had finished, search for « to check that you did not leave any field behind. Save as TidyOutput3.xsl. As I told you before, if your original template had embedded images, reformatting using tidy with -i option broke them. Now we will run:

tidy -utf8 -xml -output TidyOutput4.xsl TidyOutput3.xsl

Without the -i option everything will be reformatted to start at column 0 and the images ( <w:binData> tags) will be repaired.

6. Upload .xsl file to RS. In order to do our test we will create a new simple report, using MS Visual Studio, with the underlying query:

SELECT 'Company 1' As Company, 'Address 1' As Address
UNION
SELECT 'Company 2' As Company, 'Address 2' As Address

This will return us 2 records, without involving any database. Of course, you will need to create your own report with your own underlying query so that you can retrieve all the information you need. Then design the report as simple as you can, since it will only be used to retrieve the data. The formatting will be applied using TidyOutput4.xsl we had prepared before. Both the generated report (Report1.rdl) and TidyOutput4.xsl must be deployed to Reporting Services.

Screen capture of the html generated report inside MS Explorer

7. Test it If you have reached this point, now it's time to check if RS can send us a Word document. Type this on your explorer navigation bar:

http://reportserver.yourdomain.com/ReportServer?/directory/Report1
 &rs:Format=XML
 &rs:Command=Render
 &rc:XSLT=TidyOutput4.xsl
 &rc:FileExtension=xml

If everything has gone nicely, you should be asked for opening a file called Report1.xml. Click open and see the results. MS Word should open and you should see something like your template, but without the data. The reason for this is that we forgot to add...

8. <xsl:for-each> Most of the reports have a master/details structure. You can think about an invoice, with master part with customer, invoice number, address etc, and a details part with a list of items. In our Report1.rdl we used a table (named table1) and we need to use <xsl:for-each> in order to iterate for every record in it. Open TidyOutput3.xsl (indented) and save it as TidyOutput5.xsl. Now search for the occurrence of your first field (@Company). Now move the cursor some lines up, until you reach the opening <w:p ...> (word paragraph) in which your field is placed. Right between the previous closing paragraph tag ( </w:p> ) and the opening tag you have found, insert the following:

<xsl:for-each select="table1/Detail_Collection/Detail">

We use table1 because that is the name of the table in Report1.rdl. Now we need to find the place for the closing tag:

</xsl:for-each>

In our example, we have placed it right before the <w:sectPr...> tag. The correct place for your case will depend on your particular layout. To be able to easily find the correct places for the starting and ending xsl:for-each tags, it might be usefull to use placeholders (special characters) as we did with the field names (during the layout preparation). Since TidyOutput5.xsl is an indented version (images are broken), we generate the non-indented version of it:

tidy -utf8 -xml -output TidyOutput6.xsl TidyOutput5.xsl

and upload TidyOutput6.xsl to Reporting Services.

9. Production Now the report is in place and the correct .xsl is also deployed to reporting services server. You can retrieve the document using:

http://reportserver.yourdomain.com/ReportServer?/directory/Report1
 &rs:Format=XML
 &rs:Command=Render
 &rc:XSLT=TidyOutput6.xsl
 &rc:FileExtension=xml

Screen capture of the resulting Word XML report inside MS Word 2003

Note that the final document is not a Microsoft Word Document file (.doc), but a XML file that contains information to be interpreted by Microsoft Word 2003 and be displayed as if it were a DOC file. If you really need the file to be a Microsoft Word Document file you can then (once opened inside Word) Save as... and select the .doc format. This can be achieved also using VBScript:

Set oApp = CreateObject("Word.Application")
oApp.WindowState = 1  ' 0=wdWindowStateNormal; 1=wdWindowStateMaximize; 2=wdWindowStateMinimize
oApp.Application.Visible = False
oApp.Application.Documents.Open XMLFile ' Dim XMLFile As String
oApp.Application.ActiveDocument.SaveAs DOCFile, wdFormatDocument ' Dim DOCFile As String
oApp.Application.Visible = True ' Now the currently opened file is a DOC file

Download: You can download all the sample files together with the resulting report from here.

Keywords: reporting services, rs, render, export, generate, word, ms word, word document, word file, doc, xml, xsl, templates

27 comments:

Anonymous said...

Very cool, many compliments.

Anonymous said...

I found the problem on my xml, its because the binary data on it, I set w:embeddedObjPresent to "no" and remove all bin data into docOleData tags, to my images I remove all data into binData too and replace information about image from imagedata src="wordml://03000001.png" to her fisical path src="http://localhost/03000001.png", thats run so fine here. Your article is very good, without this, I would have to buy some render extensions, thanks a lot.

J.A. García Barceló said...

If the reason for your RS generated .xml to become invalid are the embeeded images, maybe you forgot to execute the command:
tidy -utf8 -xml -output TidyOutput4.xsl TidyOutput3.xsl
explained at the end of step 5. This will remove indentation and make the file valid again (and smaller), thought less human-readable.

Ford said...

I've run into a problem where the XML output's "Detail" node is missing an attribute if that attribute's column value is NULL.

So, "SELECT 1 AS a, NULL as b"

Would render as "<Detail a="1" />"

This causes an error when I'm looping through the attributes in my XSLT (some nodes have 2 attributes, some only have 1).

Apart from wrapping each column in my SELECT with an "ISNULL", can you think of a way around this?

Thanks

J.A. García Barceló said...

No, the only workaround I see is using ISNULL to convert every nullable column into an empty string ('') if needed (or any other value, '<NULL>' for instance).
After all, null is something that has sense in the database world, but not so much sense in the printed world: you would need to change null into something printable in order to see it.
Thanks for posting.

vishal said...

Hi,

This is really helpful. Is it possible to get the image (Image data is stored in binary format in Image datatype column in SQL Server 2005) in the word document?

Thanks

Vishal Patel

J.A. García Barceló said...

I'm sorry but I've never tried such a thing. Maybe you could if you manage to understand the internal format for doc files saved as xml. You can try with a test .doc file that contains an image and search for something like <:binData w:name="wordml://resourceidhere.emz">. This tag defines the image. Then you can use the image inside the xml document using <v:imagedata src="wordml://resourceidhere.emz" o:title="tittle for image"/>

Other option is wait for SQL Server 2008 that will include RS support for doc file exporting ;)

Regards.

Elliott Kayser said...

Hi this article is very very useful and works excellently, my only question is that if your report is running from a storedprocedure within a dataset that requires a parameter, how can we feed it to the final output URL.

Is there a way of adding a parameter to the querystring?

Kaori said...

We used this method to generate Word file from Reporting Service but we've encountered a strange problem and wondering if anyone else encountered the same thing.

Following the instruction on this page almost for word for word except we changed .xml extention to .doc. This worked fine in one version of Reporting Service but when used different version of Reporting Service the xml declaration line is deleted despite xsl:output omit-xml-declaration="no". When the xml dec line is deleted, Word can't open the file.

The Reporting Service version 8.00.1038.00 developer's edition with SP2 had this problem. Whereas RS version 8.00.743.00 developer's edition without SP worked fine.

We'd appreciate any suggestion to why this happens.

Anonymous said...

I'm very new to all of this and have a question. It doesn't appear as if my 'fields' in the Word document are being recognized. I don't find any '<<' when I search after all these steps. I'm sure this has to be that I'm not marking them as a 'field' and they're just being treated as text. I tried to use the mail-merge fields and those weren't recognized either... Any help would be appreciated. Thanks!

José Antonio García Barceló said...

The fields must be included within the body of the Word document simply as text (not mail-merge nor any other weird thing). When I said you should name your fields after «FieldName», notice that '«' and '»' are single chars (not '<<' nor '>>'). They are just placeholders so that you could search and replace for your fields afterwards in an easier way. If you used '<<' and '>>' in your Word source document, those chars are replaced by &lt;&lt; and &gt;&gt; in your .xml, so you will need to search and replace for the string '&lt;&lt;FieldName&gt;&gt;'.
I hope this helps.

Anonymous said...

I found this a very useful example and fun to do. I was looking for an example using SSRS directly against a SQL Server table where the column is XML. Do you know of an example like this? Thanks!

Anonymous said...

The thread is really useful, I followed the steps until I started to export xml file from Report manager.Then I open that xml file which is transformed by xslt already. The format is exactly the same as output from doc to xml. However, I cannot open the xml by "Words". When I used "cmd" to edit the xml file, I found some special characters before the xml like:X@#alt;?xml version="1.0"...
I think this is the problem that i cannot use word to open the xml file.
Can I have some suggestions from you
Thx

José Antonio García Barceló said...

All the reports I prepared at work (when generated, final output from RS) start with:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<?mso-application
progid="Word.Document"?>
<w:wordDocument w:macrosPresent="no" w:embeddedObjPresent="no" ...

If yours do not start at least with <?xml version="1.0" encoding="utf-8" standalone="yes"?> then the file is not a XML valid file.

I would start following the steps of this post using the attached example and, in every step, check that your output matches my file for that particular step.

If you are using < (or > characters) in your word document take into account that they are encoded as &lt; (and &gt;). Maybe you are double encoding (or decoding) or replacing something that should not be replaced.

Alex Ancheta said...

I'm still unclear as to how I can upload the .xsl file into RS. What are the exact steps to do it. Is it the same as adding an existing rdl file?

José Antonio García Barceló said...

Yes, simply browse to your reporting services web site (~/Reports/Pages/Folder.aspx) and upload the .xml file as you would upload any other file.

Anonymous said...

Awesome tutorial, really helped my a lot!

RC said...

Doesn't work for me. I used your example, all RS does is show the report

José Antonio García Barceló said...

Of course, if you query que RS without customising the URL, all you get is the simple report, without any special format. You need to add "&rs:Format=XML&rs:Command=Render&rc:XSLT=TidyOutput6.xsl&rc:FileExtension=xml" (without quotes) to the URL so you get the XSLT transformation of the original report.

Regards.

Anonymous said...

Cool,
But is there any way to pass parameters (user can choice) before rendering? (not in url?)

José Antonio García Barceló said...

The only way to pass parameters is in the URL, so if you need to add a filter or any other parameter to be added at the URL you will need to do it beforehand and then (programatically) add those parameters to the URL and download the transformed file.

Please note that the XLS transformation is done when the following parameters are added to the URL:
&rs:Format=XML
&rs:Command=Render
&rc:XSLT=Transformation.xsl
&rc:FileExtension=xml

Reporting Services works as usual until you add those parameters to the URL. Having said that, you could try to configure the parameters using the builtin fields in RS and then add the former parameters at the end, when you have your report filter configured.

Anonymous said...

good article, but a question that as I use VBScript?, thanks

cjsa said...

good article!!!! could you fix your download link please!!!!! because doesn't work tks

José Antonio García Barceló said...

Download link is fixed now. Sorry for any inconvenience.

Anonymous said...

Hi,

Is this only possible with 2008 R2?

Thx,

José Antonio García Barceló said...

This post is originally written for SQL 2005 and Reporting Services 2005 (which did not support exporting to Word format). However, I've been using this 'hack' with all SQL Server versions up to SQL 2008 R2 (included), because of the fidelity of the Word format it generates (or when it is quite complex).

Anonymous said...

Jose,

Great article! Is it possible to get charts from the SSRS on word document using this approach.

Thanks
Shalinder