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