Is there a way to retain the email subscription parameter values for a SSRS report for the report execution day?

Question

I'm using the standard version of SSRS 2016, so no access to data-driven subscriptions. I have a report subscription that is run daily utilizing a fixed StartDate parameter and the default value of an EndDate parameter that uses an expression to set its value to today's date.

I need for the subscribers to reuse the links after the report schedule date has past, but the link in the subscription email only includes the first parameter value, so the EndDate always uses today's date, not the date the subscription email was generated.

I'm wanting to retain the parameter values of the day the report was run. Is there a way to force the inclusion of the parameter values in the email subscription link?

Any ideas?

Thanks in advance.

aduguid · Accepted Answer · 2022-03-30T05:19:06.483

You could create a SQL Server job to send a custom email with your report link.

DECLARE @report_url NVARCHAR(500) = ''; --update the report url here
DECLARE @email_body NVARCHAR(MAX) = NULL;

BEGIN
   SET @email_body = '<html><body>Hi,   
   <br><br>
   You can access the report in the link below.
   <br>
   <a href="' + @report_url + '">Your Report</a>
   <br>
   <br>
   </body></html>';

   EXEC [msdb].[dbo].[sp_send_dbmail] 
      @profile_name = 'YourProfileNameHere', 
      @body = @email_body, 
      @body_format = 'HTML', 
      @recipients = 'first.last@yourcompany.com; first.last@yourcompany.com', 
      @blind_copy_recipients = 'first.last@yourcompany.com', 
      @subject = 'Report Email Subject', 
      @query_result_no_padding = 1;
   WAITFOR DELAY '00:00:02';
END;

You can build the report url in the footer of the report with parameters.

=Globals!ReportServerUrl + "/ReportServer?"
+ Replace(Globals!ReportFolder, " ", "+") + "%2f"
+ Replace(Globals!ReportName, " ", "+")
+ "&ReportFolder=" + Join(Parameters!ReportFolder.Value, "&ReportFolder=")
+ "&SearchType=" + Join(Parameters!SearchType.Value, "&SearchType=")
+ IIf(IsNothing(Parameters!SearchFor.Value), "&SearchFor:IsNull=True", "&SearchFor=" + Parameters!SearchFor.Value)
+ IIf(IsNothing(Parameters!CreatedDateFrom.Value), "&CreatedDateFrom:IsNull=True", "&CreatedDateFrom=" + Format(Parameters!CreatedDateFrom.Value, Variables!FormatDate.Value))
+ IIf(IsNothing(Parameters!CreatedDateTo.Value), "&CreatedDateTo:IsNull=True", "&CreatedDateTo=" + Format(Parameters!CreatedDateTo.Value, Variables!FormatDate.Value))
+ IIf(IsNothing(Parameters!ModifiedDateFrom.Value), "&ModifiedDate:IsNull=True", "&ModifiedDateFrom=" + Format(Parameters!ModifiedDateFrom.Value, Variables!FormatDate.Value))
+ IIf(IsNothing(Parameters!ModifiedDateTo.Value), "&ModifiedDateTo:IsNull=True", "&ModifiedDateTo=" + Format(Parameters!ModifiedDateTo.Value, Variables!FormatDate.Value))
+ "&CreatedBy=" + Join(Parameters!CreatedBy.Value, "&CreatedBy=")
+ "&ModifiedBy=" + Join(Parameters!ModifiedBy.Value, "&ModifiedBy=")
+ "&ShowSql=" + CStr(Parameters!ShowSql.Value)

To use Null as a parameter value use: [Parameter Name]:IsNull=True
To use multi value parameters, you must use the Join function
To use boolean values, you must change it to a string e.g. CStr or .ToString()

Name	Description
rs:Format	Rendering modes you can pass are HTML3.2, HTML4.0, MHTML, IMAGE, EXCEL, WORD, CSV, PDF, XML, defaults to HTML4.0
rc:Zoom	Specified in percentages, supports Page%20Width and Whole%20Page, defaults to 100%
rc:Toolbar	True/False, used to show/hide the toolbar, defaults to true
rc:Parameters	True/False/Collapsed, used to show/hide/collapse the parameters in the toolbar, defaults to true
rc:DocMap	True/False, used to show/hide document map, defaults to true (not shown unless report has document map)
rc:Section	Specifies default page number to display, defaults to 1
rc:BookMarkID	Jumps to a specific bookmark in a report
rc:FindString	Provides search criteria to the report and finds the first instance of the string specified

Thanks @aduguid, I'm getting an error at the: Join(Parameters!ReportFolder.Value, "&ReportFolder=") How do I get the report URL into the subscription email with the static report parameter values? — andyz, Mar 28 '22 at 23:39
Single value parameter: `"&ReportFolder=" + Parameters!ReportFolder.Value` — aduguid, Mar 29 '22 at 00:07
I missed a key point in my question - I cannot include the report in the email due to privacy and am using the "Include Link" option on the subscription to include the link to the report. I'm trying to get the default parameter into _this_ link. — andyz, Mar 29 '22 at 15:25
Since you can't edit the link in the regular subscription, you could do it in a SQL Server job. I've updated my answer. — aduguid, Mar 30 '22 at 05:18

Is there a way to retain the email subscription parameter values for a SSRS report for the report execution day?

1 Answers1