SQLTeam.com | Weblogs | Forums

SSRS report sending to multiple recepients based on where clause


#1

i have a SSRS report which contains many different names and i would like to send the report filtered on a name to that specif person.

for example:

create table Test (
[Text1] varchar(255),
[name]varchar(255),
[Text2]varchar(255),
email varchar(255))

insert into Test
values
('aaaaa','Kam','bbbbbb','kam@test.com'),
('aaa','Kam','cccc','kam@test.com'),
('kkkkk','Becky','ddddd','Becky@test.com'),
('uuuu','Lisa','uuu','Lisa@test.com')

select * from Test

--email kam
select
Text1
,name
,Text2
from Test
where name = 'kam'

--email Becky
select
Text1
,name
,Text2
from Test
where name = 'Becky'

--email Lisa
select
Text1
,name
,Text2
from Test
where name = 'Lisa'


#2

I have like 140 recipients to email and the where clause in SSRS report uses a parameter instead of hard coding as the example i have given above.

each recipient should only get part of the report which is filtered on their name.


#3

is it possible to use data driven subscription and based on the parameter the report gets emailed to the relevant recipient?


#4

That is what data driven subscripts are for. They are very powerful in that regard. You setup the report with the parameter as you indicated. In the data driven subscription, you would setup the SQL to determine the people who receive the report and set the parameter for each report based on that.