This summer I built an SSRS 2008 Report Model for a client. The end users report against the model using Report Builder 2.0.

I shall pause here for a rave.

<rave>

Report Builder 2.0 is just outstanding, in my humble opinion. I built a SQL 2005 Report Model for a different client a few years ago that is still using Report Builder 1.0 and the difference is astounding.

</rave>

We recently needed to change the data type of a field used in the model. The field in question is an integer in the source database but is essentially similar to an account number. The users preferred to have it be a string in their reports so that Report Builder 2.0 would stop insisting on trying to SUM it.

Making the change to the model was no big deal. However, I thought about what would happen to reports that were already created that may be using the field in question and how we could find those reports with minimal effort in case they would need to be updated.

Then I thought to myself, self, the reports are really just files stored in the reportserver database. You should be able to write a query against that db to return the names of any reports which utilize the field in question pretty easily.

After a quick search on the Interwebs, I found this:

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

Hazzah! The reports actually reside in the Catalog table in the Content field (Image data type). I ended up creating a script similar to the following with my search string being the name of the field as it appears in the model:

SELECT [name]

FROM reportserver.dbo.catalog

WHERE CONVERT(varchar(max), CONVERT(varbinary(max), content)) LIKE '%fieldname%'

This worked like a champ. And it is really easy to come up with other uses for querying the reportserver database. I will be continuing to play with this for sure.