I have been really gratified by the feedback people have given me about my original post showing how to change sheet names in Reporting Services-driven Excel using XSLT. Lots of people have written to say thanks, and I appreciate it.
Not much feedback about my followup post, which tried to show how you can easily integrate transformations into more scenarios, pretty much no matter what environment you work in. That's a pity — it means people still think this is harder than it is.
So I thought I would share one more thing on this subject today…
I just had occasion to use this RS-Excel trick myself, for a colleague on my EC|Wise team. FWIW I don't use XSLT mappers or anything visual when I code, it just isn't my style (no pun intended)… but I do use both XML Spy and Stylus Studio as IDEs, and they both have some pretty spiffy diagramming tools.
Note to any Fox people reading: XML Spy generated the VFP-RDL diagrams you see in the helpfile under Using VFP Report Output XML. MS re did the images but for the life of me I can't see the difference from what I turned in.
Note for anybody reading and considering a purchase: other than that particular form of diagram, in my opinion Stylus Studio wins, hands-down.
So, anyway. I decided to turn on Stylus Studio's XSLT mapper view, just for kicks. It gave me a great way to show my team, and you, how little of the original Excel SSML has to be dynamically driven by the RS data XML export. Most of the workbook you just copy directly from what your Excel example tells you it should look like.
Even the part you do have to dynamically drive isn't hard at all to do, as I hope my walkthrough showed. The particular instance I did this morning had a number of wrinkles, including two tables and a number of grouping levels to be taken care of besides the one that explicitly determines page breaks. (You can probably see that in the screen shot.) It also happens to be a situation, alluded to in my walkthrough, where you have to be careful that your dynamically-driven tab names are actually legal tab names in Excel. In this case, I used the following code to validate and change the tab names if necessary:
<xsl:template name="StripSheetName">
<xsl:param name="item"/>
<xsl:variable name="i1">
<xsl:value-of select="translate($item,'"“”','')"/>
</xsl:variable>
<xsl:value-of select="translate($i1,"/\'","")"/>
</xsl:template>
… looks a bit wierd and it may not turn out to be sufficient, but basically all I'm doing is taking out characters I know to be illegal. If I find more, I'll add more.
So this was sort of a gnarly case, with some extra work. But, even so, as I do this work, I really marvel at the cleanliness of the RS data export XML. It makes things incredibly easy to do.
You don't need to indulge in Muenchian grouping or anything weird to figure out how to handle subtotals and groups; it's all right there the way you most want it to be. I love that in a dialect.
>L<,
Hello! Long time. Glad to see you and Colin are doing well.
Paul Mrozowski pointed me to your blog from west-wind where I had asked, “Any thoughts or insight on the subject of comparing Visual Studio 2008 v XMLSpy 2008 v Stylus Studio 2008 and working with XSL?”. I have just started looking at XMLSpy and Stylus as well as VS2008.
In addition to your comment lauding Stylus Studio above would you know of any other side by side comparisons that would also include VS2008?
Thanks!
Best,
DD
PS – Say hello to Spitzer for me. I had done a little work with Craig Church some years back where he became CTO. Haven’t seen or heard from CC in years.
Hi DD,
As an XML or XSLT IDE… or as a schema editor… Visual Studio 2008 isn’t even in the same league… that’s probably why you aren’t seeing it “featured” in the professional tool comparisons!
If you’re looking for IDE integration, Altova (XML Spy integrates with both VS and Eclipse). I personally never used it that way, didn’t like the overhead.
I have used the VS editors quite a bit lately, since it is all that some of my team members have and I need to teach them using tools they have. There are some nice changes with regard to XSLT syntax checking. If you have only light or occasional needs, you can do fine. But I switch over to a machine with a “real editor” when it’s time to do “real work”.
The good news is that Visual Studio is extensible, so people have done some work making the XML editor better, you’ll easily find references to that. Here is an example:
http://donxml.com/allthingstechie/archive/2006/10/10/XPathmania-Wins-Visual-Studio-Extensibility-Plug_2D00_in-Contest-_2800_I_2700_m-Going-To-TechEd-Barcelona_2900_.aspx
Actually, now that I think about it DonXml would be a good person to answer your question… You’ll find some contact info for him on that blog. Good luck!
Thanks Lisa,
I like the ‘normal’ XML stuff in VS2008 but, as you suggested, I suspect that XMLSpy or Stylus Studio is in my future. I am will be doing a fair amount of XSL-related work so there you go.
I work with Jim Booth and he has Stylus Studio and recommends it highly, both for pricing and IDE-related resaons. At work they have a corporate license for XMLSpy so I suspect that’s what I’ll end up using for work work.
I had hoped to find more info on the whole comparison angle and was pleasantly surprised when your name popped up.
Thanks for the DonXML link.
Best,
hi lisa,
Your article is very much helpful to get the autofilter for the headers in my report when exporting from SSRS to xml data. The exported file is having .xml extension by default but I want to change the extension of the file to .xls while exporting. Can you give me some pointer?
Thanks,
Mohan
Hi Mohan,
Don’t change the resulting filename to XLS. It is not an XLS file (neither is it an XLSX file). It is a true XML file in the SSML dialect known to Excel.
If you change the extension, Excel will warn the user that they are opening a file with an incorrect format according to its extension. You don’t want to do that <s>.
>L<