Home > MOSS 2007, SSRS > SSRS:Data Driven Subscriptions in Integrated mode (Part 2)

SSRS:Data Driven Subscriptions in Integrated mode (Part 2)

October 26th, 2008 Leave a comment Go to comments

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.

ManageSubscriptions

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.

Sales_Order_Detials

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.

DDSubscriptionOk

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.

DDSubsSecondWizard

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.

DDSubsThirdWizard

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)

DDSubsLastWizard

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:

WhenDDIsCompleted

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

SubscriptionSentToUser1

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 :

ReportIsAttached

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

Categories: MOSS 2007, SSRS Tags: , ,
  1. Liu Chen Sum
    April 3rd, 2009 at 08:58 | #1

    Awesome article…I have been working on this very topic in SSRS2005sp2 and for me it was easier to just have a sharepoint integrated version and a second named instance in native mode…I use the sharepoint one for interface reports, but for automated things such as data-driven subscriptions I rely on the full capabilities of native mode.

    Do you know if SSRS 2008 supports both an integrated and native versions running side by side?

    Thanks!

  2. Reza Alirezaei
    April 3rd, 2009 at 09:44 | #2

    @Liu Chen Sum

    Liu, Thanks

    An instance of SSRS 2008 either can be native or Integrated so I’d say NO to your question.

  3. Achal
    September 10th, 2009 at 08:17 | #3

    Data Driven Subscription does not show up in my SSRS 2008 sharepoint integrated mode. Do you know if that needs to be enabled for it to show up as one of the options for delivery?

  4. Sergey Minyurov
    March 13th, 2012 at 08:38 | #4

    @Achal
    Data-driven subscriptions are not supported in the Standard edition or SQL Server Express with Advanced Services/
    http://msdn.microsoft.com/en-us/library/ms159150.aspx