How to find a custom web report that uses a specific property so thatif you need to change the property name you can find out which web reports it would affect/break. For this example, lets say you wanted to find out every single saved web report along with the username and report name that has used the filter or column “BESClient_MyProperty” custom property…
If you have spaces in your property names ( Take a look at the last line of this query below…) you’ll have to use the ASCII %20 to represent those spaces. For example if you wanted to search for a property named “My Property” the SQL search term for the last line of the query would be "Where (TextResult LIKE ‘%MY%20PROPERTY%’)
You will need to do this query below in SQL on the BESReporting DB.
Example search term: BESClient_MY_PROPERTY
SELECT ReportName,Creator,CreatorName, isPublic, ReportXML, TextResult **
** FROM **
** ( **
** SELECT ReportName,Creator,LoginName AS CreatorName, isPublic,ReportXML **
** FROM [BESReporting].[dbo].[WEBREPORTS] **
** LEFT JOIN **
** [BESReporting].[dbo].[USERS] **
** ON Creator=UserID **
** ) As Reports **
** INNER JOIN **
** ( **
** select * from **
** ( **
** SELECT **
** [ShortText], **
** FullText, **
** CONVERT(varchar(max),CAST(FullText AS varbinary(max))) AS TextResult **
** FROM [BESReporting].[dbo].[SHORTENED_TEXT] **
** ) as TempTable **
** ) AS MyShortText **
** ON Reports.ReportXML LIKE ‘%’ + MyShortText.ShortText + ‘%’ **
** Where (TextResult LIKE ‘%MY_PROPERTY%’)
Also see Jason Walkers forum post where I got this query template: Custom Report URLs