SSRS - RDL Compare

Article
1/17/2024

This article shares the information on how to compare two versions of the same report .

Sometimes we might be in a need to compare the changes in two versions of the same report.

We can observe the changes easily on the report if it is a color modification or something like this,
What if , if the change is in terms of sizing ?

So to gather the reports background information and compare the changes in the two versions of the same report ,
we are going to make use of RDL files of the report in the default location :
C:\Users\Sathya\Documents\Visual Studio 2010\Projects\Report1\Report1\Version1.rdl
C:\Users\Sathya\Documents\Visual Studio 2010\Projects\Report1\Report1\Version2.rdl

Two methods to get the XML content of the report :

1.)After the report is deployed,XML content of the report can be fetched from :

 

      SELECT content FROM ReportServer.dbo.catalog

2.)Bulk insert XML from RDL files in the default location :
C:\Users\Sathya\Documents\Visual Studio 2010\Projects\Report1\Report1\Version1.rdl
C:\Users\Sathya\Documents\Visual Studio 2010\Projects\Report1\Report1\Version2.rdl

In this example, we have followed method 2 . After getting the XML of the two versions of the report ,
using Xquery and nodes() method , we can easily share the values in the XML nodes and compare the values .

Consider I have two versions of the same report as Version1.rdl and Version2.rdl

Below are the screenshots of the two versions of same report :

Version1 and Version2 report has textbox with below expression :

 

      =    "User : "    +User!UserID+VBCRLF+    "ExecutionTime : "    +Globals!ExecutionTime

Version1 report has a table with below query as dataset :

DECLARE @Tmp TABLE (Id INT,Name VARCHAR(20)) 
 
INSERT @Tmp SELECT 1,'Sathya'
INSERT @Tmp SELECT 2,'Deepak'
 
SELECT * FROM @Tmp

Version2 report has a table with below query as dataset :

 

      DECLARE @Tmp TABLE (Id INT,Name VARCHAR(20)) 
   
      INSERT @Tmp SELECT 1,'Sathya'
      INSERT @Tmp SELECT 2,'Deepak'
   
      SELECT * FROM @Tmp 
      WHERE Name   = @Name

Difference between version1 and version2 reports :

1.)Version2 has textbox with background color .
2.)Version2 has table with background color for header and text centre aligned .
3.)Version2 has input parameter .
4.)Version2 has textbox and table inside rectangle container with background color .

Below Stored procedure can be used to get the compared resultset of two versions of the same report :

USE AdventureWorks2012 
GO 
 
   
 
CREATE PROCEDURE   RDL_Compare 
 
@RDLFileName1 NVARCHAR(256), --File path of the version1 of the RDL  
@RDLFileName2 NVARCHAR(256) --File path of the version2 of the RDL  
 
AS
 
BEGIN
 
SET NOCOUNT ON
SET XACT_ABORT ON
 
 
BEGIN TRY 
/*Bulk inserting XML from   RDL file */ 
 
   
 
   
 DECLARE @Query NVARCHAR(MAX) 
 
   
 CREATE TABLE   #Version1_Rdl(XMLData XML) 
 CREATE TABLE   #Version2_Rdl(XMLData XML) 
 
  BEGIN TRANSACTION
 
    SET @Query = 'INSERT INTO #Version1_Rdl(XMLData) 
    SELECT * FROM OPENROWSET( 
    BULK '''+@RDLFileName1+''', 
    SINGLE_BLOB) AS x;'
 
EXEC (@Query) 
 
    SET @Query = 'INSERT INTO #Version2_Rdl(XMLData) 
    SELECT * FROM OPENROWSET( 
    BULK '''+@RDLFileName2+''', 
    SINGLE_BLOB) AS x;'
 
EXEC (@Query) 
 
  
/*Bulk inserting XML from   RDL file */ 
 
   
SELECT
tmp1.ColumnHeader ColumnHeader_V1, 
tmp1.ColumnName ColumnName_V1, 
tmp1.ColumnValue ColumnValue_V1, 
tmp2.ColumnHeader ColumnHeader_V2, 
tmp2.ColumnName ColumnName_V2, 
tmp2.ColumnValue ColumnValue_V2, 
ISNULL(tmp1.ColumnHeader,tmp2.ColumnHeader)  Header1Compare, 
ISNULL(tmp1.ColumnName,tmp2.ColumnName)  Header2Compare, 
ISNULL(tmp1.ColumnValue,tmp2.ColumnValue)  ValueCompare 
FROM ( 
SELECT
 
       i.value('local-name(..)','varchar(max)') ColumnHeader, 
       i.value('local-name(.)','varchar(max)') ColumnName, 
       i.value('.','varchar(MAX)') ColumnValue 
FROM #Version1_Rdl V1 
CROSS APPLY V1.XMLData.nodes('//*[text()]') x(i) ) tmp1 
FULL OUTER  JOIN
( 
SELECT
       i.value('local-name(..)','varchar(max)') ColumnHeader, 
       i.value('local-name(.)','varchar(max)') ColumnName, 
       i.value('.','varchar(MAX)') ColumnValue 
FROM #Version2_Rdl V2 
CROSS APPLY V2.XMLData.nodes('//*[text()]') x(i) 
) tmp2  
ON tmp1.ColumnHeader = tmp2.ColumnHeader AND tmp1.ColumnName = tmp2.ColumnName 
AND tmp1.ColumnValue = tmp2.ColumnValue 
WHERE (tmp1.ColumnHeader IS  NULL  AND  tmp1.ColumnName IS   NULL  AND tmp1.ColumnValue IS   NULL)  
OR (tmp2.ColumnHeader IS  NULL  AND  tmp2.ColumnName IS   NULL  AND tmp2.ColumnValue IS   NULL)  
 
   
   COMMIT TRANSACTION
 
END TRY 
 
BEGIN CATCH 
 
DECLARE @ERROR_MESSAGE NVARCHAR(256) 
 
SELECT @ERROR_MESSAGE = ERROR_MESSAGE() 
   
IF @@TRANCOUNT > 0 
 
BEGIN
 
ROLLBACK TRANSACTION
  
END 
 
RAISERROR(@ERROR_MESSAGE,16,1) 
 
END CATCH 
 
END

Execute above created Stored procedure to get the compared resultset of two versions of the same report :

 

      USE AdventureWorks2012   
      GO   
   
      EXEC RDL_Compare 
      'C:\Users\Sathya\Documents\Visual Studio 2010\Projects\Report1\Report1\Version1.rdl'    ,   
      'C:\Users\Sathya\Documents\Visual Studio 2010\Projects\Report1\Report1\Version2.rdl'

Share via

SSRS - RDL Compare

See Also

Additional resources