SSRS version control of reports

Problem

Nowadays, business intelligence is widely used and is becoming more and more important to our business. The demand for new reports and various needs is rising.  Starting with the Reporting Services (SSRS) of SQL Server 2008 R2, Microsoft View has released report tools reporting Builder 3.0 to reduce the workload of IT teams. Creating complex reports prior to this must be done with bids (that is, Visual Studio, called SSDT after 2012). Reports can now be established with bids or Report Builder. Because you can use two tools to create a modified relationship. Version control of reports is increasingly difficult.

Versioning is simple when a report is created only with bids.  You can take whatever tool you like, such as TFS, SVN, or Git. But using the Builder is more complicated, this thing does not integrate any version control system.   In addition, the builder can be directly designed and saved on the server side, which makes versioning more difficult. The builder can also exist locally. But using shared datasets and data sources is a problem.

Why version control is needed

The version control system (VCS) has many benefits. The biggest benefit is that you can restore the previous version. A good version control system can also make it easy for you to view different versions of changes, Meta-data, etc. Now we have to use both the builder and the bids, so our version control is not easy.

I found a lot of information, but I didn't get it done. One solution I saw was to export RDLs to SVN. I see a solution that puts the RDL file in SVN and submits each change record for the report on the development server to the VCs. However, the solution does not match my existing environment, some of us are using bids some of them are in the reports Builder, some in the local file system, Some are on the report server.

My SSRS version control system.

Because I couldn't find a suitable solution. I'm going to build one myself to revert to the previous version.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

This is a very simple system. I realized the change record for the report on the server. I added a table to the report server database to record the version of the report. (see code below). Then I added the Insert/update trigger on the database.

Version record table code:

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 (
      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 ReportServer go ALTER TRIGGER storeversion on dbo.
         Catalog for INSERT, UPDATE as BEGIN inserts 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 are not NULL and Type!= 3  and ItemID in (SELECT c.itemid to 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 C Ontent is isn't null and V.itemid is null OR v.modifieddate!= c.modifiedd ATE)) end