Version Control for SSRS

I absolutely LOVE this functionality, I wish that Microsoft provided this as a built-in option!

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.  (registration required)


Tautological expressions

Tautological expressions

The word tautology was used by the ancient Greeks to describe a statement that was true in every possible interpretation merely by virtue of saying the same thing twice. For instance, “tables are tables” is a tautological statement.

Tautological expressions are always true, so that they can safely be short-circuited without changing the meaning of the overall statement. For instance, filtering with the predicate “1 = 1” will return all rows, without evaluating the condition at all.

Consider the following query:

 FROM #Test
 WHERE 1 = 1 AND a= 1

Execution plan with clustered index seek details


As you can see from the execution plan, the seek predicate does not contain “1 = 1”, as it was stripped away by contradiction detection.

When will reporting services support c#?

I still use vb on a daily basis.. Primarily because there are three or four places in SQL server that don’t support c#.

When will Microsoft provide functionality that we can write functions for reporting services in c#?

Twelve years ago we had one language for asp, dhtml, office, SQL server and batch files.

How many languages do we need today to do the same thing?

TSQL – how to send try catch errors through sp_send_dbmail

I had a bit of a problem trying to get the results from an error into an email. Had to share my experience.. Please let me know if you know of a better / easier method.

Finally figured out that I had to push the error values in as constants.

SET @Query = ‘SELECT ‘ + CHAR(39) + REPLACE(CONVERT(VARCHAR(1000), ERROR_NUMBER()), CHAR(39), ”) + CHAR(39) + ‘ AS ErrorNumber
,’ + CHAR(39) + REPLACE(CONVERT(VARCHAR(1000), ERROR_SEVERITY()), CHAR(39), ”) + CHAR(39) + ‘ AS ErrorSeverity
,’ + CHAR(39) + REPLACE(CONVERT(VARCHAR(1000), ERROR_STATE()), CHAR(39), ”) + CHAR(39) + ‘ AS ErrorState
,’ + CHAR(39) + REPLACE(CONVERT(VARCHAR(1000), ERROR_PROCEDURE()), CHAR(39), ”) + CHAR(39) + ‘ AS ErrorProcedure
,’ + CHAR(39) + REPLACE(CONVERT(VARCHAR(1000), ERROR_LINE()), CHAR(39), ”) + CHAR(39) + ‘ AS ErrorLine
,’ + CHAR(39) + REPLACE(CONVERT(VARCHAR(1000), ERROR_MESSAGE()), CHAR(39), ”) + CHAR(39) + ‘ AS ErrorMessage;’

then you can just push the results in via the @query parameter

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘_________________________________’
,@recipients = ‘_________________________________’
,@subject = ‘_________________________________’
,@body = ‘____________________________’
,@body_format = ‘TEXT’
,@importance = ‘HIGH’
,@query = @Query

Data Warehouse Developer | Gorge Networks – Jun 2013 – Present

SQL Server Analysis Services / Cubes / Olap Dimensional Modeling Operational Data Store Datamart design / development

Data Warehouse Developer | Gorge Networks – Jun 2013 – Present was originally published on

  • SQL Server Analysis Services / Cubes / Olap
  • Dimensional Modeling
  • Operational Data Store
  • Datamart design / development

Data Warehouse Developer | Gorge Networks – Jun 2013 – Present was originally published on