Resources For IT Professionals
Post an article
Wikis - Page Details
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  SSRS - RDL Compare

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'

, , , , [Edit tags]
Leave a Comment
  • Please add 2 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 7 Sep 2013 11:20 PM

    Maheshkumar S Tiwari edited Original. Comment: corrected typo error and minor edit

Page 1 of 1 (1 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 7 Sep 2013 11:20 PM

    Maheshkumar S Tiwari edited Original. Comment: corrected typo error and minor edit

Page 1 of 1 (1 items)