CODEWITHSUNDEEP
reporting-servicesssrs-2016
stecyk
stecyk

Reputation: 59

SSRS 2016 Report Emails across time zones

I'm using SSRS 2016 and have a number of users that will need to receive an emailed report by 11am respective to the users local time.

What's the best practice for doing this?

My initial thought was to just create different Active Directory distribution lists for each time zone and add the users accordingly...then create multiple schedules for each report and adjust the time of the report send time relative to the when the reports were needs for distribution list.

As an example, the SQL server is on central time while Eastern and Central time employees need a report by 11am local time. - Create an email schedule to send report to eastern time employees at 10am (server time) and again at 11am for the central time employees.

Is there a better way to do this?

Upvotes: 1

Views: 418

Answers (1)

gaccardo
gaccardo

Reputation: 395

Your approach is exactly what we are doing. The SQL Server instance is in the Central time zone, and we have users around the world. Each group maintains a mailing list, and manually does arithmetic when creating a report subscription. Interestingly enough, SSRS adjusts to local user's language (Le rapport hebdomadaire @ReportName a été exécuté à @ExecutionTime instead of @ReportName was executed at @ExecutionTime for the default subject), but offers nothing to help with time zones.

If you have an enterprise edition of SQL Server, you could use data driven report subscriptions. Given the big increase in cost between standard and enterprise editions, I doubt this one feature justifies the cost.

The other solution requires things outside of SSRS, but it can be handy. If you have other scheduling tools running in the various locations, they could run a PowerShell script to trigger the subscription. You could also use this approach to run reports when data is loaded, for example.

PowerShell script to list subscriptions and their IDs:

$server = 'http://server.company.com/reportserver'
$site = '/'

$rs2010 = New-WebServiceProxy -Uri "$server/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential
$subscriptions = $rs2010.ListSubscriptions($site)
$subscriptions | Sort-Object -property path,owner,SubscriptionID -Unique | select Path, Owner, SubscriptionID

Powershell script to trigger subscription:

# Given an SSRS URL and a subscription ID, trigger the subscription.
$server = 'http://server.company.com/reportserver'
$subscriptionid = "55477447-b0b0-433c-a75f-08ad0b6d18bd"

$rs2010 = New-WebServiceProxy -Uri "$server/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;
$rs2010.FireEvent("TimedSubscription",$subscriptionid,$null)

Upvotes: 0

Related Questions