Pro SQL Server 2012 Reporting Services

CHAPTER 10  MANAGING REPORTS

Figure 10-21. Daily Activity report

Because data-driven subscriptions are based on just that, data, a query to drive the subscription is

essential. It is important to design the query to have selective criteria, because SSRS delivers a copy of

the report for every record that is returned from the data source.

For our recipient list, we use the query in Listing 10-2. Essentially, the query returns all employees

who have an e-mail address and who also have scheduled activities for the day following the date of

report execution. The report is processed and delivered after hours. It is unusual in our environment

that an employee’s schedule will change after 9:00 PM, so we set up the report to execute at that time.

Listing 10-2. T-SQL Query to Return the Subscriber List

SELECT DISTINCT

EmployeeTblid, Email, HWUserLogin, ActivityDate

FROM

Employee E (NOLOCK)

JOIN Activity A (NOLOCK) ON E.EmployeeTblid = A.ProviderID

WHERE

E.Email IS NOT NULL

AND A.ActivityDate BETWEEN GETDATE() AND GETDATE() + 1

When this query is executed, the output of the query yields six rows of data, as you can see in Table

10-1, indicating that six clinicians have activities for the next day. You have many ways to format and

compare datetime values. However, in this case, using the GETDATE function to compare the current

date with the ActivityDate field value was the best choice. It was necessary to use BETWEEN with

GETDATE because the ActivityDate value defaults to 00:00:00 for the time value, whereas GETDATE

returns the current time. The comparison values wouldn’t match in a one-to-one comparison.