Search code examples
reporting-servicesparameterssubscription

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

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.

enter image description here

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.

Solution

  • 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.

    enter image description here

    =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