My team had created many properties since years and also had created many web reports showing those properties. Currently we need to get rid of lots of properties but we are not sure which web reports are using those properties. There are many scheduled activities as well to send emails with web reports.
I am wondering if there is anything from “Session relevance tester” to find which web reports uses particular property. Or is there any other way to find all webreports using one property in question? We have almost 20 properties to look for within 200+ web reports.
There’s currently no functionality to tell who is using what property value etc so unless you exported your reports and looked through the report itself it would be difficult to do.
Oh… It will be time consuming as I came to know that other department people have created almost 400 extra web reports with various inter linked departments
I will ask team to export all reports of all users and then check for those columns… definitely not good as I thought before.
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
