{"id":62,"date":"2011-05-30T04:48:00","date_gmt":"2011-05-30T04:48:00","guid":{"rendered":"\/lisa\/post\/2011\/05\/30\/YAPS-on-SSRS-Documentation-the-Lite-version.aspx"},"modified":"2011-05-30T04:48:00","modified_gmt":"2011-05-30T04:48:00","slug":"yaps-on-ssrs-documentation-the-lite-version","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2011\/05\/30\/yaps-on-ssrs-documentation-the-lite-version\/","title":{"rendered":"YAPS on SSRS Documentation: the Lite version"},"content":{"rendered":"

The fully-loaded version of xmlRSDocs<\/a> is invaluable in my work, because it allows me to ferret out every last technical detail about the RDLs on an SSRS Server. <\/p>\n

However, when I just want my users to have an idea of the reports available to them in Report Manager, running xmlRSDocs is overkill.  I don’t need to do an SSIS loop through all the reports in ReportServer.dbo.Catalog, to download the RDL file and parse it.  I just want to list the available reports in some organized fashion.<\/p>\n

If the report developers in my organization took the time to use the Description when they deployed reports, this generated list would probably have everything users needed… but they don’t.<\/p>\n

What I really need to do is provide a skeletal document, maybe in Excel or Word, or in HTML suitable for a wiki page. This generated document will get filled in with descriptive content by other personnel. <\/p>\n

I can add a bit more generated content, including Description if there is one, and I can generate a formatted Parameters list, and page orientation (using dbo.Catalog’s Property column).<\/p>\n

QnD SSRS User Docs: one query, a trivial xslt<\/h2>\n

This idea turned out to be pretty easy to implement, using the following query, which translates the results into reasonably-formulated XML using the FOR XML PATH syntax in T-SQL:<\/p>\n

 
select <\/span>
   u1.<\/span>UserName As<\/span> Creator,<\/span> u2 .<\/span>UserName As<\/span> Modifier,<\/span>
   Path<\/span>,<\/span> Name,<\/span> ItemID,<\/span> ParentID,<\/span>
   Type<\/span>,<\/span> Description<\/span>,<\/span> Hidden,<\/span>
   CreationDate,<\/span> ModifiedDate,<\/span> MimeType,<\/span>
   ExecutionFlag ,<\/span> ExecutionTime,<\/span> SubType,<\/span>
   cast<\/span>(<\/span>Property as<\/span> xml<\/span>),<\/span>   — you need to cast to get rid of the <\/span>
                            — yucky escaped text in the ntext column!!<\/span>
   cast<\/span>(<\/span>Parameter as<\/span> xml<\/span>)<\/span>    — ditto here<\/span>
from<\/span><\/span> Catalog<\/span>
        join <\/span><\/span> Users u1 on<\/span> Catalog<\/span>.<\/span>CreatedByID =<\/span> u1.<\/span>UserID
        join <\/span><\/span> Users u2 <\/span>on<\/span> Catalog<\/span>.<\/span>CreatedByID =<\/span> u2.<\/span>UserID<\/span>
for <\/span><\/span> xml<\/span> path <\/span>(<\/span>‘row’<\/span>),<\/span> root<\/span> — row is actually the default, but<\/span>
                           — I’m making it explicit because<\/span>
                           — I’m using it explicitly in the xslt example.
                           — At no place in the xslt do I explicitly
                           — use the root node name, so I won’t bother
                           — specifying that one.
<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

This simple query plus a little XSLT for whatever output I want is enough to do the trick. <\/p>\n

Here’s a sample XSLT, showing you how to recursively “visit” all the reports in the server folders; here, we’re nesting <UL> elements that would match the server folder organization for our wiki page:<\/p>\n

<<\/span><\/span>?xml version=”1.0″?<\/span><\/span>><\/span>
<\/span> <<\/span><\/span>xsl:stylesheet <\/span><\/span>version=<\/span><\/span>“1.0”<\/span><\/span> xmlns:xsl=<\/span><\/span>“http:\/\/www.w3.org\/1999\/XSL\/Transform”<\/span><\/span>><\/span>
<\/span>   <<\/span><\/span>xsl:output <\/span><\/span>method=<\/span><\/span>“html”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>  
   <<\/span><\/span>xsl:template <\/span><\/span>match=<\/span><\/span>“\/”<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>!– we’re going to recursively go through the tree starting from the root node —<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>xsl:apply-templates <\/span><\/span>select=<\/span><\/span>“\/*\/row[not(ParentID)]”<\/span><\/span> mode=<\/span><\/span>“folder”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>     <<\/span><\/span>\/xsl:template<\/span><\/span>><\/span>
<\/span>
    <<\/span><\/span>xsl:template <\/span><\/span>mode=<\/span><\/span>“folder”<\/span><\/span> match=<\/span><\/span>“row”<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>xsl:param <\/span><\/span>name=<\/span><\/span>“thisFolder”<\/span><\/span> select=<\/span><\/span>“.\/ItemID”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>ul<\/span><\/span>><\/span>
<\/span>             <<\/span><\/span>li<\/span><\/span>><\/span>
<\/span>                 <<\/span><\/span>xsl:if <\/span><\/span>test=<\/span><\/span>“not(ParentID)”<\/span><\/span>><\/span><\/span>ROOT <<\/span><\/span>\/xsl:if<\/span><\/span>><\/span><\/span>Folder
            <<\/span><\/span>xsl:apply-templates <\/span><\/span>mode=<\/span><\/span>“item”<\/span><\/span> select=<\/span><\/span>“.”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>                 <<\/span><\/span>xsl:apply-templates <\/span><\/span>select=<\/span><\/span>“\/*\/row[ParentID=$thisFolder and Type=’1′]”<\/span><\/span>
               mode=<\/span><\/span>“folder”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>                 <<\/span><\/span>xsl:if <\/span><\/span>test=<\/span><\/span>“\/*\/row[ParentID=$thisFolder and Type != ‘1’]”<\/span><\/span>><\/span>
<\/span>                     <<\/span><\/span>ul<\/span><\/span>><\/span>
<\/span>                         <<\/span><\/span>xsl:apply-templates <\/span><\/span>select=<\/span><\/span>“\/*\/row[ParentID=$thisFolder and Type=’2′]”<\/span><\/span>
                     mode=<\/span><\/span>“report”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>                         <<\/span><\/span>xsl:apply-templates <\/span><\/span>select=<\/span><\/span>“\/*\/row[ParentID=$thisFolder and Type=’5′]”<\/span><\/span>
                     mode=<\/span><\/span>“datasource”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>                     <<\/span><\/span>\/ul<\/span><\/span>><\/span>
<\/span>                 <<\/span><\/span>\/xsl:if<\/span><\/span>><\/span>
<\/span>             <<\/span><\/span>\/li<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>\/ul<\/span><\/span>><\/span>
<\/span>     <<\/span><\/span>\/xsl:template<\/span><\/span>><\/span>
<\/span>
    <<\/span><\/span>xsl:template <\/span><\/span>match=<\/span><\/span>“row”<\/span><\/span> mode=<\/span><\/span>“report”<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>li<\/span><\/span>><\/span>
<\/span>             <<\/span><\/span>font <\/span><\/span>color=<\/span><\/span>“green”<\/span><\/span>><\/span><\/span>Report<<\/span><\/span>\/font<\/span><\/span>><\/span><\/span>:
            <<\/span><\/span>xsl:apply-templates <\/span><\/span>select=<\/span><\/span>“.”<\/span><\/span> mode=<\/span><\/span>“item”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>             <<\/span><\/span>xsl:apply-templates <\/span><\/span>select=<\/span><\/span>“.\/Parameters”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>             <<\/span><\/span>xsl:apply-templates <\/span><\/span>select=<\/span><\/span>“.\/Properties”<\/span><\/span>\/<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>\/li<\/span><\/span>><\/span>
<\/span>     <<\/span><\/span>\/xsl:template<\/span><\/span>><\/span>
<\/span>
    <<\/span><\/span>xsl:template <\/span><\/span>match=<\/span><\/span>“row”<\/span><\/span> mode=<\/span><\/span>“datasource”<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>!– TBD: more detail —<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>li<\/span><\/span>><<\/span><\/span>font <\/span><\/span>color=<\/span><\/span>“blue”<\/span><\/span>><\/span><\/span>Datasource<<\/span><\/span>\/font<\/span><\/span>><\/span><\/span>:
         <<\/span><\/span>xsl:apply-templates <\/span><\/span>select=<\/span><\/span>“.”<\/span><\/span> mode=<\/span><\/span>“item”<\/span><\/span>\/<\/span><\/span>><<\/span><\/span>\/li<\/span><\/span>><\/span>
<\/span>     <<\/span><\/span>\/xsl:template<\/span><\/span>><\/span>
<\/span>
    <<\/span><\/span>xsl:template <\/span><\/span>match=<\/span><\/span>“row”<\/span><\/span> mode=<\/span><\/span>“item”<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>xsl:value-of <\/span><\/span>select=<\/span><\/span>“.\/Name”<\/span><\/span>\/<\/span><\/span>><\/span><\/span>
        <<\/span><\/span>font <\/span><\/span>size=<\/span><\/span>“-1”<\/span><\/span>><\/span>
<\/span>             <<\/span><\/span>xsl:if <\/span><\/span>test=<\/span><\/span>“.\/Hidden=’1′”<\/span><\/span>><\/span>
<\/span>                 <<\/span><\/span>font <\/span><\/span>color=<\/span><\/span>“red”<\/span><\/span>><<\/span><\/span>em<\/span><\/span>><\/span><\/span> Hidden<<\/span><\/span>\/em<\/span><\/span>><<\/span><\/span>\/font<\/span><\/span>><\/span>
<\/span>             <<\/span><\/span>\/xsl:if<\/span><\/span>><\/span>
<\/span>             <<\/span><\/span>br\/<\/span><\/span>><\/span>
<\/span>          created by <<\/span><\/span>xsl:value-of <\/span><\/span>select=<\/span><\/span>“.\/Creator”<\/span><\/span>\/<\/span><\/span>><\/span><\/span> on
         <<\/span><\/span>xsl:value-of <\/span><\/span>select=<\/span><\/span>“substring-before(.\/CreationDate,’T’)”<\/span><\/span>\/<\/span><\/span>>         <\/span><<\/span><\/span>!– etc —<\/span><\/span>><\/span><\/span> <\/span>
<\/span><\/span>             <<\/span><\/span>xsl:if <\/span><\/span>test=<\/span><\/span>“string-length(normalize-space(.\/Description)) &gt; 1”<\/span><\/span>><\/span>
<\/span>                 <<\/span><\/span>br\/<\/span><\/span>><<\/span><\/span>em<\/span><\/span>><<\/span><\/span>xsl:value-of <\/span><\/span>select=<\/span><\/span>“Description”<\/span><\/span>\/<\/span><\/span>><<\/span><\/span>\/em<\/span><\/span>><\/span>
<\/span>             <<\/span><\/span>\/xsl:if<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>\/font<\/span><\/span>><\/span>
<\/span>     <<\/span><\/span>\/xsl:template<\/span><\/span>><\/span>
<\/span>
    <<\/span><\/span>xsl:template <\/span><\/span>match=<\/span><\/span>“Parameters”<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>!– TBD: more detail —<\/span><\/span>><\/span>
<\/span>     <<\/span><\/span>\/xsl:template<\/span><\/span>><\/span>
<\/span>
    <<\/span><\/span>xsl:template <\/span><\/span>match=<\/span><\/span>“Properties”<\/span><\/span>><\/span>
<\/span>         <<\/span><\/span>!– TBD: more detail —<\/span><\/span>><\/span>
<\/span>     <<\/span><\/span>\/xsl:template<\/span><\/span>><\/span>
<\/span>     
<<\/span><\/span>\/xsl:stylesheet<\/span><\/span>><\/span><\/span>  <\/p>\n

As you see, I’m only handling content of Type 1 (folder), 2 (report), and 5 (datasource) — and I may even opt to eliminate Type 5 for the user-friendly version — but obviously if you want to handle all Types, including uploaded files, there is nothing more complicated to do.<\/p>\n

Gussy it up if you want<\/h2>\n

A nice touch is a parameter to point to the Report Manager base url, so you can provide a link directly to each report in situ<\/em>.<\/p>\n

\"Sample<\/p>\n

 <\/p>\n

If you choose to do this, however, remember that not all your users have access to all your reports, so (depending on your user base) you may have some people who can read this document but experience frustration when they click the links.<\/p>\n

Making like Tina Turner<\/h2>\n

“We never, ever do anything nice and easy.”<\/em><\/p>\n

I did run into one irritating “infelicity” in SQL Management Studio while thinking this through, FWIW. <\/p>\n

It seems that Management Studio has exactly the same facility to test XSLT output of an XML file you’re editing as Visual Studio does<\/a>, so you should be able to perform the query above, open a Properties window, navigate to your XSLT, and immediately see the result.  But it doesn’t work, probably never did work, and maybe never will work<\/a>. <\/p>\n

Oh well.  If you have nothing else available, you can always apply the xslt at the command line<\/a>.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

The fully-loaded version of xmlRSDocs is invaluable in my work, because it allows me to ferret out every last technical detail about the RDLs on an SSRS Server.  However, when I just want my users to have an idea of the reports available to them in Report Manager, running xmlRSDocs is overkill.  I don’t need to do an SSISRead 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,11,12],"tags":[],"class_list":["post-62","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server","category-xml-xslt","category-xmlrsdocs","category-yaps"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/62","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=62"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/62\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=62"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=62"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=62"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}