An alternative way to the MSWord Render extension of SQL Reporting Services using XSLT and XML document mapping

• Download source files - 8.98 KB

Introduction

SQL Reporting Services doesn't support exporting reports to MSWord yet. Using render extensions for exporting MSWord is not so easy. This article describes exporting a report to an MSWord doc file that already has some data. Some client/end users always prefer adhoc reporting and some prefer a basic report template. MSWord is heavily used and is in general what users prefer most. This article describes converting an XML file (exported from Reporting Services) to MSWord. To do this, there are a lot challenges. I'm describing them one by one below.

I'm assuming that readers are familiar with SQL Reporting Services, XML, XSD, and XSLT.

Details of the attached sample.zip

• BookOrder.xsd is the data schema of BookOrder.
• BookOrder.doc contains the mapping with the data schema.
• BookOrder.xml is a sample data file generated from SQL Reporting Services.
• BookOrderMAPPED.xml is sample doc file saved as XML format.
• BookOrder.xslt contains detailed formatting. This file is generated from BookOrderMAPPED.xml by using the Word ML Processing Tool (the command line to generate XSLT is [WML2XSLT.EXE "file name" -o "output file name"]. A sample command: [WML2XSLT.EXE -o "BookOrder.xml" -o "BookOrder.xslt"]. Users have to generate the XSLT each time they modify BookOrder.doc. They can keep the previous version XSLT also for keeping history. Any BookOrderMAPPED.xml can be formatted by any kind of XSLT unless the data schema (BookOrder.xsd) changes.
• wml2xsl.msi is a installer file to getting the WML2XSLT.EXE Word ML processing tool. Download URL: http://www.microsoft.com/downloads/details.aspx?FamilyID=2cb5b04e-61d9-4f16-9b18-223ec626080e.

Basic steps to convert XML to MSWord file

1. Prepare the report data that is exported from SQL Reporting Services.
2. Design the XSD.
3. Map the MSWord document with XSD.
4. Generate the XSLT file.
5. Apply XSLT on XML to get the desired MSWord document.

SQL Report Viewer built-in with Visual Studio has the option to export a report to XML. A data schema XSD is needed for mapping to the MSWord template. The mapped word file may contain some static data or might be empty. It is also possible to maintain the Word template version if the report data schema remains the same. After every modification, we must generate the XSLT file. Applying different XSLT on the same XML will generate different MSWord files. Applying XSLT can be done programmatically or manually.

Preparing the report data before exporting to XML

By default in SQL Reporting Services, each element data output type is set as auto. I'll talk about XML mapping in the "Walk through of mapping the data schema to a Word document" section. Also, you don't need to export each data element. It is possible to set each data element from the property window and we also have the option to render an element/attribute.

Some times in reports, null values appear. If a null value comes in table cells, the table cells in MSWord will not render and also table cell alignment will be broken. To resolve this, pass a default character for a null value.

We can easily handle this by the IIf function from Reporting Services. E.g., the following expression:

=IIf(IsNothing(Fields!CompanyName.Value),"-",Fields!CompanyName.Value)

Grouping

Mapping a collection object when it comes with a group is difficult, but using XSD rules, most complex scenarios are easy to handle. Let's see an example.

Create schema rules less restricted. Allow nullable, set min-max, outbound value, etc.

Date format

In XML, date time data comes with row format but different date formats are used in different regions and it also depends on the organizational culture. To solve this, load the XML using the parser, visit all nodes, and replace data with the desired data format. But it's not an efficient and good solution if the number of XML nodes is too high. If so, set an XSLT before exporting the report from Reporting Services. This option is available in the report page property in Reporting Services.

Namespace problems

When XML is exported from Report Viewer, it adds a default namespace but if you want to change or modify the namespace in XSD before transforming, you have to change it programmatically.

//set the namespace as xsd  
XmlDocument oXmlDocument = new XmlDocument();     
try
  {
    oXmlDocument.Load("serverFileName");
    oXmlDocument.DocumentElement.SetAttribute("xmlns", "http://www.w3.org/2001/XMLSchema");
    oXmlDocument.Save("serverFileName");
}catch{}//don't try this at home
 
oXmlDocument = null;
GC.Collect();
GC.WaitForPendingFinalizers();

Transform using MSWord:

private Microsoft.Office.Interop.Word.ApplicationClass oWordApplic;
 // a reference to Word application
private Microsoft.Office.Interop.Word.Document oDoc;
// a reference to the document
 
object missing = System.Reflection.Missing.Value;
oDoc = oWordApplic.Documents.Add(ref missing, ref missing, ref missing, ref missing);

oDoc.Activate(); 
oDoc.TransformDocument(filename, false);
oDoc.Save();
 
object missing = System.Reflection.Missing.Value;
oWordApplic.Application.Quit(ref missing, ref missing, ref missing);

Walkthrough of mapping the data schema to a Word document

1. Open a new Word document, and select the XML structure from the task pane.



Figure1.3: Start new document
2. After clicking Templates and add-ins, the following window comes with the option to add a schema.



Figure1.4: Schema settings
3. After clicking Add schema, the following window comes and asks for a name, and this will complete adding the schema to a file.



Figure 1.5: Schema alias settings

To change the applied schema any time, go to the Tools> Templates and add-ins menu.

After adding the schema, all the data elements will be available on the Task pane.

To map data in a Word document, we need to select the data and click on the left side data schema. For example, select the date and click on date from the left menu, and this will insert the XML tag shown as follows:

Sample.zip has a BookOrder.doc which is fully mapped with BookOrder.xsd. After completing the mapping, the files have to be saved in XML format. To save BookOrder.doc to XML format, choose the File>Save as option.

The File Save dialog has two options: Apply transform and Save as data, uncheck both. Sample.zip contains the BookOrderMAPPED.XML file that is saved as XML.

The next job is generating the XSLT file. From the command prompt, enter the directory and use the following command:

WML2XSLT.EXE -o "BookOrderMAPPED.XML" -o "BookOrder.xslt"

Now open the BookOrder.XML data file with Word.

In the task pane, Word will give two options: Data only and Browse. Click on Browse and choose BookOrder.xslt for getting the desired output.

If any change occurs in BookOrder.doc in the data mapping area, generate a new XSLT and apply it on BookOrder.xml to get the changes.

Enjoy....