YAPS: Random acts of kindness regarding Reports, Excel, XSLT, and you

by Lisa Nicholls Sun, February 08 2009 00:34

You've provided some gratifying responses to my unintended continuing series on using custom XSLT to derive better Excel  or other formats for your reports than the standard stuff  you get from SS Reporting Services' Excel renderer.  With the gratifying responses come lots of questions about details and areas that I haven't covered.  So here's a round up of some of those points.

I'm going to thank Beth for most of the items in this catch-all list, but I'd also like to issue a blanket "thank-you" to the astounding number of readers who continue to be interested in this topic.  

What do you use to test the XSLT (Part 1)?

A number of people have asked about my toolset; I've talked about the commercial XML editors that I use, and recommended some others.  Most XML editors give you an opportunity to generate output by apply an XSLT transformation to an XML document, although each is different. 

For those of you who are working in Visual Studio or the BIDS version of Visual Studio that ships with SQL Server as a development tool, you have a very simple choice in your current environment without adding on any tools. 

I didn't realize this was a problem for you, until Beth wrote about it.  As she worked on her custom XSLT for a report, she was deploying the XSLT to her report server to check it out -- or possibly exporting XML from within the Report Preview in Visual Studio;  I'm not really sure. 

The point is, running your report to check your transformation is the long way around, especially if your report runs lots of complex queries. 

Let's leave the RDL alone, and stop poking it, shall we?  You're finished developing the thing, you're just supplying an alternative format now.

Here's what I suggest you do:

  1. Export the XML from your report without  adding a custom XSLT, so you have your raw source file.  You needed this anyway, to figure out how the file was constructed, right?
  2. Open the exported XML as an XML file in Visual Studio.
  3. Open the Properties Window for this XML Document; you'll see something like this (in VS 2005; slightly better in VS 2008 in every way):

    Editing an XML Document in VS 2005  

  4. You see, in the Properties window, where there's a place for a Stylesheet name?  Go ahead and navigate to the one you've been working on; there's a little ellipsis for a file-picker dialog to the right, on that line, not showing in the screen shot here.  While you're at it, put an "output" filename in the appropriate row, although this is not required. 

  5. After visiting the Properties window, click back into the XML Document editor, on the left side, and wait for your change-of-focus to register.  You should see an XML context menu pad.  Open it.  Guess what happens when you use the "Show XSLT Output" item on this menu popup?

    No prizes for guessing...

  6. If you're a VS person anyway the above procedure is really all you need.  While VS isn't a great XSLT editor, it's fine for occasional use, and you can do all your work within your accustomed IDE without strain.

What do you use to test the XSLT (Part Deux)?

What if you're not a VS person?  Well, frankly, if I just want to apply an XSLT to an XML, I usually go with the command-line approach, using MSXSL.EXE.  This tiny EXE (it's under 30K) has decent error reporting, allows you to specify XSLT parameters (although you won't be doing that in an RS context), etc.  It's freely   available from Microsoft from the XML developer section of MSDN. You'll find it easily if you search for "command line transformation utility" or "msxsl.exe" in the downloads section.

MSXSL is, of course, just a quick wrapper around the MSXML core libraries, so you can also write yourself a little VBS script that does the same thing.   I've already posted an example of how to do this, and the example script referred to in that post, transformRDL.vbs, is part of the samples that accompany RDL Documenter.

MSXSL.EXE command line options

Put some PowerShell to work on top of this, if you want to!

The raw xml is the page content data, not the page header and footer data

Most banded report writers have to make a distinction between what I call the "formatting bands" and the "content bands".  "Formatting bands" are full of information that requires pagination to be applied, and therefore will be different from renderer to renderer, and different rendering instance even when done by the same renderer, because of different output page sizes.  In the case of the SQL Server Reporting Services engine's XML renderer, there is no concept of pagination, so the XML renderer only gives you the contents of the page body. 

Beth wanted to include some information from the page header in her report (possibly in the sheet tab name, or the print setup information we've just discussed).  Since the XML renderer doesn't give you that information, the usual way to handle this is to include everything you want in the page body instead.  You can make it white-on-white, so it doesn't appear in other renditions, or play other tricks to hide it. 

For what it's worth, I sometimes find myself putting the whole "Page header" into the body of the report for other reasons, and telling it to repeat on page.

Ersatz Page Header information in Report Body, using repeat on page

If you have an issue with getting something like this to work, give me a specific case, and I'll explain in more detail.

What can SSML (Excel 2003 XML Dialect) do? 
What can't it do? 
And how do you know?

Just BTW, and as a caveat, I didn't set out to become an expert in SSML, but it's a neglected art, and people keep coming to me for help in it.

It's no wonder SSML is neglected.  If you look up that acronym in Standards Land, it's already been re-used for another purpose (Speech Synthesis Markup Language).  In Microsoft Land, it's been superseded by Excel 2007's XML format, which is a bit harder to use and create, although quite a bit more capable. 

You certainly can't use XSLT with RS to get Excel 2007 as easily as you can create SSML; you'll need some more manual work than just attaching an XSLT to an RDL definition on the server.  So, perfect it isn't, but I still like Excel 2003 XML.

As a general rule, SSML can do ... most things that Excel can do, except for serving as a host to "foreign objects", such as MS Chart and images.  If you want to know exactly what is included in "most things", start with the documentation.

Where in the docs do you find good information about this?  The answer will probably surprise you: try doing a Save As XML Spreadsheet 2003 in Excel 2007.  Even if you're saving a blank workbook, you'll get a warning about features that won't be included in this format.

Saving to SSML from Excel 2007

When you press help, here's what you learn:

XML Spreadsheet 2003

This XML Spreadsheet 2003 file format (.xml) does not retain the following features:

  • Auditing tracer arrows
  • Chart and other graphic objects
  • Chart sheets, macro sheets, dialog sheets
  • Custom views
  • Data consolidation references
  • Drawing object layers
  • Outlining and grouping features
  • Password-protected worksheet data
  • Scenarios
  • User-defined function categories
  • VBA projects

New Office Excel 2007 features, such as improved conditional formatting, are not supported in this file format. The new row and column limits of Excel 2007, however, are supported.

I know you'll be shocked to hear this...

The help file entry you see above is far more complete than the information we had about SSML in Excel 2003 documentation, but it's not complete-complete.

So how do you really know?

Most people get started writing SSML by Saving Excel As SSML and then looking at what they get.  They slavishly follow what they got from the internal "decisions" that Excel makes about how to do this translation.

This practice is okay for a while, but it  leads to a lot of extremely bad practices.

For example, Excel uses generated style names that are difficult to maintain, and tends to include many more of them than you actually have used in any Excel workbook, just because you've changed your mind a couple of times while working on it.  For goodness' sakes: figure out which you need, toss the rest out.  Then re-name them with sensible names so you know to which types of cells you should apply them when you write your XSLT.  I've put a small snippet below; you'll see that I've used a reasonable name.

The right place to learn about SSML is from its schema documentation.

SSML is an XML dialect, the schema is published, as it is with most XML dialects, to give you exactly this information. SSML's XSD is available to tell you exactly what is allowed and what is not allowed, what Excel will recognize and render and what it can't.   Save As gives you the "learn from an example" method, but you can never be sure how much in that example was a quirk and how much was required.  The XSD gives you the whole picture and the correct syntax for everything, without ambiguity.

You'll find the full set of Office 2003 XML Reference Schemas are available on the Microsoft site; if the link I've just given you is not correct, just search for that phrase on the MS site and you'll find it.  The organization of the various general and specific schema files, and the contents of the Excel-specific schema, are straightforward and clear, and they're all heavily annotated.

Office 2003 Reference Schemas

Extra tip:

The editor in the screenshot above is a free editor called First Object XML Editor.  By a wierd coincidence, its short name is FOXE!

This app has few frills but some notable benefits:

  • It loads very large files extremely fast.
  • It's a tiny download with no installation routine (it's a C++ program, I believe).
  • It handles file encodings well (including Chinese characters).
  • It searches and formats/indents large files like a dream.  My team uses it when a file has been created without extra white space. 

    Visual Studio tends to balk at the load because of "long line lengths", or crash when you ignore the warnings and try to load the large file anyway.   We load it quickly to FOXE, use the Indent feature to break the lines up, save it, and then Visual Studio will load the file.

Beth's question: a case-in-point for what's missing from the help file and what's hard to find out

If you look at the screen shot above, you'll see that I've zeroed in on the section describing the "PrintType" complex type, which Contains workbook and worksheet print options, according to the schema annotation.  Just as with "regular" binary Excel, you can specify the comments layout, whether you want draft quality, scaling, paper sizes and resolutions, etc. You can specify whether gridlines are printed, and whether columns are printed left-right or top-bottom as a large worksheet is broken into printer-page-size chunks.

Beth wanted to use Excel's facility to repeat a designated set of rows on every printed page, to use as static column headers.  This was something I had never had occasion to do.

SSML will let you freeze a set of columns or rows in the screen-display of a worksheet, just like binary Excel, and it's very easy to find the facility in the SSML schema:

             <xsd:element name="SplitHorizontal" type="xsd:int" default="0" minOccurs="0">
                    <xsd:documentation>Contains the number of points from the top of the window that a worksheet is split horizontally.</xsd:documentation>
            <xsd:element name="SplitVertical" type="xsd:int" default="0" minOccurs="0">
                    <xsd:documentation>Contains the number of points from the left of the window that a worksheet is split vertically.</xsd:documentation>
            <xsd:element name="FreezePanes" minOccurs="0">
                    <xsd:documentation>Specifies whether the panes of a worksheet window are frozen.</xsd:documentation>
            <xsd:element name="FrozenNoSplit" minOccurs="0">
                    <xsd:documentation>Specifies whether the frozen panes of a worksheet window are not split.</xsd:documentation>

... and SSML also gives you the opportunity to include custom content in the page header and footer; this is clearly spelled out in the XSD.  But I couldn't find what Beth was asking for, and this really had me scratching my head.  The schema contains about a zillion different settings to specify attributes of Pivot Tables. No matter how warped Microsoft priorities are, it seemed inconceivable that they would have left out this basic feature.

Well, they didn't leave it out but, it's not something you'll really figure out from the schema, either.

Sigh.  When in doubt, go back to the "learn from an example" strategy.

It turns out that this feature isn't baked into the Excel XSD because it uses one of Excel's "magic" named ranges: the one called Print_Titles.  Anything included in a named range with this magic name gets treated in a special way, and this is just as true in SSML as in binary Excel.

But the magic range names aren't called out in the annotations (where else would they be able to put a secret knowledge thing like this, from an XML perspective?!?).

The Named Range feature is, of course, thoroughly baked into the XSD.  To use it, you define a range with the magic name and tell it what rows to look at...

<Worksheet ss:Name="Sheet1">
   <NamedRange ss:Name="Print_Titles" ss:RefersTo="=Sheet1!R1:R3"/>

... then you use the NamedCell syntax to call out the items that will actually need to be repeated by Excel. (And don't ask me why it works this way.  I don't know.  That is one of the dangers of learning from examples, remember?)

   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">My major title line</Data><NamedCell
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">My minor title line</Data><NamedCell
   <Row ss:AutoFitHeight="0" >
    <Cell ss:StyleID="sHeaderLeft"><NamedCell ss:Name="Print_Titles"/></Cell>
    <Cell ss:StyleID="sHeader"><NamedCell ss:Name="Print_Titles"/></Cell>
    <Cell ss:StyleID="sHeader"><NamedCell ss:Name="Print_Titles"/></Cell>
    <Cell ss:StyleID="sHeader"><NamedCell ss:Name="Print_Titles"/></Cell>
    <Cell ss:StyleID="sHeader"><Data ss:Type="String">Address</Data><<NamedCell ss:Name="Print_Titles"/></Cell>
    <Cell ss:StyleID="sHeader"><Data ss:Type="String">Invoice To</Data><NamedCell
    <Cell ss:StyleID="sHeader"><Data ss:Type="String">$</Data><NamedCell
    <Cell ss:StyleID="sHeaderRight"><Data ss:Type="String">Notes</Data><NamedCell

One more thing:

When you're trying to find a feature like print setup in the Excel schema, remember to distinguish between an item that is specific to a Worksheet and an item that is global to a Workbook.  These items will be in different places in the schema. 

Print_Titles happens to be a "sheet thing", as my example above probably shows.  However, if I am not mistaken, Excel ranges used for other purposes can be global to the book as well.

What have I forgotten?

There is no end to the wonder and glory that is Excel.

Here's an apology if I have not addressed your question in this random list.  Nudge me e-mail or something and I'll top it up.


Reporting | SQL Server | XML/XSLT | YAPS

Comments (22) -

2/14/2009 12:12:13 AM #


Hi Lisa,

I have (quickly) read through your blog, and I am not sure that the repeating header problem is fixed, but, I won't know for sure until I have some time to compare my XSLT to what you've shown here. I had already got the "Freeze Frame" working, and though I know I had already employed "Print_Titles", it did not cause the column headers to repeat on every page, which is, supposedly, what it is supposed to do. But, there may be something else in my XSLT that causes it not to work.

As for page header/footer, I have not processed, yet, what you've said about that.

regards, beth

beth United States

7/5/2009 1:19:37 AM #


Just subscribed to your RSS Feed, hopefully you poduce mode great Info in the future.

Peter Australia

7/7/2009 8:01:47 PM #

internet marketing

What is the latest version of XML and XSLT? And what is the difference b/w their previous versions?

internet marketing United States

9/16/2009 11:10:05 AM #

Geschenk Kind

Great report, especially that XML-thing is something a little bit difficult. Thanks for that info from Geschenk Kind

Geschenk Kind Germany

10/22/2009 11:59:06 AM #

Alleinunterhalter K&#246;ln

Thanks for your hard work and sharing this for us all.

Alleinunterhalter Köln Germany

11/2/2009 6:04:16 PM #

medical tourism

I've never been a fan of VS, but this is very helpful. Good way to work around some of the frustration.

medical tourism United States

11/25/2009 11:44:45 PM #

Driving Lessons Manchester

all put together it really helps

Driving Lessons Manchester United Kingdom

2/7/2010 6:14:00 AM #


I've never been a fan of VS, but this is very helpful. Good way to work around some of the frustration.

acompanhantes France

3/10/2010 9:49:52 AM #

Hotel Bayerischer Wald

I've subscribed to your News-Feed, for another good infos. Greetz, Hotel Bayerischer Wald

Hotel Bayerischer Wald Germany

5/20/2010 12:35:44 AM #

Facebook Smiley

Everything is very open and very clear explanation of issues. It contains truly information. Your website is very useful. Ii impresses me so much!

Facebook Smiley United States

5/23/2010 5:30:39 AM #


The warning on Excel schema is one thing I found to be very useful. Thanks for securing this warning and for making it available.

savingsaccount United States

6/2/2010 9:56:51 AM #

logo design jobs

I use XSLT and XML often. It is a modern technology if you want to make your site faster.

logo design jobs Romania

7/1/2010 1:42:50 AM #

sri lanka all inclusive

Thanks for clarifying, LSN.

sri lanka all inclusive United States

7/6/2010 11:14:32 PM #

designer sunglasses

Hi Lisa,

I have (quickly) read through your blog, and I am not sure that the repeating header problem is fixed, but, I won't know for sure until I have some time to compare my XSLT to what you've shown here. I had already got the "Freeze Frame" working, and though I know I had already employed "Print_Titles", it did not cause the column headers to repeat on every page, which is, supposedly, what it is supposed to do. But, there may be something else in my XSLT that causes it not to work.

As for page header/footer, I have not processed, yet, what you've said about that.

designer sunglasses United States

8/10/2010 2:48:58 AM #

Tina Deko

The code helpe me - good work - many thanks Wink

Tina Deko Germany

9/1/2010 4:56:17 PM #

Oliver Mack

The post is really informative and very helpful! Although, the header problem seems to be still unresolved! However, with this information and with the cooperation of everyone, such task will be easily solved!
Hope to have more of this and more people willing to support and help such posts!

<a href="http://www.md5-decrypt.com/">md5 decrypt</a>

Oliver Mack United States

10/28/2010 2:36:07 PM #


is an article explains very well, good job!

RoccoSoft United States

11/10/2010 9:53:30 AM #

araba yarislari

Anything to standardize the reporting process is excellent, especially for technical people. It's the content that matters, so when the design is already taken care of then it's one less thing to consider.

araba yarislari Turkey

3/24/2012 3:35:00 AM #

Morecambe Builders

I've been an Excel user now for about 10 years and I've never seen this before. Thank you so much for putting this tutorial together.


Morecambe Builders United Kingdom

8/5/2012 1:15:07 PM #

Tina Speditionen

This post help me al lot, many thanks!

Best regards from
Tina <a href="http://www.box24.de">Speditionen</a>;

Tina Speditionen Germany

8/6/2012 5:07:30 AM #

Facebook statuses

Hi Lisa,
I will definitely try it out. If any error encounters, then I will definitely make you contact regarding this.
Thank you.

Facebook statuses India

4/14/2013 9:43:13 PM #


A very informative page. It has helped me a lot to see more clearly obout this subject. Thank you.

codeclic France

Pingbacks and trackbacks (1)+