May 2011 – TechSpoken

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 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.

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.

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.

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).

QnD SSRS User Docs: one query, a trivial xslt

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:

select
   u1.UserName As Creator, u2 .UserName As Modifier,
   Path, Name, ItemID, ParentID,
   Type, Description, Hidden,
   CreationDate, ModifiedDate, MimeType,
   ExecutionFlag , ExecutionTime, SubType,
   cast(Property as xml), — you need to cast to get rid of the
                            — yucky escaped text in the ntext column!!
   cast(Parameter as xml)    — ditto here
from Catalog
        join Users u1 on Catalog.CreatedByID = u1.UserID
        join Users u2 on Catalog.CreatedByID = u2.UserID
for xml path (‘row’), root — row is actually the default, but
                           — I’m making it explicit because
                           — 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.

This simple query plus a little XSLT for whatever output I want is enough to do the trick.

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:

<?xml version=”1.0″?>
<xsl:stylesheet version=“1.0” xmlns:xsl=“http://www.w3.org/1999/XSL/Transform”>
 <xsl:output method=“html”/>

 <xsl:template match=“/”>
 <!– we’re going to recursively go through the tree starting from the root node —>
 <xsl:apply-templates select=“/*/row[not(ParentID)]” mode=“folder”/>
 </xsl:template>

 <xsl:template mode=“folder” match=“row”>
 <xsl:param name=“thisFolder” select=“./ItemID”/>
 <ul>
 <li>
 <xsl:if test=“not(ParentID)”>ROOT </xsl:if>Folder
 <xsl:apply-templates mode=“item” select=“.”/>
 <xsl:apply-templates select=“/*/row[ParentID=$thisFolder and Type=’1′]”
 mode=“folder”/>
 <xsl:if test=“/*/row[ParentID=$thisFolder and Type != ‘1’]”>
 <ul>
 <xsl:apply-templates select=“/*/row[ParentID=$thisFolder and Type=’2′]”
 mode=“report”/>
 <xsl:apply-templates select=“/*/row[ParentID=$thisFolder and Type=’5′]”
 mode=“datasource”/>
 </ul>
 </xsl:if>
 </li>
 </ul>
 </xsl:template>

 <xsl:template match=“row” mode=“report”>
 <li>
 Report:
 <xsl:apply-templates select=“.” mode=“item”/>
 <xsl:apply-templates select=“./Parameters”/>
 <xsl:apply-templates select=“./Properties”/>
 </li>
 </xsl:template>

 <xsl:template match=“row” mode=“datasource”>
 <!– TBD: more detail —>
 <li>Datasource:
 <xsl:apply-templates select=“.” mode=“item”/></li>
 </xsl:template>

 <xsl:template match=“row” mode=“item”>
 <xsl:value-of select=“./Name”/>
 
 <xsl:if test=“./Hidden=’1′”>
 Hidden
 </xsl:if>
 
 created by <xsl:value-of select=“./Creator”/> on
 <xsl:value-of select=“substring-before(./CreationDate,’T’)”/> <!– etc —>
 <xsl:if test=“string-length(normalize-space(./Description)) > 1”>
 <xsl:value-of select=“Description”/>
 </xsl:if>
 
 </xsl:template>

 <xsl:template match=“Parameters”>
 <!– TBD: more detail —>
 </xsl:template>

 <xsl:template match=“Properties”>
 <!– TBD: more detail —>
 </xsl:template>

</xsl:stylesheet>

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.

Gussy it up if you want

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.

Sample xslt output

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.

Making like Tina Turner

“We never, ever do anything nice and easy.”

I did run into one irritating “infelicity” in SQL Management Studio while thinking this through, FWIW.

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, 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.

Oh well. If you have nothing else available, you can always apply the xslt at the command line.

Month: May 2011

YAPS on SSRS Documentation: the Lite version

QnD SSRS User Docs: one query, a trivial xslt

Gussy it up if you want

Making like Tina Turner