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.
WHERE CONVERT(varchar(max), CONVERT(varbinary(max), content)) LIKE ‘%fieldname%’
Here is a quick query to find what reports are using which data sources:
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
DS.Name IS NOT NULL
L. Dixon Galler