//
you're reading...
BI, SQL Server, SSRS

Find All Deployed SSRS Reports That Reference A Particular String

As all too often occurs you have a need to change the data type of a field used in the data model. For example lets say that the field in question is an integer in the source database but represents an account number. The users now have a requirement to support alphanumeric account numbers.

Making the change to the model was no big deal. However, what’s the impact of the change on any dependent objects? In this case what reports could be impacted?  What would happen to any reports that were already created that may be using the field in question and how do we could identify those reports with minimal effort in case they would need to be updated.

Luckily the report meta data is stored in the ReportServer database. Now it’s a simple matter of extracting what you want. Below is a simple query the finds a string in the report meta data and could easily be enhanced to meet your specific needs.

SELECT [name]
FROM reportserver.dbo.catalog
WHERE CONVERT(varchar(max), CONVERT(varbinary(max), content)) LIKE ‘%fieldname%’

Here is a quick query to find what reports are using which data sources:

SELECT
DS.Name AS DatasourceName,
C.Name AS DependentItemName,
C.Path AS DependentItemPath
FROM ReportServer.dbo.Catalog AS C
INNER JOIN ReportServer.dbo.Users AS CU
ON C.CreatedByID = CU.UserID
INNER JOIN ReportServer.dbo.Users AS MU
ON C.ModifiedByID = MU.UserID
LEFT OUTER JOIN ReportServer.dbo.SecData AS SD
ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
INNER JOIN ReportServer.dbo.DataSource AS DS
ON C.ItemID = DS.ItemID
WHERE
DS.Name IS NOT NULL
ORDER BY
DS.Name, c.name

Reference:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/58bc9be2-cc37-4fde-8a94-de3c7a00c613/

Bonn Appetit!

L. Dixon Galler

 

 

 

 
Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: