{"id":135,"date":"2008-03-22T15:29:00","date_gmt":"2008-03-22T15:29:00","guid":{"rendered":"\/lisa\/post\/2008\/03\/22\/Another-postscript-to-QnD-Excel-in-RS-post.aspx"},"modified":"2008-03-22T15:29:00","modified_gmt":"2008-03-22T15:29:00","slug":"another-postscript-to-qnd-excel-in-rs-post","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2008\/03\/22\/another-postscript-to-qnd-excel-in-rs-post\/","title":{"rendered":"Another postscript to QnD Excel-in-RS post"},"content":{"rendered":"

\nI 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<\/a>. Lots of people have written to say thanks, and I appreciate it.\n<\/p>\n

\nNot much feedback about my followup post<\/a>, 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.\n<\/p>\n

\nSo I thought I would share one more thing on this subject today…\n<\/p>\n

\nI just had occasion to use this RS-Excel trick myself, for a colleague on my EC<\/span>|<\/span><\/strong>Wise <\/strong><\/span><\/span>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<\/a> and Stylus Studio<\/a> as IDEs, and they both have some pretty spiffy diagramming tools. \n<\/p>\n

\nNote to any Fox people reading: XML Spy generated the VFP-RDL diagrams you see in the helpfile under Using VFP Report Output XML<\/em>. MS re did the images but for the life of me I can't see the difference from what I turned in. <\/p>\n

Note for anybody reading and considering a purchase: other than that particular form of diagram, in my opinion Stylus Studio wins, hands-down.\n<\/p>\n

\nSo, 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<\/em> 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.\n<\/p>\n

\n\"Stylus\n<\/div>\n

\nEven 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<\/em> tab names in Excel.  In this case, I used the following code to validate and change the tab names if necessary:\n<\/p>\n

\n<<\/span>xsl:template <\/span>name=<\/span>"StripSheetName"<\/span>>
\n<\/span>      <<\/span>xsl:param <\/span>name=<\/span>"item"<\/span>\/<\/span>>
\n<\/span>      <<\/span>xsl:variable <\/span>name=<\/span>"i1"<\/span>>
\n<\/span>         <<\/span>xsl:value-of <\/span>select=<\/span>"translate($item,'&quot;“”','')"<\/span>\/<\/span>>
\n<\/span>      <<\/span>\/xsl:variable<\/span>>
\n<\/span>      <<\/span>xsl:value-of <\/span>select=<\/span>"translate($i1,&quot;\/\\'&quot;,&quot;&quot;)"<\/span>\/<\/span>>
\n<\/span><<\/span>\/xsl:template<\/span>><\/span>\n<\/p>\n

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

\nSo 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<\/strong>.  It makes things incredibly easy to do. \n<\/p>\n

\nYou 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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 easilyRead more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6,10],"tags":[],"class_list":["post-135","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server","category-xml-xslt"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=135"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/135\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}