0

I am manually setting and running an SSRS report subscription. It has worked fine up until now, but I need to make a change to the report to set one of the parameters to NULL (this is a valid selection for the parameter to select "All" options of the parameter).

I cannot figure out how to pass the parameter into the XML so that it is set to NULL for the report execution. I can think of other workarounds by changing the report definition and underlying SQL queries, but was hoping to find a solution here first.

e.g. The below statement does not work as it is just an empty string.

UPDATE ReportServer.dbo.Subscriptions 
     SET parameters = '<ParameterValues><ParameterValue><Name>SomeParameter</Name><Value></Value></ParameterValue></ParameterValues>'
WHERE subscriptionid = @subscription_id
Neil Galloway
  • 141
  • 1
  • 1
  • 5
  • Try `IsNull=True` for the value. That's what I use to pass a null value to a parameter in a URL. – aduguid Apr 07 '20 at 10:38
  • I tried using `IsNull=True`, but it does not like that. The report does not send and the subscription is set to disabled with the result of "The subscription contains parameter values that are not valid.". – Neil Galloway Apr 07 '20 at 19:23

1 Answers1

0

Found it out finally. I just needed to exclude the <Value></Value> section entirely from the XML and it treats that as NULL.

UPDATE ReportServer.dbo.Subscriptions SET parameters = '<ParameterValues><ParameterValue><Name>SomeParameter</Name></ParameterValue></ParameterValues>' WHERE subscriptionid = @subscription_id

I discovered this by manipulating the subscription to look the way I wanted in the user interface and then examining the XML from the database.

Neil Galloway
  • 141
  • 1
  • 1
  • 5