CODEWITHSUNDEEP
ssrs-2008reporting-services
nojetlag
nojetlag

Reputation: 753

SSRS Reports Versioning

I'm looking for a way to get versioning informations out of my SSRS reports. I have several environments and would like to be able to compare which report version is deployed on these environments. In SSIS this is very easy because each SSIS package gets a new version when it was modified and safed. Is there something similiar with the reports ?

Upvotes: 12

Views: 9488

Answers (5)

Imran Sh
Imran Sh

Reputation: 1774

I tried this solution for my customers: Open the .rdl file with notepad and add a comment in the first line and save it, like:

<?xml version="1.0" encoding="utf-8"?>
<!--
=============================================
Author:             My Name (My Company)
CHANGE date:        Release Date
Description:        Short description
Current Version:    Ver x.x.x
Vx.x.x:             Version change history
=============================================
-->
<Report MustUnderstand="df" xmlns=...

I hope it helps others

Upvotes: 0

WaitForPete
WaitForPete

Reputation: 467

The Report Server dbo.Catalog table has both CreationDate and ModifiedDate.

As part of an investigation I put together a query to list out the queries in RDL files and perform a simple CHECKSUM on that to facilitate checking for drift. You can of course do the same on the whole of Content field much more simply, or do a better job of opening the XML using real xml processing.

Hope it is helpful;

DECLARE @reftextstart VARCHAR(255);
DECLARE @reftextend VARCHAR(255);

SELECT @reftextstart = '%<CommandText>%';
SELECT @reftextend = '</CommandText>';


SELECT SQ2.Path,
       SQ2.Name,
       SQ2.CreationDate,
       SQ2.ModifiedDate,
       ReportQuery,
       CHECKSUM(ReportQuery) ReportQueryChecksum
FROM
(
    SELECT SQ.Path,
           SQ.Name,
           SQ.CreationDate,
           SQ.ModifiedDate,
           CASE PATINDEX(@reftextstart, Report)
               WHEN 0 THEN
                   N''
               ELSE
                   SUBSTRING(
                                Report,
                                PATINDEX(@reftextstart, Report) + LEN(@reftextstart) - 2,
                                CHARINDEX(
                                             @reftextend,
                                             SUBSTRING(
                                                          Report,
                                                          PATINDEX(@reftextstart, Report) + LEN(@reftextstart) - 2,
                                                          1024
                                                      )
                                         ) - 1
                            )
           END ReportQuery
    FROM
    (
        SELECT TOP 1000
               [Path],
               [Name],
               [CreationDate],
               [ModifiedDate],
               [ExecutionTime],
               CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), Content)) Report
        FROM [ReportServer].[dbo].[Catalog]
        WHERE Name IN ( N'Report1', N'Report2' )
    ) SQ
) SQ2
ORDER BY [Path],
         Name;

Upvotes: 0

Martin Sentker
Martin Sentker

Reputation: 31

There still seems to be no good solution. If you create a hidden Parameter "Version" with a default value you could use a build Task to modify its value in the .rdl file, e.g. the revision part with the source control changeset number. Sadly you may need another visual Studio Project to place this build task since the reporting project type seems not capable.

You may also create inline code or an assembly that may do some lookup.

Cheap suboptimal alternative: Use the last modified date form the report database:

    Select 
    Name,
    Path,
    CreationDate, 
    ModifiedDate, 
    ModUser.UserName AS ModUser,
    CAST(catalog.parameter as xml).value(/Parameters[1]/Parameter[Name="Version"][1]/Values[1]/Value[1]','NVARCHAR(20)') as Version
   FROM Reportserver.dbo.Catalog
   INNER JOIN ReportServer.dbo.Users ModUser on Moduser.UserID = ModifiedByID
   WHERE Type = 2

and convert the date to a revision number... Doesen't help with versions across different server instances though.

Upvotes: 0

Dennis G
Dennis G

Reputation: 21788

Unfortunately there currently is no built-in functionality similar to a dll assembly version for RDL files.

The only way to get some kind of version information is to query the last modified date of the RDL file on the server via C# or VB.Net. You could do this using the ReportingServices webservice.

You could also implement a custom function which updates some field in your database to the current date each time the RDL file modified.

The problem with all the file modified information: You still don't know which version is on which server, you just know when it was uploaded/modified.

See the following pages for some more information - unfortunately no solution:

Upvotes: 10

hurleystylee
hurleystylee

Reputation: 612

In my reports, I create a variable named Version and make it a string data type (and move it to the top of the variable list). Every time I change a report, I update the Version variable based on Semantic Versioning.

Then, I can query my Report Server and look at the Parameter field of the ExecutionLog table and I can see what version was run. Technically I take care of all of this in an SSIS job that writes to another table, but that's a little outside the scope here.

Upvotes: 4

Related Questions