Reza Alirezaei’s Blog

The thing is, if you read SQL Server 2008 books online (For example here or here) , it is explicitly mentioned that Data-driven subscriptions are not supported when the report server is configured for SharePoint integrated mode!  I could have sworn that I heard several times from SSRS top subject matters that they have added this new functionality to the SSRS 2008. Plus looking at the proxy endpoint tells me that the functionality is there, but <rant>like a lot of other things in the Integrated mode (and I’m talking about SSRS 2008 not 2005 SP2), there are a few non-MS people who have gone ahead and partially demonstrated how to implement stuff! All I see is a marketing *LIST* of all the new goodies ,but  not even a single Web cast or How-To with the focus on the integration</rant> lovely! eh?

So one million dollar question is : Is Data Driven Subscriptions part of SSRS 2008 when it is configured in integrated mode? Answer is Yes! It is fully supported.

Before I walk you through how to set this up in SharePoint (mostly covered in Part 2) , I would like to give you  a brief tour of what the data driven subscription concept is and show you some preparatory steps that you need to take before you touch the SharePoint equivalent feature sets. Okay, let’s get the ball rolling here:

Generally speaking in SSRS , subscriptions are an alternative way of delivering reports to people in a very dynamic and automated manner.The objective is minimizing On-demand access to the report server; therefore reducing the traffic and boosting the performance.  It is all about targeting different information at different audiences , all done in a data driven fashion and asynchronously (can’t u tell that I am a developer?! 😉 )  delivered to them. Subscriptions are processed on the report server and are distributed through delivery extensions (another important concept in SSRS) that are deployed on the server. Two very important delivery extensions in SharePoint are:

• Email
• SharePoint Document Library.See this.

Let me quickly walk you through setting up a data driven subscription (using Email delivery extension) on a sample report deployed  to a Report Library in a SharePoint site. I am sure you will see the value! All right, the plan is to distribute our sample report to a fluctuating list of subscribers kept in a role (in my role provider) called SSRSSubs. Let’s see , for a data driven subscription based on the email delivery extension , what you would need to put together:

1) Membership and Role provider: I am using the out of the box AspnetSqlMembership and role provider for this demonstration; therefor I don’t spend any time digging into what roles and memberships are. Having a membership and role provider to benefit from data driven subscriptions is absolutely NOT necessary, but making the parameters of a subscription dynamic and keep their values somewhere (such as a SQL db) and retrieving them at run time is what data driven subscriptions is all about. Are we square on this? GREAT! I heard the yes answer loud and clear (I have ear over IP kind of organ!) .Let’s move on then.

For this demonstration purpose I am just making the list of emails dynamic , but feel free to make everything dynamic such as the report’s parameter(s) , email’s subject, report’s format and etc. Just think about how powerful this could be!

I have added three users (user1, user2 and user3) to my membership provider and added them to SSRSSubs role. Each user has a respective email with the extension of techdays.ca (i.e user1@techdays.ca). All three emails are also defined in my mail server.

2) Data Driven Query: Each data driven subscription requires a query that retieves its paramters value at runtime. For this demonstration, I have written an stored proc called aspnet_GetEmailsInRoles that would return the emails of all the users in a given role stored in my role provider. This stored proc is not part of the out of the box AspnetSqlMembership database schema . As you will see in Part 2 , I will call into this stored procedure to retrieve the list of emails at run time.

[Sql]
USE [aspnetdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_GetEmailsInRoles]
@ApplicationName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL

SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

IF (@RoleId IS NULL)
RETURN(1)

SELECT m.Email
FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur, dbo.aspnet_Membership m
WHERE u.UserId = ur.UserId AND u.UserId = m.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
ORDER BY m.Email
RETURN(0)

END

[/Sql]

Here is the store proc execution screenshot:

3) Payload: Below is the report that I am trying to attach to a data driven subscription ( nice and sexy)

Clarification: As much as I’d love to take the credit for authoring the report shown above, this report is part of the AdventureWorks report pack that coms out of the box with default installation of SSRS 2008.

4) Email Settings: No matter how you’ve configured the outgoing email settings in SharePoint (Central Admin -> Operation -> Outgoing Email Settings) , you have to setup email settings in Reporting Service Configuration manager for the email delivery extension to show up in static and data driven subscriptions you set up in SharePoint.

Email delivery extension is not available:

You configure the email settings in Reporting Service Configuration tool :

Here is the “Email delivery extension” all of a sudden showing up:

5) SQL Agent Service: Obviously SSRS automated background services are handled by SQL Server Agent , so get the bad boy started if you haven’t already.

Okay, now that we have all of our non-SharePoint parts in place, so let’s just move on with making SharePoint aware of what we are planning to set up. Please proceed to part 2.