troubleshooting Question

Clever data driven subscriptions

Avatar of tommyfly
tommyfly asked on
DB Reporting ToolsMicrosoft SQL Server 2005
6 Comments2 Solutions2023 ViewsLast Modified:
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.

Thanks
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
begin
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
end
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros