XML/XSLT – Page 26

I had a nice note from Gilda Tramontana this week, asking for more information about something I'd posted about revising the Excel output you get from SQL Server Reporting Services.

Someone had noted, in the forum thread that Gilda referenced, that the way that documentation acknowledges the limitations of the default Excel output is "amusing". Apparently the term they use in the docs is "unique attributes" and, believe me, they're not talking about primary keys here.

In another dev universe, long ago and far away, we used to refer to these "unique attributes" as "infelicities" or "devolutions". They're not bugs, they just… are.

Whatever. In the thread, I responded that the Excel default output is not the only Excel output available to you from SQL Server RS:

What I mean: you can use the data-xml export from RS and associate an XSLT stylesheet with it. That means you can take the data and make presentation in Excel you want, just go out to SSML. SSML is Excel 2003's XML dialect. It's pretty straightforward, although Excel's debugging abilities are minimal — .it's hard to see what you did wrong, so get used to reading an XSD!

… and you can read the rest of the thread if you want… but Gilda wrote asking for more information and I said I would do my best. I'll now provide a walkthrough here of how you apply the XSLT and how you create it.

I hope this walkthrough will not only show you that you can do it, but also why it is hard to do it generically. My example will be complete, and will provide you with some generic bits to start you off. But you will have to adapt a bit. Ready?

First, realize that I said the data-xml export option. Not the Excel export option.

Second, realize that you can take any Excel export document that you get from a Reporting Services report and Save it As SSML (Excel 2003 Spreadsheet XML dialect), to give you a reasonable starting point. IOW, you really don't have to figure out how to do this from scratch.

So here is what you do:

Export your report as Excel and, from Excel, Save a copy of the sample output As SSML. Like I said. This is your example of your almost-perfect target result.
Also, from Reporting Services or the preview, Export your report as Data XML, so you know what the underlying data you are going to have to work with at runtime looks like. Now you have an example of your source, from which you are going to derive your perfect target result.
Now you need to create an XSLT style sheet that will reproduce what you have in the SSML, with one simple change (in this case: the name of each worksheet), and plugging in your source Data XML into the Excel rows instead of the literals you see in your SSML sample.
Try not to faint. The samples you just saved give you almost everything you need. I'll provide an illustration, and walk you through this part, below.
Now attach your XSLT document to the report by putting its name in the Data Output tab of the Report Properties dialog.
You can test your results at this point by choosing Export to XML file with report data from Report Preview. If you've done this right, the result is SSML.
Deploy your XSLT document to your report server.
You can do this by rightclicking on the XSLT within your Visual Studio project, the same way you deploy the report , or you can do it by uploading the XSLT as a file in the Report Manager (which of course you can also do with the RDL).
Re-deploy your report to the Report Server, too, because you've adjusted its properties to include the XSLT reference.
When you Export this report to Data XML, your result is now Excel in your preferred format !

Here's the zoom-in instructions for step #3 above, the part you're scared about doing.

In my example, the report is Recipients grouped by Locale, so Locale is the group-break value for which I would like to change the worksheet names.

Remember: Excel creates different sheets in response to your explicit data page breaks, usually on a group within a data region.

When I examine my report data sample, I might see something that looks like this:

It's a pretty straightforward structure. You can see where the label for locale that corresponds to my group break with the paging instructions is located (there is a table1_Locale element for each, with a Detail_Collection underneath). You can see where the elements for each detail row are going to come from (there are Detail nodes in each Detail_Collection).

When I examine my sample SSML export, I see something that looks superficially much more complicated, but actually has a very logical structure . It has a Workbook root, under which are three first-level children with global information (DocumentProperties, ExcelWorkbook, and Styles) followed by a succession of Worksheet nodes.

Your XSLT is going to preserve much of this sample intact. It's going to repeat the three first-level "prolog" children pretty much verbatim (actually, I recommend that you strip out much of the ExcelWorkbook node — you'll see why when you check out its contents — and you can remove as much of DocumentProperties as you want). After the "prolog", it will provide a template for the Worksheets, as you'll see below.

Start your XSLT document with something that declares all the necessary namespaces from Excel's point of view, and create a template that matches any root element ( "/*"). This is just an easy way to avoid specifying the namespace of the Report root node for the match. You can use the skeleton you see here, verbatim (just add the Styles from your sample):

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
     xmlns="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:o="urn:schemas-microsoft-com:office:office"
     xmlns:x="urn:schemas-microsoft-com:office:excel"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:html="http://www.w3.org/TR/REC-html40">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="no" omit-xml-declaration="no"/>

<xsl:template match="/*">

      <?mso-application progid="Excel.Sheet"?>
      <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:html="http://www.w3.org/TR/REC-html40">

         <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
         </ExcelWorkbook>

<!– see below for your Worksheet template–>

</Workbook>
</xsl:template>
</xsl:stylesheet>

Now you need to add some information in where I've indicated the Worksheet template goes, into the skeleton I've provided above. This is the part that is quite difficult to do generically, because, depending on how you've nested and named your data regions, I can't tell you exactly what element you want to drill down to, to match what appears as the "page element" in your report's XML data.

In mine, it's an element named table1_Locale, a couple of layers deep. I'm going to iterate through all of these nodes in the data XML, and create a Worksheet node for each in the output. As before, I'm going to use * instead of literal names for the nodes, to avoid bothering with namespace discussions.

As you can see in this skeleton, I'm going to create my sheet names as an expression (the word "Locale-" plus the value of the Locale attribute that holds the value for this group in the source data XML. That's the one significant change we're making from the original content provided by the default Excel renderer; the original sample you saved would have had an attribute that said something like ss:Name="Sheet1".

<xsl:for-each select="./*/*/*"> <!– Report/table1/table1_Locale_collection/table1_Locale —>
  <Worksheet ss:Name="{concat('Locale-',@Locale)}">
<Names/>
  <Table >
  <!– see below for what goes in here —>
  </Table>
</Worksheet>
</xsl:for-each>

The curly braces you see are a shorthand way to insert the result of an expression in an attribute. You may never have seen that syntax before. If you prefer, you can use xsl:attribute instruction to supply the sheet name as an expression.

Inside the Worksheet's Table node, I return to my SSML saved sample for more content that I can plop in verbatim: first, a set of Column definitions, followed by some Row nodes that hold the header rows for each sheet. You can just paste this in from your saved sample.

Anything that isn't the same throughout the report, such as the name of the Locale for each "page" in my case, can be replaced by an appropriate expression. Here's a header row from mine; you can see the xsl:value-of construct supplying the name of the Locale for this worksheet:

Finally, below your header rows, it's time to iterate through your details. Put another xsl:for-each construct in place at this point in the template and point it at your Detail nodes. Paste a sample row into the xsl:for-each, right out of your SSML sample content, and adjust the content of each cell to point at the appropriate data element. Mine looks like this:

<xsl:for-each select="./*/*"> <!– DetailCollection/Detail —>

… and that's pretty much all she wrote. Get the idea?

Now, as I said, you can start thinking about how many other forms of Excel you might support from the same data.

You don't have to design the formatting in an RDL at all, if you don't want to. Design it in Excel exactly the way you want it, save out the SSML, and then plug your data elements into it from the original report…

"Unique attributes", indeed. Hmph.

Category: XML/XSLT

Changing the Sheet names in SQL Server RS Excel: QnD XSLT