Share via

SSRS - Make common change in multiple reports in one click

  • Article

This article is an outcome of this MSDN thread.

Scenario:

Say if there are more than 100 reports developed already. Each & every report has company logo on header as an embedded image. Suddenly, if the company has decided to make a minor change in the logo and all the reports need to be modified with new logo image.

Prerequisites:

  1. Change only one report manually with new logo image and save it.
  2. Create a backup folder to keep copy of original reports.

To make common change in multiple reports in one click, we need to create a Stored Procedure & SSIS package:

Stored Procedure - RDL_Img_Upd accepts two parameters
@RdlPath1 NVARCHAR(256) – RDL file path of manually modified report
@RdlPath2 NVARCHAR(256) – RDL file path of other reports that needs to be modified.

This Stored Procedure imports both report XML into temp table using OPENROWSET and necessary report tags are modified using Xquery. Inside this Stored Procedure, ImageData tag from XML of report will be replaced with ImageData tag value from XML of report that has been already manually modified. 

CREATE PROCEDURE   [dbo].[RDL_Img_Upd] 
--EXEC RDL_Img_Upd 'D:\work\Report Project2\Report Project2\Report1.rdl','D:\work\Report Project2\Report Project2\Report2.rdl'
@RdlPath1 NVARCHAR(256) ,@RdlPath2 NVARCHAR(256) 
 
AS
BEGIN
SET NOCOUNT ON
 
 DECLARE @Query NVARCHAR(MAX)
        ,@XMLDATA1  XML
        ,@XMLDATA2  XML
        ,@OldImageName NVARCHAR(MAX)
        ,@NewImageName NVARCHAR(MAX)    
 
 CREATE TABLE   #temp1(xmldata XML) 
 CREATE TABLE   #temp2(xmldata XML) 
 
 SET @Query = 'INSERT INTO #temp1(XMLData) 
    SELECT * FROM OPENROWSET( 
    BULK '''+@RdlPath1+''', 
    SINGLE_BLOB) AS x;'
 
  
  EXEC (@Query) 
 
  SET @Query = 'INSERT INTO #temp2(XMLData) 
    SELECT * FROM OPENROWSET( 
    BULK '''+@RdlPath2+''', 
    SINGLE_BLOB) AS x;'
 
  EXEC (@Query)  
 
 SELECT @XMLDATA1 = XMLData FROM #temp1
 SELECT @XMLDATA2 = XMLData FROM #temp2
 
--Change XML schema based on your installed SSRS version
 
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS  rd )
SELECT @NewImageName = CAST(@XMLDATA1.query('/Report/Page/PageHeader/ReportItems/Image/Value/text()') AS  NVARCHAR(MAX))
 
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS   rd )
SELECT @XMLDATA1 = CAST(REPLACE(REPLACE(CONVERT(NVARCHAR(MAX),@XMLDATA1.query('/Report/EmbeddedImages/EmbeddedImage[@Name=sql:variable("@NewImageName")]/ImageData')),'<p1:ImageData xmlns:p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">' ,'<ImageData>'),'</p1:ImageData>','</ImageData>') AS   XML)
 
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS  rd )
SELECT @OldImageName = CAST(@XMLDATA2.query('/Report/Page/PageHeader/ReportItems/Image/Value/text()') AS  NVARCHAR(MAX))
 
SET @XMLDATA2.modify('declare namespace N1 = "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
declare namespace N2 = "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"; 
delete /N1:Report/N1:EmbeddedImages/N1:EmbeddedImage[@Name=sql:variable("@OldImageName")]/N1:ImageData');
 
SET @XMLDATA2.modify('declare namespace N1 = "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
declare namespace N2 = "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";     
insert sql:variable("@XMLDATA1")   
after (/N1:Report/N1:EmbeddedImages/N1:EmbeddedImage[@Name=sql:variable("@OldImageName")]/N1:MIMEType)[1]'); 
 
SELECT REPLACE(CONVERT(VARCHAR(MAX),@XMLDATA2),'','')RDL
 
END
GO

To make this Stored Procedure work for all the reports, SSIS package is created that will loop through all the RDL XML files in a report solution folder, with @RdlPath1 being fixed and @RdlPath2 gets RDL filename on each iteration.

Below are the variables needed for this package:

 

 

Below are the ForEachLoop task settings:

 

Sequence container and expression on Precedence Constraint are added inside ForEachLoop task to skip manually modified RDL file during iteration:

 

Below code block has precedence constraint expression:

FINDSTRING( @[User::RdlName],"Report1",1) == 0

Using FileSystemTask, a copy of RDL file is made in another folder before manipulating original RDL file. To have a backup of your report, just in case if your RDL is corrupted and if something goes wrong.

Note: keep DelayValidation property as True for this task.

 

DataFlow task is added with OLE DB source to invoke the Stored Procedure RDL_Img_Upd and Flatfile destination to export the modified XML from Stored Procedure as new RDL file with binary value of image modified.

 

Below are the OLEDB Source Editor, DataTransformation Conversion Editor and FlatFile Destination Editor settings:

 

Using FileSystemTask, old RDL file is deleted. No worries, a copy of this is already taken in previous step.

Note: keep DelayValidation property as True for this task.

 

 

Next step is to rename the RDL file that is exported from Stored Procedure output with modified XML.

Note: keep DelayValidation property as True for this task.

 

 

  

 

 Working Example:

 I have created six sample reports with "SQL Server 2014" image in the header as shown below:

 
 Say, if the requirement is to change the header image as "SQL Server 2016" in all the reports. I have changed the header image as "SQL Server 2016" manually for Report1.rdl alone as shown below:

Now after executing the SSIS package:
   i) Except Report1.rdl other five RDL's are copied to backup folder.
  ii) Remaining reports are modified with new header image and imported into report solution folder.

Considerations:

  • By making modifications in Stored procedure (by altering the XML tag of RDL that needs change), this tool can be used to make any common change in multiple reports in one click
  • Report folder paths can be populated into a table, if the RDL files reside in different report solution folder and this table can be iterated instead of folder path.

See Also

  • TechNet Articles - SQL Server Reporting Services Portal
  • My SSRS Articles