Stephen A. Fuqua (saf)

a Bahá'í, software engineer, and nature lover in Austin, Texas, USA

RS and XSLT, pt3.3: Using XSLT for Custom Reporting Services Output

We recently returned to the discussion of using XML style transforms to format flat file output from Reporting Services. The report we created was pretty basic, so let’s look at something more complex: a fixed format.

Let’s say the client has given us the following file format, and we’ve trimmed the data in the SQL query to make sure it doesn’t exceed the data width:

Record Type Static Data Dynamic Data Width
Header 01   2
Header MyReport   9
Header   Date and Time 10
Header 0x0D0x0A   2
       
Detail 02   2
Detail   col1 9
Detail   col2 10
Detail   col3 15
Detail 0x0D0x0A   2
       
Trailer 03   2
Trailer   Row count 9

New problem for our XSL: need to dynamically pad a column to make sure that it reaches the specified width.

Introducing the Recursive Space Template

This is a cool function, add it to the template file:

<xsl:template name="Space">
  <xsl:param name="count" />
    <xsl:if test="$count">
      <xsl:value-of select="' '"/>
    <xsl:call-template name="Space">
      <xsl:with-param name="count" select="$count - 1" />
    </xsl:call-template>
  </xsl:if>
</xsl:template>

Line-by-line analysis:

  1. Start the template
  2. Create an input parameter/variable called count (think of this like a function's argument)
  3. Branch the template when the count variable has data (is greater than zero)
  4. Add a space character
  5. Call the template again, recursively
  6. Set the input parameter to the current count minus one
  7. Next three lines wrap things up.

Variables for the Column Widths

<xsl:variable name="WidthCol1" select="9" />
<xsl:variable name="WidthCol2" select="10" />
<xsl:variable name="WidthCol3" select="15" />

Calling the Space Template

<xsl:value-of select="@col1"/>
<xsl:call-template name="Space">
<xsl:with-param name="count" select="$WidthCol1 - string-length(@col1)" />
</xsl:call-template>

Analysis:

  1. Insert the col1 value
  2. Call the Space template
  3. Set the input parameter as the pre-defined column width for column1 minus the length of the data value
  4. End the Space template call

Putting It All Together

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:rpt="RsXsltDemo">
  <xsl:output method="text" encoding="utf-8" media-type="text/plain" />
  <xsl:strip-space elements="*"/>
  <xsl:template name="Space">
    <xsl:param name="count" />
    <xsl:if test="$count">
      <xsl:value-of select="' '"/>
      <xsl:call-template name="Space">
        <xsl:with-param name="count" select="$count - 1" />
      </xsl:call-template>
    </xsl:if>
  </xsl:template>
  <xsl:variable name="WidthCol1" select="9" />
  <xsl:variable name="WidthCol2" select="10" />
  <xsl:variable name="WidthCol3" select="15" />
  <xsl:template match="Report">
    <xsl:text>01MYREPORT </xsl:text>
    <xsl:value-of select="executeTimestamp"/>
    <xsl:call-template name="Space">
      <xsl:with-param name="count" select="$WidthCol1 - string-length(@executeTimestamp)" />
    </xsl:call-template>
    <xsl:text>&#xD;&#xA;</xsl:text>
    <xsl:for-each select="resultSet/Detail_Collection/Detail">
      <xsl:text>02</xsl:text>
      <xsl:value-of select="@col1"/>
      <xsl:call-template name="Space">
        <xsl:with-param name="count" select="$WidthCol1 - string-length(@col1)" />
      </xsl:call-template>
      <xsl:value-of select="@col2"/>
      <xsl:call-template name="Space">
        <xsl:with-param name="count" select="$WidthCol2 - string-length(@col2)" />
      </xsl:call-template>
      <xsl:value-of select="@col3"/>
      <xsl:call-template name="Space">
        <xsl:with-param name="count" select="$WidthCol3 - string-length(@col3)" />
      </xsl:call-template>
      <xsl:text>&#xD;&#xA;</xsl:text>
    </xsl:for-each>
    <xsl:text>03</xsl:text>
    <xsl:value-of select="rowCount"/>
    <xsl:call-template name="Space">
      <xsl:with-param name="count" select="$WidthCol2 - string-length(@rowCount)" />
    </xsl:call-template>
  </xsl:template>
</xsl:stylesheet>

Comments

imported from old Movable Type blog

author: James Telfer
date: ‘2008-10-28 18:44:11 -0500’
url: http://jtnlex.com/blog

After going through pretty much the same experience as you seem to have with this series of posts, I’ve come up against something you haven’t touched on yet: output escaping.

I have the output method set to text, which Visual Studio and Xalan honour. But when I hook the template up to SSRS, less/greater than signs etc appear as their entity escaped values. I can avoid this by using the ‘disable-output-escaping’ attribute on the xsl:text and xsl:value-of elements, but according to the spec this should be ignored when the output type is text.

Have you come up against this problem?

Cheers, JT

author: Stephen Fuqua
date: ‘2008-10-28 20:15:19 -0500’

I haven’t needed these characters in any of my text reports so hadn’t run into it. Thanks for the heads up!

Posted with : Tech, Microsoft SQL Server and other databases, SQL Server