One of my friends Company needs analysis on subscribed reports & their parameters & values using ReportServer data of Reporting Services 2005/2008.
Subscriptions (table) let you create subscriptions to the report. Subscriptions allow you to set up periodic delivery of reports to end users by e-mail or file share.
Subscriptions table has [Parameters] column type of [ntext] and actually (as per best of my knowledge), it stores XML formatted data.
There may be multiple Subscribed Reports, having multiple parameters, and each parameter may have single/multiple values.
So there was need to retrieve each Subscribed Report, with detail information including report Name, Owner, LastRunTime, Parameters & their value(s) are subscribed etc.?
This is easy enough to do with cursors & temp variable/table approach but I was looking solution in simple way, and found it easier using CROSS APPLY, STUFF with FOR XML PATH, and simple CTE, please see script:
