RS and XSLT, pt3.2: Using XSLT for Custom Reporting Services Output
August 17, 2008
(Hm, forgot to continue this a few months back...)
In the third installment of a series, we defined some intended flat file output, designed a simple report in MSSQL Reporting Services, and looked at the plain XML output from Reporting Services. Now, long after that post, we can create and apply the XLST needed to convert that report to the desired output.
Here's the raw XML output again:
<?xml version="1.0" encoding="utf-8"?> <Report p1:schemaLocation="RsXsltDemo http://reportserver?%2fRsXsltDemo&rs%3aFormat=XML&rc%3aSchema=True" Name="RsXsltDemo" executeTimeStamp="2008-02-28T14:49:23.7940175" textbox2="3" xmlns:p1="http://www.w3.org/2001/XMLSchema-instance" xmlns="RsXsltDemo"> <resultSet> <Detail_Collection> <Detail col1="r1c1" col2="r1c2" col3="r1c3" /> <Detail col1="r2c1" col2="r2c2" col3="r2c3" /> <Detail col1="r3c1" col2="r3c2" col3="r3c3" /> </Detail_Collection> </resultSet> </Report>
And here is the intended output, fixed up so that it consistently uses the pipe character instead of commas in the header and trailer:
HEADER|MYREPORT|<datetime> <col1 data>|<col2 data>|<col3 data> <col1 data>|<col2 data>|<col3 data> TRAILER|<row count>
Now let's build up the XSL needed to transform this.
Startup
1 <?xml version="1.0"?> 2 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:rpt="RsXsltDemo"> 3 <xsl:output method="text" encoding="utf-8" media-type="text/plain" /> 4 <xsl:strip-space elements="*"/>
So, we have the standard start to an XML stylesheet, followed by a command to save the output as plain text in UTF-8 format, and a command to trim all data elements.
Create a Template
5 <xsl:template match="Report"> 6 <xsl:text>HEADER|MYREPORT|</xsl:text> 7 <xsl:value-of select="executeTimestamp"/> 8 <xsl:text>
</xsl:text> 9 <xsl:for-each select="resultSet/Detail_Collection/Detail">
Taking this line-by-line:
- Sets this template applicable to the Report element in our source XML.
- Places static header into the output.
- Selects the timestamp value from the input and places it into the output.
- Line break and carriage return (or is that carriage return and line break?)
- Start a loop on all Detail elements in Detail_Collection.
Format the Detail Lines
10 <xsl:value-of select="@col1"/> 11 <xsl:text>|</xsl:text> 12 <xsl:value-of select="@col2"/> 13 <xsl:text>|</xsl:text> 14 <xsl:value-of select="@col3"/> 15 <xsl:text>
</xsl:text>
- Insert the value from the column named col1
- Insert the pipe character
- Value from column named col2
- Pipe character
- Value from column named col3
- Carriage return and line break
Finish Off the Template
16 </xsl:for-each> 17 <xsl:text>TRAILER|</xsl:text> 18 <xsl:value-of select="rowCount"/> 19 </xsl:template> 20 </xsl:stylesheet>
- Close out the loop
- Add the static trailer text
- Insert the row count from the input
- Close out the template
- Close out the stylesheet
Applying the Template on Output of XML
In the Report Builder, Layout tab, open the report's properties box and switch to the Data Output tab. Add the name of the transform (full file name, including the XSL extension) Finally, preview the report and export as XML. Is your output what you hoped for?
In the next and final installment, we'll add the complexity of a fixed format.