Reza Alirezaei’s Blog

In Part 1, I gave you a quick tour on what needs to be set up before you can jump into data driven subscriptions in SharePoint. Now the real fun begins. From the ECB menu of a report choose Manage Subscription.

In the Manage Subscriptions page , click on the Add Data Driven Subscriptions button. This will take you to step 1 of 4 of setting up a data driven subscription.

Step 1) This step is literally the heart of the subscription and where our stored proc comes to the picture. Basically, you tell the subscription where to connect to retrieve the parameters at run time and how! I am using a shared data source I already built it to point to my Membership provider (not covered in this post). In the Query textbox I specified a call into my stored proc. Remember, you can also use a T-SQL query instead of the stored proc thing , but you gotta make sure that you hit that darn Validate button to not only validate your query, but also to retrieve the schema of the returned result set to be used in the upcoming steps.

A big No Answer to couple of potential smart questions:

• Can I make the data driven query dynamic and extract it at runtime too? No.
• Can I merge delivery extensions in one single subscription, say deliver the report via email and dump into a doc lib as well? No.
• Is there anyway I can create a 1:m relationship between my data driven subscription and my reports? No – only 1:1.
• I know that the list of subscribers to a data driven subscription can be extracted dynamically and it should be stored somewhere , but is there any way to make where subscribers data source points to , dynamic as well? No. Subscribers data source (In this case membership provider)must be static and defined when setting up the data driven subscription. No dice in making that dynamic too.

Step 2) The sample report I am using in this demonstration has a parameter called sales order number. In this step, you get a chance to set this parameter. I am opting for the parameter’s default value (first option) , but as shown in the picture below you can set another value (specific for this subscription) in the textbox or you can get the parameter from a field returned from your subscription query (discussed in step 1) from the drop down control.

Step 3) A subscription (either static or data driven) is meaningless without a delivery mechanism. In this step , you indicate what the delivery extension you want your subscription to use. The plan is to deliver the report via email so go ahead and choose Email from the drop down menu. As you can tell , there are many other properties in this page that can be parametrized and initialized at run time. Our stored proc only returns the actual email addresses so just set TO field to Email column of the returned result set. You also have to set the Subject and Format (not shown in the picture below) before proceeding to the next step.

Step 4) Now , you need to tell the subscription when to fire off. You have a choice of using an already created Shared Schedule or you can configure a dedicated schedule for this particular subscription. I am using a shared schedule (and Yes, I am going to present this whole thing at my presentations in Microsoft techdays 2008 tour across Canada)

When you click Finish on Step 4, you will be redirected to where you start and you are done. Click on the subscription you just built and you should be able to see something like the following picture:

Once the subscription gets executed , emails are nicely delivered as expected :

Two things need to be highlighted here :

• If you had chosen to include a link to the actual report in step 3 (I didn’t) , you would see that the link to the report is from the zone that user belongs too. It was kind of interesting!
• I am using smarter mail as my mail server and client. Read my post here for more information.

Click on the attachment and watch the result :

Yeap! that’s pretty much all about it. Hope you have enjoyed our journey on SSRS data driven subscription topic.

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.

…  replacing the Open with Report Builder link (to point to the new 2.0 RB ClickOnce app)  in the Report Viewer Web part is NOT available in the RTM version.It is pushed back to SQL Server 2008 SP1 or CU release. Read this thread!

FYI, if they would have released this functionality in RB 2.0 , you could set the URL to the new RB 2.0  by browsing to central administration site, under Application Management tab in the Reporting Services Section , choose Set Server Default page then you could set the URL in the Custom Report Builder launch URL text box. Looks like for now we should just leave it empty 😉

Clarification : This doesn’t mean that you can’t use RB 2.0 in the Integrated mode. Just replacing the link in reportviewer Web part isn’t possible. Not a biggie , but I thought you may be intrested to know.

I’ll have another quick tip popping up in the next blog post. See ya in a bit!

As I promised last week in this blog post, the first chance I get , I would craft up another POC using PowerShell that takes care of deploying SSRS reports and data sources in bulk to SharePoint.Here you go:

You can download the script here and below are the steps to get this script to work:

1) Create a folder and throw the script there.

2) All right! now you need to compile the SSRS Porxy endpoint for SharePoint integrated mode (ReportService2006.asmx) and build the  ReportService2006.dll. There are couple ways to do this (like this), but most likely the easiest way is by using Visual Studio command prompt.This is where wsdl.exe comes handy.Just as FYI, Wsdl.exe parses ReportService2006.asmx schema and its WSDL file and generates a .NET class (ReportService2006.cs). Since the schema of ReportService2006.asmx won’t change until the next version (I hope so!), you don’t need to repeat this step anymore. Open Visual Studio command prompt and type:

wsdl http://myserver/_vti_bin/ReportServer/ReportService2006.asmx 

Now, invoke and point the C# compiler (csc.exe) to the generated class and create the DLL (in your active directory):

 csc /t:library ReportService2006.cs

In the source code, this dll is used to communicate with the endpoint.

• If you don’t know what the hell I am talking about , I kindly ask you to read this post to know what I mean by SSRS proxy endpoint.Thanx!
• If you are lazy , you can download the dll right from here. I’ve already generated the dll.

3) Dump  the ReportService2006.dll in the same folder as the script.

4) Throw all of your reports and data source in the same folder.

Actually, this Powershell script (unlike the feature approach) is not bound to one shared data source.  I read the data souce from the rdl file, so as long as the data source is physically present in the same folder,script works just fine. Obviously , this is just another POC ;therefore it comes with no exception handling!

5) Open the PowerShell command line , browse to your folder  and type in the following command:

DeploySSRSInteg.ps1 -targetRptLib “http://myserver/myrptlib” -targetDCL “http://myserver/myDCL” -sourceFolder “C:\SSRSDeployment ” -rsProxyEndpt “http://myserver/_vti_bin/ReportServer/ReportService2006.asmx”

6) Once you hit the enter , you should be able to see a traffic light popping into your screen 🙂 (just kidding!). Here is the execution screenshot:

7) And here are all your data sources and reports deployed for you. PoSh rocks baby!

Data sources are converted:

Reports are published & its data source is nicely referenced:

‘8) As always, this is just a POC and by no means production ready , so use it at your own risk!

With all the issues IIS caused in the previous versions of SSRS, specially to configure it to interact with other applications (such as SharePoint) hosted in IIS , in SSRS 2008, Microsoft made a major architectural shift to move toward to a *real* Single Service Architecture that would eliminate dependency on IIS.

You no longer require having IIS installed on the RS box and, even nicer; you don’t have to install SQL on that RS box! Well, let me rephrase what I just said above. SSRS doesn’t require IIS to function, but this only applies when you configure it in native mode. In integrated mode and since you have to join the SSRS machine to the farm, you have to install SharePoint WFE components on RS machine and this means dependency on IIS, but again that’s the dependency SharePoint has on IIS , not SSRS!

This shifting architectural decision took care of two very serious issues bugging customers constantly:

1. SSRS trying to take over port 80 which was in control of SharePoint and subsequently throwing an unrelated exception that tipped many people over the edge when configuring SSRS in integrated mode.
2. Hosting IIS on the database server and potentially exposing security threats.

In the previous version of SSRS, there were two services (Web Service and Windows Service) that worked hand-to-hand and collaborated (Collab sounds familiar eh? 😉 ) to execute the reports, but in the new version they rearchitected and abstracted everything to a SINGLE service – SSRS Windows service. Obviously, WMI APIs are still available for configuration tools, third party apps and your enterprise deployment scripting fun, but Windows service is just serving as the main entry into all of the background and foreground services SSRS offers!

Well, finding a suitable host to support the shifting decision was not that difficult. They just shoved everything into the common architectural layer as with SQL Server uses, but the idea of hosting ASP.NET inside SQL CLR inside SSRS service process was extremely intuitive. I was like WOW when I first saw it  (my jar hitting the floor kind a) – SSRS team literally came up with their own mini IIS with many similar characteristics. Wait! It doesn’t mean that you have another web server hosted in SQL CLR. It’s just there for them to serve incoming HTTP requests using SQL Server networking interface and again mainly for departing from IIS land.

Why am I using the word mini IIS here is because, first SSRS uses the same underlying network layer as IIS 6 and higher uses, built right into the operating system– a.k.a HTTP.SYS layer. Secondly, SSRS 2008 has all the IIS components it requires to function without having any dependencies on IIS. Moving away from IIS, called for two major actions to be taken care of by SSRS team:

1. Disabling some of the functionalities IIS handles which there are no equivalent in SSRS 2008 , such as anonymous, client certificate and ISAPI crap. (I have allergy to ISAPI – sorry!)
2. Enabling some of the functionalities that IIS used to handle for SSRS before. Things such as ability to add virtual directory, port, SSL certificate, W3C compliant logging mechanism and so on so forth.

A lot of new IIS-Like settings are configured by Reporting Service Configuration Manager (picture below) , a tool that is significantly changed since the previous version. In this tool, Web Service URL tab is a just a new (and a very important) tab that offers creating Virtual directory , assigning ports and applying SSL certificates, but there is this important drop down that has the same behavior as IIS: IP Address.

As you can tell, the recommended option is All Assigned. This means that any HTTP requests that come in the specified port (TCP Port text box) and uses the specified URL reservation and virtual directory name (Virtual Directory text box) should be served by Reporting Services. So if you see something like http://+:80/ReportServer , the wildcard sign (+) indicates that you are using port 80 and All assigned when configuring the URL reservations settings for your report server.

On the other hand All Unassigned indicates that if there are no other listeners (services) registered for a specific URL reservation , on this port , for this virtual directory then give me (not me , I mean SSRS 🙂 ) the request. As you can see it fully supports IPv4 and wild card IPv6 addresses  and exactly like IIS you can load balance the incoming requests if your server happens to be using more than one NIC cards. Very nice, eh?

Let’s have some fun here:

Setup a SharePoint Web application on port 80 (let’s say http://mossdev) and then in configuration tool choose the same port for SSRS – for example http://mossdev/reportserver , with All Assigned option selected. If you note you can share the port with IIS hosted Web app. Then create a team subsite in SharePoint called ReportServer (http://mossdev/reportserver) – Once the proviosning of the site is finished, you will be redirected to SSRS – I laughed 🙂 !Change the SSRS virtual directory name or port number, issue is fixed. As you can see, understanding URL reservation is extremely important.

If you would like to see all of the URL reservations currently in place on your server , just be aware that HTTP.SYS comes with a set of command line utils. For example,  by typing “netsh http show urlacl” command , you would get all of the URL reservations echoed for ya. If you note in this particular case I am using a different port (Heck!It can even be a different URL!) for Report Manager and Report Sever.

If any one of you understand what those security descriptor strings (SDDL) mean , please drop me a line and educate me as well 😉

These URL reservation must mach the URL Resevation node in the RS configuration file as shown below, otherwise when you start the Windows service , there are going to be some inconsistencies in services SSRS offer: