The Problem

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.

The code for the Version Store Table:

USE ReportServer
go
CREATE TABLE [dbo].[VersionStore](
      [VersionItemId] [bigint] IDENTITY(1,1) NOT NULL,
      [ItemId] [uniqueidentifier] NOT NULL,
      [Name] [nvarchar](425) NOT NULL,
      [ModifiedDate] [datetime] NOT NULL,
      [ModifiedBy] nvarchar(50),
      [InsertedDate] [datetime] NOT NULL,
      [Def] [xml] NULL,
 CONSTRAINT [PK_VersionStore] PRIMARY KEY CLUSTERED
(
      [VersionItemId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The trigger code to populate the table:

USE ReportServer
go
ALTER TRIGGER StoreVersion ON dbo.Catalog
   FOR INSERT, UPDATE
AS
BEGIN
      INSERT INTO ReportServer.dbo.VersionStore
         ( ItemId
         ,Name
         ,ModifiedDate
         ,ModifiedBy
         ,InsertedDate
         ,Def
         )
         SELECT ItemId
              ,[Name]
              ,ModifiedDate
              ,u.UserName ModifiedBy
              ,GETDATE() InsertedDate
              ,CONVERT(XML, CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CONTENT))) Def
            FROM Catalog C
               INNER JOIN Users U
               ON u.UserID = c.ModifiedByID
            WHERE Content IS NOT NULL AND Type != 3
               AND ItemID IN (
               SELECT c.ItemID
                  FROM Catalog C
                     LEFT OUTER JOIN ( SELECT v1.ItemID
                                            ,MAX(v1.modifiedDate) modifiedDate
                                          FROM VersionStore V1
                                          GROUP BY ItemID
                                     ) V
                     ON C.ItemID = v.ItemID
                  WHERE Content IS NOT NULL
                     AND ( v.itemID IS NULL
                           OR v.modifiedDate != c.ModifiedDate
                         ) )
END

Finally, I built an SSRS report that displays the various versions of each report and contains the full XML of the RDL, or RDS. If a report needs to be reverted to an earlier version, the user takes the XML of the previous version, puts it in a file and uploads it to the server. They can do this with the same name of the problematic report, overwriting it, or can give it a different name so that the two reports can both sit on the server side by side to compare.

The report is normally collapsed at the folder level and then can be expanded to show any given report’s history.

Because the XML for each report is so large, it is hidden unless it is needed for someone to restore a report.

We have had this system in place for over a year and a half now and it has worked quietly without impacting performance. Multiple times I have been told by various report writers how the system saved them significant time and effort, both for reports that were posted with errors and for reports that during modification had become corrupted. The ability to quickly and fairly painlessly roll back to an earlier version of a report has worked very well.

Conclusion and Caveats

I am aware that Microsoft recommends doing nothing to the back end of Reporting Services, but in this case the addition of one table and one trigger seems a pretty minor change for the added utility gained. Also, this system doesn’t attempt to modify any of the Report Server tables directly. I also know that this isn’t a true VCS. It doesn’t provide many of the features of a modern VCS, but it does provide the most crucial feature we needed, which was the ability to track and retain report versions. All the code and reports for this are included in a zip file below.

Any comments or suggestions for improvement are always welcome.

David K. Bennett