SSRS - RDL Compare

SSRS - RDL Compare

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'

  


See Also

SQL Server Reporting Services Portal
List of Award Winning TechNet Guru Articles
Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 1 (4 items)