Query to get list of susbscriptions with params and values

• mark 4643

  SSCommitted

  Points: 1503

  More actions

  September 12, 2012 at 6:03 pm

  #281290

  I've be searching and trying for hours to get a query that gives me all current subscriptions from and SSRS serv with parameters and their values, so that they can be recreated on a new server (after pruning)

  For example a simple report might be HoursByDepartment that takes three params:

  @From =Today - 7 days (Default)

  @To = Today (Default)

  @Dept = 2 (part of subscription)

  What I want to get is something along these lines (or something that will let me create a report)

  Report ParamName ParamValue Default

  HoursByDepartment From Today-7days True

  HoursByDepartment To Today True

  HoursByDepartment Dept 2 False

  OR

  Report Param1Name Param1Value Param1Def Param2Name Param2Value Param2Def

  HoursByDepartment From Today-7days True To Today True

  I'm pretty good with XSl, so if i could get something like, I could work with it

  <subid>

  <report>

  <ParameterValues>

  <ParameterValue>

  <Name>MinAvailable</Name>

  <Value>10000</Value>

  </ParameterValue>

  <ParameterValue>

  <Name>OwnerIDs</Name>

  <Value>0</Value>

  </ParameterValue>

  <ParameterValue>

  <Name>ShowCosts</Name>

  <Value>False</Value>

  </ParameterValue>

  <ParameterValue>

  <Name>MinValue</Name>

  <Value>0</Value>

  </ParameterValue>

  </ParameterValues>

  </report>

  </subid>

  Regards

  Mark

• Ant-Green

  SSC Guru

  Points: 113445

  More actions

  September 13, 2012 at 1:56 am

  #1537048

  This should get you to a pretty good starting point

  SELECT

  U.UserName AS SubscriptionOwner,

  C.Path AS ReportPath,

  CONVERT(XML,S.ExtensionSettings) AS SubscriptionSettings,

  S.EventType AS SubscriptionType,

  CONVERT(XML,S.Parameters) AS ReportParameters,

  S.DeliveryExtension AS DeliveryMethod,

  Sc.Name AS ScheduleName,

  Rs.ScheduleID

  FROM

  Subscriptions S

  INNER JOIN

  Users U

  ON

  S.OwnerID = U.UserID

  INNER JOIN

  Catalog C

  ON

  S.Report_OID = C.ItemID

  INNER JOIN

  ReportSchedule RS

  ON

  S.SubscriptionID = RS.SubscriptionID

  INNER JOIN

  Schedule Sc

  ON

  RS.ScheduleID = Sc.ScheduleID

  ORDER BY

  1,2