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.
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.
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:
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:
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.
I’ve been looking for something like this for a while. Your solution is very good and saved me from hours of dredging through all of my reports manually. Cheers
Me too Mark,
I’m working on a Saturday morning onsite and the code was very slow, but it worked for me. I had success with both searching a errant data constant as well as the specific name of a stored procedure I was back-tracking.
Used this to find reports with hard coded addresses which needed to change. A really useful three lines of code!
Thanks for sharing, John! Glad you found it helpful. I sure did. 🙂
I can’t tell you how much time this has saved me! I love you random internet person for posting this!!!
Thanks Mark! Oldie, but goodie! Helped me out in a pinch!
Thanks, Joe. Glad it helped.
Thanks very much!
You are awesome!! In 2019 this still is great!!
Thanks, Arlo. Glad to hear that.