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!

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:

See if you read all the documentation about SSRS 2008 integrated mod, they pretty much ask you to create the reports in BIDS and go through the publishing steps or simply upload your Reporting services artifacts to the designated document libraries. This is nice and easy, but sucks big time!

First off , no offense , but the majority of those who deploy have absolutely no clue how to use BIDS! Secondly BIDS is not necessarily available on the production servers. Thirdly, there are many scary clients out there who don’t entertain anything except full automation of your development bits including your reports, data models, data source and etc. All this calls for an automated solution that takes care of deploying your reports and all of their dependencies.

Well, we have a problem though! While you can publish report definitions (.rdl) and report models (.smdl) to a SharePoint library, this does NOT apply to data source (.rds) files! That’s exactly what design tools (i.e. BIDS) do under the hood! During publication, design tools create a new .rsds file from the original .rds file and publish that file instead. They literally convert the .rds file to its .rsds counterpart which is pretty contains same content but in different schema! This process very much resembles to what InfoPath publishing wizard does by turning UDC files into UDCX files for browser-enabled forms.

Truth to be told, if you want to fully automate your deployment, you should write your own converter and perform the deployment by utilizing SharePoint feature framework and SSRS proxy endpoint (ReportService2006.asmx). I am not sure how difficult it was for the SSRS team to provide another web method that takes a .rds file and publish it to a SharePoint document library!! The functionality is there and is used by design tools, right? They just didn’t expose it to us!

Okay, I just wrapped up a POC that demonstrate how you can accomplish this. You can download the code here.

1. Craft up a project and include the shared data source and all of your reports like below:

BTW, I am using WSPBuilder for this project.

2. In feature.xml, put in the following CAML code snippet:

As you can tell, in the feature manifest, I am referencing the RsProducts.rdl and DtSourceNorthwind.rds files as ElementFile inside ElementManifests node.In this blog post I am assuming that each feature pack contains multiple reports,but share one data source. If you have other reports which don’t share data source, just create more features. Who cares? Too many features?!! Well, you can hide them all and use a hub feature that when gets activated, activates all of the child features; so does the deactivation. No worries!

One more thing about the feature above. Since I am using Data Connection Library and Report Library to host my SSRS artifacts, there is this activation dependency to ensure that enterprise feature is already turned on.

Let’s move on and have a look at the code in the feature receiver:

[CSharp]
public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
SPWeb web = properties.Feature.Parent as SPWeb;
ReportingService2006 rs06 = new ReportingService2006();
rs06.Url = web.Url + “/_vti_bin/ReportServer/ReportService2006.asmx”;
rs06.Credentials = System.Net.CredentialCache.DefaultCredentials;
…
}
[/CSharp]

In the FeatureActivated method, I first initialize the SSRS Proxy endpoint which has already been nicely virtualized and context aware by SSRS Add-in for me. Security context under which this Web service call gets executed is determined by how you set up SSRS authenitcation mode in the Central Administration site. Read my post here.

[CSharp]
string featurePath = string.Format(@”{0}\FEATURES\{1}\”, SPUtility.GetGenericSetupPath(“Template”), @”Top20Products”);
string[] reportPaths = Directory.GetFiles(featurePath, “*.rdl”);
string[] dataSourcePath  = Directory.GetFiles(featurePath, “*.rds”);
if(dataSourcePath.Length != 1)
throw new Exception(“Oops!Only one shared datasource per feature pack or there is no datasource”);
[/CSharp]

So the plan is I can simply copy reports and their shared data source over to the feature folder so when feature gets activated I can call ReportService2006.asmx to publish reports, create the data source and finally fix up the data source reference in the deployed reports. Since the report and its data source is already deployed to the feature folder , I can simply get a reference to the files on the WFE server.

[CSharp]
XmlNode extensionNode = null;
XmlNode connectStringNode = null;
XmlNode rptDataSourceNameNode = null;

fileName = Path.GetFileName(dataSourcePath[0]).Replace(“rds”, “rsds”);
stream = File.OpenRead(dataSourcePath[0]);
XmlDocument dsDOM = new XmlDocument();
dsDOM.Load(stream);
stream.Close();
rptDataSourceNameNode = dsDOM.SelectSingleNode(“RptDataSource/Name”);
XmlNodeList connectionPropertiesNodes = dsDOM.SelectNodes(“RptDataSource/ConnectionProperties”);

foreach (XmlNode node in connectionPropertiesNodes)
{
extensionNode = node.SelectSingleNode(“Extension”);
connectStringNode = node.SelectSingleNode(“ConnectString”);
}

DataSourceDefinition dsDefinition = new DataSourceDefinition();
DataSource dSource = new DataSource();
dSource.Item = dsDefinition;
dsDefinition.Extension = extensionNode.InnerText;
dsDefinition.ConnectString = connectStringNode.InnerText;
dsDefinition.ImpersonateUserSpecified = true;
dsDefinition.Prompt = null;
dsDefinition.WindowsCredentials = true;
dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated;
dSource.Name = rptDataSourceNameNode.InnerText;
string parentDataSourceLibrary = “http://mossdev/DemoDCL”;
string fullDSUrl = string.Concat(parentDataSourceLibrary,”/”, fileName);
rs06.CreateDataSource(fileName, parentDataSourceLibrary, true, dsDefinition, null);
[/CSharp]

As you can tell, in the receiver class I am loading the .rds file into an XMLDocument object which I can then traverse to programmatically create the equivalent .rsds data source. Please be noted that for simplicity sake, I am just converting a simple data source (look st the source code), but be my guest if you’d like to deserialize .rds data source into a full-blown business object to cover more complex data source types. I am just trying  to keep the POC focused on the topic and simple!

Quite frankly, I don’t care about setting data sources properly during deployment. You can just create dummy data sources, reference those in your reports and when everything gets deployed, just go to the Data Connection Library and fix up the data sources. As part of the integration, you have the ability to set data source properties right from SharePoint UI. Plus , you should always be using shared data sources as much as possible so supposedly there shouldn’t be crazy number of data sources in your deployment.

I also hardcoded the parentDataSourceLibrary path that points to a Data Connection Library. You should either programmatically create the Report and Data Connection library in your feature receivers or use the same ( or another) feature to create these doc libs using CAML. I assumed that lists are already there. what a lazy a$$ I am 😉

[CSharp]

Warning[] warnings = null;
foreach (string rptPath in reportPaths)
{
fileName = Path.GetFileName(rptPath);
stream = File.OpenRead(rptPath);
Byte[] definition = new Byte[stream.Length];
stream.Read(definition, 0, (int)stream.Length);
stream.Close();
string parentReportLibrary = “http://mossdev/DemoReports”;
string fullReportURL = string.Concat(parentReportLibrary, “/”, fileName);
CatalogItem item = rs06.CreateReport(fileName, parentReportLibrary, true, definition, null, out warnings);
foreach (Warning warning in warnings)
{
//Proceed with logging the warnings
}
DataSource[] catalogItemDtSrcs = rs06.GetItemDataSources(fullReportURL);
DataSourceReference reference = new DataSourceReference();
DataSource dsNew = new DataSource();
reference.Reference = fullDSUrl;
dsNew = catalogItemDtSrcs[0];
dsNew.Item = reference;
rs06.SetItemDataSources(fullReportURL, catalogItemDtSrcs);
}
[/CSharp]

At the end we just publish the reports again to a hardcoded Report library named “DemoReports ” and fix up the wrong data source references. That’s basically it. just Build & Deploy the project. Upon successful deploy , you should see a Web scoped feature that when gets activated , injects the data source into a Data Connection Library called DemoDCL and the report to a Report library called DemoReports.

*The source code for the sample we did in this blog post can be downloaded here.

*If I get a chance next Weekend , I will craft up a PowerShell script which does pretty much the same thing  but without utilizing the feature framework. I will keep you posted here!

1/2 a great rest of your weekend 😉