What a difference a year, and a job change, make

by Lisa Nicholls Sun, February 27 2011 22:15

I honestly cannot believe that I started to work on xmlRSDocs in November 2009 and, only now, have gotten the whole thing squared away.

But there it is.  I did just enough work for my real-life need-at-the-time, and put it aside.

Now real life requirements have prodded me back into harness on this project, so I've spent all my free time for the past month doing a pretty thorough job on xmlRSDocs 2008.  The docs are now up-to-date, and the revamped download for xmlRSDocs contains full source, samples, and deployment bits.

A changed focus

In this version, there's no longer an RDLDocumenter Designer tool, because Custom Properties can now be dealt with through Report Builder and Report Designer standard UI. If you don't know how to, or don't know why you would want to, use Custom Properties to add documentation to a report, you can read the docs for some of my thoughts about the type of custom documentation I think RDLs ought to have. 

But don't worry about that immediately.  It turns out that what people (including me) really need and want is simply good, customizable access to the native contents of an RDL.  We simply don't have this, out-of-the-box, and we really need it.

What's in it for [you and] me

Consider this scenario: you want to make a change in a stored procedure or a view, and you want to be certain of what impact this change will have.  What do you do?

In SQL Server Management Studio, you can right-click on an object and check its dependencies.  Right?

Well, no.  You're going to get information about intra-database dependencies, sure. But SQL Server Management Studio isn't going to look any further than it can see...  and it can't see your SSRS reports.

When I joined the Santa Rosa City Schools District, I entered an environment that had literally hundreds of SSRS reports deployed in production servers, most of which had multiple datasets.  Whether the datasets were driven by embedded SQL, stored procedures, or semantic models, none of these datasets were "visible" using standard means.

Recently, I was trying to figure out the source of an occasional deadlock in a critical database, the one that serves as the source for all these datasets. I realized that I absolutely, positively had to include reporting dependencies in the "universe" of code I was looking at, to understand performance issues, resource contention, etc.

Before you say "why are reports pointed at a transactional database?" ... don't get me started.  Just take it as read: 350+ of them are, whether we like it or not.

I needed clear visibility into the statements and procedures driving those datasets.  I also decided I wanted an easy way to list the entities and objects within embedded SQL statements "buried" in RDLs, to augment the dependency lists I could get by rightclicking in SQL Management Studio.

It was already possible to do what I wanted to do using xmlRSDocs, without enhancements.  But it took a tedious and lengthy pull of all the RDLs from the server using RS Scripter, and then rearranging the scores of folders of RDLs so that they'd all be available for parsing and loading.  It became abundantly, and somewhat painfully, clear to me that there was lots more that xmlRSDocs could, and should, do to help.

So I abandoned the idea that I was ever going to update the Custom Report Item for design-time use -- which was cool to do once, but wasn't at all practical and isn't even necessary any more. I rolled up my sleeves to concentrate on creating an SSIS package that would do everything I actually needed.

 

The new package uses SOAP access to grab each RDL from a ReportServer catalog.  It processes each RDL according to the proper namespace (because these reports have been created over a very long time span, some are 2005, some are 2008, and some soon will be 2010).  As the original package did, it allows you to save the doc.XML results to disk and also to load the documentation to an "RDL Doc" database of your choice. 

As before, it offers you the change to publish or "export" your documentation by invoking an SSRS report -- using URL Access for this action -- at the end of its run.  In this delivery, I've included a bare-bones-but-usable RDL and some fancy parsing to support it, so that the "export" provides a list of all the entities and procedures on which the RDLs you've documented depend.  Of course, you can still use any RDL you choose, and do much more exciting things if you need them, you can still create any schema you like for your xmlRSDocs database, etc, but this is a pretty good base.

TestHarness got revamped with new features, too. This winForm application is now renamed xmlRSDocsHarness, because it turns out to be pretty useful on its own, not just as a teaching tool to help you understand the xmlRSDocs idea.

Enjoy. 

Now to get cracking on that dependency sleuthing...

Tags:

Reporting | SQL Server | SSIS | XML/XSLT | xmlRSDocs