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 asxml),-- you need to cast to get rid of the -- yucky escaped text in the ntext column!! cast(Parameter asxml)-- ditto here fromCatalog join Users u1 onCatalog.CreatedByID = u1.UserID join Users u2 onCatalog.CreatedByID = u2.UserID for xmlpath ('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:

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.

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.