Send RS report to every single email within SQL query

Question

Using SQL Services 2005. I have a report in Microsoft Visual Studio 2005: Projects.rdl

In the dataset I have the following query: (Showing me the previous month, where projects are overdue)

SELECT [Date], [Project], [Project Owner], [Email]
FROM [Table1]
WHERE [Date] < (SELECT DATEADD(MONTH,-1,GETDATE())

Now I want to send a RS Report to each [Project Owner], without having to create a report for each user with their specific filter.

That is I want to avoid doing the following for every report sent to each user:

SELECT [Date], [Project], [Project Owner], [Email]
FROM [Table1]
WHERE [Date] < (SELECT DATEADD(MONTH,-1,GETDATE())
AND [Project Owner] = 'AceAlfred'

Does Data-Drive-Subscription provide this feature??

UPDATE:

SELECT [Date], [Project], [Project Owner], [Email]
FROM [Table1]
WHERE [Date] < (SELECT DATEADD(MONTH,-1,GETDATE())
AND [Email] = @email

Error message: The report parameter ‘email’ has a DefaultValue or a ValidValue that depends on the report parameter “email”. Forward dependencies are not valid. (rsInvalidReportParameterDependency)

dcarneiro dcarneiro · Accepted Answer · 2012-03-14T10:24:29

I think that what you want is to send the project owner as a parameter.

On the menu of your report project go to Report > ReportParameters and add a new parameter called @projectOwner

Then your query will look like

SELECT [Date], [Project], [Project Owner], [Email]
FROM [Table1]
WHERE [Date] < (SELECT DATEADD(MONTH,-1,GETDATE())
AND [Project Owner] = @projectOwner

Send RS report to every single email within SQL query

1 Answers