[Translation] SSRs report Version Control

Problem

Today, business intelligence is widely used and increasingly important to our business. requirements for new reports and various types of services are constantly increasing. since reporting services (SSRS) of SQL Server 2008 R2, Microsoft view launched report builder 3.0, a report tool, to reduce the workload of the IT team. before creating a complex report, you must use bids (Visual Studio, ssdt after 2012 ). now all reports can be created using bids or report builder. because you can use two tools to create a modified relationship. it is increasingly difficult to control the version of a report.

When a report is only created using bids, version control is very simple. any tool you can use. for example, TFS, SVN, or git. however, it is complicated to use report builder, which is not integrated with any version control system. in addition, report builder can be directly designed and saved on the server, which makes version control more difficult. report builder can also exist locally. however, using shared datasets and data sources is troublesome.

 

Why version control?

Version Control System (VCS) has many advantages. the biggest benefit is that the previous version can be restored. A good version control system also allows you to easily view different version changes, such as meta-data. now we need to use both report builder and bids, so our version control is not easy.

I found a lot of information, but I didn't fix it. I see a solution to export RDLS to SVN. I see a solution: place the RDL file in SVN, and submit each change record of the Report on the Development Server to VCs. however, the solution does not match my existing environment. Some of us use bids, some use report builder, some use local file systems, and some use report servers.

 

My SSRs Version Control System

Because I did not find a suitable solution, I plan to build a self-built version to reply to the previous version.

This is a very simple system. I implemented the change record for reports on the server. I added a table to the Report Server database to record the report version. (See the following code ). then I added the insert/update trigger to the database.

Version record table code:

USE ReportServergoCREATE 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]

Trigger code:

USE ReportServergoALTER TRIGGER StoreVersion ON dbo.Catalog   FOR INSERT, UPDATEASBEGIN      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 created an SSRs report to show that the version changes of each report also contain the complete XML of their RDL/RDS. to restore an earlier version, you only need to copy the XML document of the earlier version and upload it to the server. of course, it can also be used for comparison and other operations.

 

Because XML is relatively large, it is hidden by default and can be expanded as needed.

Resource:

Code

Http://www.sqlservercentral.com/articles/Reporting+Services+ (SSRS)/94119/

[Translation] SSRs report Version Control