January 2, 2009 at 10:26 am
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 multiple reports to different groups of users based on a parameter.
I set up a query that returns a list of email addresses and then loops though based on the @suppgroup variable:
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
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
In SQL there are no absolutes, it always depends...
January 12, 2009 at 2:30 am
Does anyone have any experience with data driven subscriptions in SQL Server 2005 Reporting services (SSRS)?
I've found some documentation online, but it's all been rather basic. Does this mean that the subscription functionality is basic or can you do any clever things with it?
Thanks in advance for any help.
In SQL there are no absolutes, it always depends...
January 13, 2009 at 3:05 am
The data driven subscription will send one email for each record in the dataset that your query returns, it won't handle the multiple datasets that your current query is returning. Try to rewrite to return to one dataset. One way would be to insert into a table variable where you currently have your select statement in the loop, then select from the table at the end.
January 13, 2009 at 7:06 am
Yes, you need to have one single result set. What gets sent to each recipient can be controlled by virtue of being able to supply a unique set of parameters to the report for each and every record in your recipient list. You don't HAVE to supply values for ALL the parameters that way, and you can either accept the default value or specify a static value, for each of the parameters that the report has. Thus you have to design your report with those capabilities in mind. Another thing to take into consideration is the possibility that a given set of parameters might produce zero result records, and thus a given recipient's report might be empty. That can be prevented by incorporating the query that is used to generate the report's dataset into an EXISTS clause in the query that drives the subscription. I've done this numerous times with no difficulties. Be aware, however, that SSRS can be somewhat picky about what's allowed in the subscription query, and a DELETE statement appears to be a no-go, although an UPDATE statement appears to be ok. The critical thing is having a single result set, and your existing code could easily be modified to create and populate either a CTE or a temp table.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 13, 2009 at 7:16 am
Try the following and see if it works for you:
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
SELECT DISTINCT U.email, O.suppgroup
FROM dbo.userdb AS U
JOIN dbo.opencall AS O
ON O.owner = U.keysearch
INNER JOIN @suppgroups AS S
ON O.suppgroup = S.suppgroup
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 15, 2009 at 2:39 am
Thank you SSChasing Mays. I've got this working properly now. I seemed to have had a block as to how this all worked. The crux of my problem was that I did not understand that the suppgroup in the data driven subscription query needed to be passed as a variable to the report.
In SQL there are no absolutes, it always depends...
January 15, 2009 at 6:32 am
Thomas,
Thanks for the feedback. Glad you have it working, and more importantly, that you now have a good idea of how it works, which can lead to being able to do that much more with Reporting Services.
Just an fyi, "SSChasing Mays" isn't my forum id, it's the category of posting I fall into, and apparently, those categories were chosen with a baseball theme in mind... "SSChasing Mays" refers to the idea of the number of my posts approaching the number of home runs hit by big leaguer Willie Mays. My forum id is smunson, and I'm Steve...
Steve
(aka smunson)
:):):)
Thomas Mucha (1/15/2009)
Thank you SSChasing Mays. I've got this working properly now. I seemed to have had a block as to how this all worked. The crux of my problem was that I did not understand that the suppgroup in the data driven subscription query needed to be passed as a variable to the report.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply