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.