I haven't been able to find any information about this, but excuse me if someone else has already asked a similar question.
Using one report and one data driven subscription, I would like to send varying result sets to different groups of users based on a parameter.
I set up a query (see below) that returns a list of email addresses and then loops through based on the @suppgroup variable.
What I would then like to do is have reporting services send reports to the recipients with the relevant result sets based on the @suppgroup variable.
Currently, the subscription runs but does not loop through the @suppgroups table properly. It merely gets the first row and sends the report to that email address.
I guess the main question is if this is at all possible. Please let me know if more information would help.
declare @suppgroup varchar(50)
declare @suppgroups table(suppnumber int PRIMARY KEY IDENTITY(1,1),suppgroup varchar(50))
insert into @suppgroups (suppgroup)
select distinct suppgroup from dbo.opencall
declare @suppgroupcount int
set @suppgroupcount = (select max(suppnumber) from @suppgroups)
DECLARE @counter int
set @counter = 1
while @counter <= @suppgroupcount
set @suppgroup = (select suppgroup from @suppgroups where suppnumber = @counter)
select DISTINCT u.email,o.suppgroup from dbo.userdb as u
join dbo.opencall as o
on o.owner = u.keysearch
where o.suppgroup = @suppgroup
set @counter = @counter + 1