I absolutely LOVE this functionality, I wish that Microsoft provided this as a built-in option!
Reporting and business intelligence have become both vital to, and distributed throughout, our businesses. The demand for new and different reports continues to climb rapidly. With SQL Server Reporting Services (SSRS) starting in version 2008R2, Microsoft attempted to ease the load on IT departments by including a capable end-user reporting tool, Report Builder 3.0. Previously, the only way to create sophisticated reports in SSRS was via BIDS (the BI development studio, a version of Visual Studio). Now, reports can be created equally easily using BIDS or Report Builder. The challenge created by having two tools in use for report creation and modification is that it makes version control very difficult.
When reports were being created exclusively in BIDS, version control was simple. You could use any number of source control systems that integrate with Visual Studio such as TFS, SVN, or Git. Report Builder complicates the issue on two fronts, the first is that unlike BIDS, there are no source control systems that integrate with it, as it is a one-click-download product. The second challenge is that, by default, Report Builder is designed to work with report files directly on the server itself. This is a benefit for rapid report creation and deployment, but it makes source control that much more difficult. Report Builder can be used with local files, but that creates other problems as it makes working with shared datasets and data sources much trickier.
Reasons for Version Control
In a full featured version control system (VCS) there are many benefits. The primary benefit is the ability to easily roll back changes that have been made to an earlier state. A good VCS will also enable you to easily see what has changed from one version to the next, store meta-data about changes, and many other things. My personal concern was that as we moved from using BIDS exclusively to using Report Builder together with BIDS, we were losing our ability to roll back to earlier, known good, versions of our reports.
I searched all over the Internet for a single solution that fit our situation and didn’t find it. I saw solutions that exported RDLs into SVN and solutions that relied on using the step of reports being promoted from a development server to get them committed to a VCS. None of those matched our environment, where some reports were being developed in BIDS and others in Report Builder, some on local file systems and some directly on the report server. We needed to allow analysts the ability to use Report Builder directly on the server to allow them to use shared data sources that they might not have security to create themselves.
My SSRS version system
Failing to find any solution created by anyone else, I decided to build a system that would cover our basic need, the ability to revert to an earlier version. I don’t pretend that what I have built is a complete VCS, but it does cover the minimum functionality and has been very well received by our analysts and reporting team.
Conceptually, this is a very simple system. I realized that the only place where report changes were all visible was at the server. I added a table to the report server database to store versions of report objects (see code below). I then created an insert/update trigger on the database that ensured that any time a report was added or changed in the database, the new version was stored as well. This basically ensures that no matter what tool is used, when a report is added to or changed on the server, I have that logged.
http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/94119/ (registration required)