1

I have an SSRS (2016) report that accepts a date parameter. The user selects a date and the report runs and returns one page per customer. The report also has a data driven subscription which when triggered, emails the output relevant to the customer.

My current approach is to have a text box that only some users can see and clicking the text box triggers the subscription . I do this by :

Inserting the parameter selected by the user into a table when the user clicks "View Report" button and then use the following to trigger the subscription:

EXEC msdb.dbo.sp_start_job @job_name ='job name to be sent out'

The data set for the subscription/ report then uses the parameter inserted into the table to do it's thing. This all works fine.

The problem arises when more than one person runs the report.. the second persons trigger can potentially over write the first persons parameter insert resulting in the wrong set of data being sent out..

My questions is.. is there a better way to trigger data driven subscriptions where the report needs parameter values - but the parameter value cannot be predetermined?

If my approach is acceptable, how can I eliminate the overwriting of parameter values?

Better still, how can I determine the correct parameter and pass it to the subscription? e.g. session id / user id.. etc.,

Any ideas or alternate implementations are welcome..

Harry
  • 2,636
  • 1
  • 17
  • 29

1 Answers1

0

I've run into the same issue before, where a default table value is overwritten during processing from another user. The way I resolved it was adding a column to the default table for the HOST_ID or HOST_NAME. It's a bit of a pain because then you have pass that parameter to the report as well.

 SELECT HOST_ID(), HOST_NAME()

OR

In a report parameter you can use the following expression default value to get the current user.

 =Right(User!UserID, Len(User!UserID)-instr(User!UserID, "\"))
  • Note: Your data-driven subscription will fail if you set this as a report variable. As a parameter, it will work.
aduguid
  • 3,099
  • 6
  • 18
  • 37
  • Sorry.. go side tracked..good suggestion, but I've already tried this.. it works fine locally when developing the report.. once deployed.. it gets the reportserver details and it will be the same for everyone who runs the report. – Harry Feb 26 '18 at 23:31
  • Any luck with this? – aduguid Mar 08 '18 at 04:00
  • Thanks for the followup.. but I got lucky and the report is only required by one user for any given day.. I simply truncate the control table for each run.. insert the new data and then use that for the subscription.. data driven subscriptions don't allow for User!UserID anyway – Harry Mar 08 '18 at 19:51