Thursday, 7 April 2016

FIND OBJECTS BEING REFERRED BY SSRS REPORT

Hi Everyone

In the daily team meeting today, we had a requirement to find out the stored procedure used/referenced by SSRS reports. My Team Lead suggest why cant we write a sql to search the xml of the reports.

Upon googling I came across this beautiful article by :-

Suggest everyone to go through this to understand the code.

Here is the snipped which would give list of sp's that are used inside a report :-


Declare @ReportName VARCHAR(100)
Set @ReportName = 'QuarterlySalesPerformance'

;WITH XMLNAMESPACES
( DEFAULT
, 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition )
SELECT 
distinct
xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(2500)') AS CommandText

FROM ( 
      SELECT C.Name
      ,c.Path
      ,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
      FROM  dbo.Catalog C
      WHERE  C.Content is not null
      AND  C.Type = 2
      ) CATDATA
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable ( xmlcolumn )
WHERE
CATDATA.Name = @ReportName
and xmlcolumn.value('(Query/CommandType)[1]','VARCHAR(2500)') ='StoredProcedure'

1 comment:

  1. This really has covered a great insight on MSBI . I found myself lucky to visit your page and came across this insightful read on MSBI tutorial. Please allow me to share similar work on MSBI training course :-

    https://www.youtube.com/watch?v=tFG-VkaSvhI

    and regarding the Certification Process on Bi instruction please check on the Link:-

    https://www.youtube.com/watch?v=yf__UkGxQ8c

    and for further Basics concepts on MSBI course with the pictorial Representation please check on this Links as well:-

    https://www.youtube.com/watch?v=OzmdY0zCw4g
    https://www.youtube.com/watch?v=EdF9tZliIok

    ReplyDelete

SSIS Issues : A Day of Learning

Seldom are the days when you run into complex issues but resolve them in the shortest interval of time. Thanks to Larry Page and my fello...