Configure a Report to Accommodate Export of "Raw Data Using XSLT"
When exporting data from a Reporting Services report into Excel, users often remark that the resulting spreadsheets are difficult to work with and contain extraneous formatting that makes sorting and totaling data cumbersome. To work around this, you can create an XSLT (eXtensible Stylesheet Language Template) that can be applied to your report to output data into an Excel format you desire.
Put simply, the XSLT takes the exported report data via XML output and formats it in exactly the way you configure.
Figure 1 - The native Excel export option includes extraneous columns and many merged cells making filtering, copying, sorting difficult.
Figure 2 - The same report, when exported via an XSLT template into an XML spreadsheet is immediately manipulatable.
In the Reporting Services environment, once the user has executed the report and wishes to export it to the “Raw Data” format, they will select “XML file with report data” from the below export dialog. Consider that this is probably not intuitive for most users and that some user education may be required.
Figure 3 - Export Dialog
Upon clicking “Export”, the user will be prompted to Open/Save (see below image). If they choose Open, it will open in Excel as an XML spreadsheet. Note – Developers with an XML editor installed on their machines (e.g. Visual Studio) may be prompted with a choice to open the XML file with their XML editor.
If the user selects Save, the resulting file will save with a .XML extension. The icon for the file will reflect that it is an XML Spreadsheet – compare this with the normal Excel icon (see below image).
|Figure 4 - Open/Save Dialog
|Figure 5 - XML Spreadsheet and Standard Excel Icons
Configuring the XSLT file
In designing the output, the simplest approach is to use a user-modified Excel spreadsheet as a starting point. Ask the user(s) to provide their ideal Excel spreadsheet output for the report. Convert this Excel spreadsheet to XML – you can then copy/paste components of that XML (e.g. Styles) into your XSLT template.
Use the following XSLT template as a guide to structure and syntax. It is simplified and commented for illustration’s sake. For a step-by-step example of how this can be applied see: http://spacefold.com/lisa/post/2007/10/03/changing-the-sheet-names-in-sql-server-rs-excel-qnd-xslt.aspx. This contains valuable explanations of the structures / syntax and is one of the seminal articles on the Web related to this concept.
- There is significant flexibility here – your XSLT template can be configured to display data in any program capable of rendering XML (e.g. MS-Word).
- Be creative – if multiple/differing spreadsheet outputs are desired, consider using a variable in the report to define which XML the XSLT will render or consider rendering the differing outputs to different tabs within the same spreadsheet.
- When developing your XSLT file, many of the errors encountered will be related to the location context within the XML (i.e., the location of the nested data regions within the XML). This may be the first thing you want to check when troubleshooting.
- In addition to raw data output, many spreadsheet features can be rendered through the template output (e.g. calculations, conditional logic).
The last step is to reference your XSLT file in the report RDL. This is done by setting the “DataTransform” property of the Report object in the RDL to the name of your XSLT file. Then publish the XSLT file in the same directory as your RDL on the SSRS webserver.
Figure 6 - Configure RDL to use the XSLT
The logic behind this approach was initially taken from these articles:
For further examples - most of the reports in the Financial folder of the production EDW Reporting Services environment are configured to use an XSLT output. Click the “Show Details” link to view the XSLT files.