{"id":111,"date":"2009-02-07T17:34:00","date_gmt":"2009-02-07T17:34:00","guid":{"rendered":"\/lisa\/post\/2009\/02\/07\/YAPS-Random-acts-of-kindness-regarding-Reports-Excel-XSLT-and-you.aspx"},"modified":"2009-02-07T17:34:00","modified_gmt":"2009-02-07T17:34:00","slug":"yaps-random-acts-of-kindness-regarding-reports-excel-xslt-and-you","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2009\/02\/07\/yaps-random-acts-of-kindness-regarding-reports-excel-xslt-and-you\/","title":{"rendered":"YAPS: Random acts of kindness regarding Reports, Excel, XSLT, and you"},"content":{"rendered":"

\nYou've provided some gratifying responses to my unintended continuing series<\/a> on using custom XSLT to derive better Excel  or other formats<\/a> 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.\n<\/p>\n

\nI'm going to thank Beth<\/a> 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.  \n<\/p>\n

\nWhat do you use to test the XSLT (Part 1)?<\/h4>\n

\nA 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. \n<\/p>\n

\nFor 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. \n<\/p>\n

\nI 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. \n<\/p>\n

\nThe point is, running your report to check your transformation is the long way around, especially if your report runs lots of complex queries. \n<\/p>\n

\nLet'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.\n<\/p>\n

\nHere's what I suggest you do:\n<\/p>\n

    \n
  1. Export the XML from your report without <\/em> adding a custom XSLT, so you have your raw source file.  You needed this anyway, to figure out how the file was constructed, right? \n\t<\/li>\n
  2. Open the exported XML as an XML file in Visual Studio. \n\t<\/li>\n
  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):\n
    \n\t\"Editing  <\/p><\/div>\n<\/li>\n
  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. \n<\/li>\n
  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?\n

    <\/p>\n

    \n\t\"No\n\t<\/div>\n<\/li>\n
  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.\n\t<\/li>\n<\/ol>\n

    \nWhat do you use to test the XSLT (Part Deux)?<\/h4>\n

    \nWhat 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.  <\/strong>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<\/a>. You'll find it easily if you search for "command line transformation utility" or "msxsl.exe" in the downloads section.\n<\/p>\n

    \nMSXSL 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<\/a> to do this, and the example script referred to in that post, transformRDL.vbs<\/strong>, is part of the samples that accompany RDL Documenter<\/a>.\n<\/p>\n

    \n\"MSXSL.EXE\n<\/p>\n

    \nPut some PowerShell to work on top of this, if you want to!\n<\/p>\n

    \nThe raw xml is the page content data, not the page header and footer data<\/h4>\n

    \nMost 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. \n<\/p>\n

    \nBeth 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. \n<\/p>\n

    \nFor 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.\n<\/p>\n

    \n\"Ersatz\n<\/p>\n

    \nIf you have an issue with getting something like this to work, give me a specific case, and I'll explain in more detail.\n<\/p>\n

    \nWhat can SSML (Excel 2003 XML Dialect) do? 
    \nWhat can't it do? 
    \nAnd how do you know?<\/em><\/h4>\n

    \nJust 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.\n<\/p>\n

    \nIt'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. \n<\/p>\n

    \nYou 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.\n<\/p>\n

    \nAs 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.\n<\/p>\n

    \nWhere 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.\n<\/p>\n

    \n\"Saving\n<\/p>\n

    \nWhen you press help, here's what you learn:\n<\/p>\n

    \n

       <\/p>\n
    \n

    \tXML Spreadsheet 2003 <\/p>\n

    \n<\/h4>\n

    \n\tThis XML Spreadsheet 2003 file format (.xml) does not retain the following features:\n\t<\/p>\n