Finding Parameter values for subscriptions in a SSRS report

Today I had to look at about 100 subscriptions for a SSRS report, and find if a particular region has been used as a parameter in the subscription,

I tried opening each subscription to see the parameter.  After opening a couple I thought this parameter should be saved in the database so started writing a query to find out all the values of the parameter used in all the subscirptions in the subscriptions database.

The following is the query that I came up with to find out what values were used for the parameters in the subscriptions for  a report.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

WITH [CParameters] AS ( SELECT [SubscriptionID], [Parameters] = CONVERT(XML,a.[Parameters]) FROM [Subscriptions] a ), [MySubscriptions] AS ( SELECT DISTINCT [SubscriptionID], [ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')), [ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)') FROM [CParameters] a CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p) ), [SubscriptionsAnalysis] AS ( SELECT a.[SubscriptionID], a.[ParameterName], [ParameterValue] = (SELECT STUFF(( SELECT [ParameterValue] + ', ' as [text()] FROM [MySubscriptions] WHERE [SubscriptionID] = a.[SubscriptionID] AND [ParameterName] = a.[ParameterName] FOR XML PATH('') ),1, 0, '') +'') FROM [MySubscriptions] a GROUP BY a.[SubscriptionID],a.[ParameterName] ) SELECT a.[SubscriptionID], c.[UserName] AS Owner, b.Name, b.Path, a.[Locale], a.[InactiveFlags], d.[UserName] AS Modified_by, a.[ModifiedDate], a.[Description], a.[LastStatus], a.[EventType], a.[LastRunTime], a.[DeliveryExtension], a.[Version], e.[ParameterName], LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue], SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName FROM [Subscriptions] a INNER JOIN [Catalog] AS b ON a.[Report_OID] = b.[ItemID] LEFT OUTER JOIN [Users] AS c ON a.[OwnerID] = c.[UserID] LEFT OUTER JOIN [Users] AS d ON a.MODIFIEDBYID = d.Userid LEFT OUTER JOIN [SubscriptionsAnalysis] AS e ON a.SubscriptionID = e.SubscriptionID where name like '%Report%'