Information about Report subscriptions is held in ReportServer.dbo.Subscriptions table.

ExtensionSettings

XML Fragment with root node <ParameterValues>  Contains information about the format and destination of the report.

Each <ParameterValue> element has child <Name> and <Value> nodes : named according to classical EAV theory.

• Windows share parameters
  • PATH : windows share
  • FILENAME : filename (excludingExtension)
  • FILEEXTN : True or False
  • USERNAME , PASSWORD   encrypted values
  • RENDER_FORMAT : Excel, etc
  • WRITEMODE : Overwrite…
• Email parameters
  • TO | CC | BCC | ReplyTo :  email addresses
  • IncludeReport : True or False
  • RenderFormat :  HTML, Excel, etc
  • Subject : Subject of the email
  • Comment : text included in the email body

Fragment of SQL to parse useful values.

WITH S AS
(
  SELECT TOP 1000 [SubscriptionID],
  ExtensionSettings = cast(ExtensionSettings as xml)

  FROM [ReportServer].[dbo].[Subscriptions]
)

SELECT
	S.SubscriptionID,
	TransmissionMethod = CASE ExtensionSettings.value('(/ParameterValues/ParameterValue/Name)[1]', 'varchar(50)')
		WHEN 'TO' THEN 'Email'
		WHEN 'PATH' THEN 'File Share'
		ELSE 'Unknown' END,
	FileSharePath = ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "PATH"
		return data($value)'),
	FileName = ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "FILENAME"
		return data($value)'),
RenderFormat = ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "RENDER_FORMAT" or $name = "RenderFormat"
		return data($value)'),
	EmailSubject = ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "Subject"
		return data($value)')
	,ExtensionSettings
FROM S

References

• https://www.simple-talk.com/sql/database-administration/ad-hoc-xml-file-querying/