{"id":762,"date":"2008-10-26T20:29:02","date_gmt":"2008-10-27T01:29:02","guid":{"rendered":"http:\/\/blogs.devhorizon.com\/reza\/?p=762"},"modified":"2008-10-27T17:29:19","modified_gmt":"2008-10-27T22:29:19","slug":"ssrsdata-driven-subscriptions-in-integrated-mode-part-2","status":"publish","type":"post","link":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/","title":{"rendered":"SSRS:Data Driven Subscriptions in Integrated mode (Part 2)"},"content":{"rendered":"<p>In <a href=\"https:\/\/blogs.devhorizon.com\/reza\/?p=761\">Part 1<\/a>, 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 <strong>Manage Subscription<\/strong>.<\/p>\n<p><a href=\"http:\/\/farm4.static.flickr.com\/3048\/2975421183_257995af84_b.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/farm4.static.flickr.com\/3048\/2975421183_257995af84_m.jpg\" alt=\"ManageSubscriptions\" width=\"240\" height=\"150\" \/><\/a><\/p>\n<p>In the<strong> Manage Subscriptions<\/strong> page , click on the <strong>Add Data Driven Subscriptions<\/strong> button. This will take you to step 1 of 4 of setting up a data driven subscription.<\/p>\n<p><a href=\"http:\/\/farm4.static.flickr.com\/3041\/2976285866_9cbc49fb06_o.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/farm4.static.flickr.com\/3041\/2976285866_fd5809c4e3_m.jpg\" alt=\"Sales_Order_Detials\" width=\"240\" height=\"180\" \/><\/a><\/p>\n<p><strong>Step 1)<\/strong> 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 <strong>Query<\/strong> 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 <strong>Validate <\/strong>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.<\/p>\n<p><a href=\"http:\/\/farm4.static.flickr.com\/3282\/2976285498_907c7bcdb6_o.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/farm4.static.flickr.com\/3282\/2976285498_975958b273_m.jpg\" alt=\"DDSubscriptionOk\" width=\"240\" height=\"180\" \/><\/a><\/p>\n<p>A big No Answer to couple of potential smart questions:<\/p>\n<ul>\n<li>Can I make the data driven query dynamic and extract it at runtime too? No.<\/li>\n<li>Can I merge delivery extensions in one single subscription, say deliver the report via email and dump into a doc lib as well? No.<\/li>\n<li>Is there anyway I can create a 1:m relationship between my data driven subscription and my reports? No &#8211; only 1:1.<\/li>\n<li>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.<\/li>\n<\/ul>\n<p><strong>Step 2)<\/strong> The sample report I am using in this demonstration has a parameter called <strong>sales order number<\/strong>. In this step, you get a chance to set this parameter. I am opting for the parameter&#8217;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.<\/p>\n<p><a href=\"http:\/\/farm4.static.flickr.com\/3165\/2975431707_fdec832c77_o.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/farm4.static.flickr.com\/3165\/2975431707_1a6da23607_m.jpg\" alt=\"DDSubsSecondWizard\" width=\"240\" height=\"180\" \/><\/a><\/p>\n<p><strong>Step 3)<\/strong> A subscription (either static or data driven) is meaningless without a delivery mechanism. In this step , you indicate what the <strong>delivery extension<\/strong> you want your subscription to use. The plan is to deliver the report via email so go ahead and choose <strong>Email <\/strong>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 <strong>TO<\/strong> field to <strong>Email<\/strong> column of the returned result set. You also have to set the <strong>Subject<\/strong> and <strong>Format<\/strong> (not shown in the picture below) before proceeding to the next step.<\/p>\n<p><a href=\"http:\/\/farm4.static.flickr.com\/3174\/2976285576_3ca086b07f_o.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/farm4.static.flickr.com\/3174\/2976285576_797c06880c_m.jpg\" alt=\"DDSubsThirdWizard\" width=\"240\" height=\"180\" \/><\/a><\/p>\n<p><strong>Step 4)<\/strong> 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 <a href=\"http:\/\/www.microsoft.com\/canada\/techdays\/instructors.aspx\">Microsoft techdays 2008<\/a> tour across Canada)<\/p>\n<p><a href=\"http:\/\/farm4.static.flickr.com\/3054\/2976285696_d93cdfea89_o.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/farm4.static.flickr.com\/3054\/2976285696_1e67ef4a1f_m.jpg\" alt=\"DDSubsLastWizard\" width=\"240\" height=\"180\" \/><\/a><\/p>\n<p>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:<\/p>\n<p><a href=\"http:\/\/farm4.static.flickr.com\/3296\/2975431389_92ca1ed70a_o.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/farm4.static.flickr.com\/3296\/2975431389_0e93059560_m.jpg\" alt=\"WhenDDIsCompleted\" width=\"240\" height=\"180\" \/><\/a><\/p>\n<p>Once the subscription gets executed , emails are nicely delivered as expected :<\/p>\n<p><a href=\"http:\/\/farm4.static.flickr.com\/3156\/2976286224_0c27cd9664_o.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/farm4.static.flickr.com\/3156\/2976286224_5a9dd26871_m.jpg\" alt=\"SubscriptionSentToUser1\" width=\"240\" height=\"180\" \/><\/a><\/p>\n<p>Two things need to be highlighted here :<\/p>\n<ul>\n<li>If you had chosen to include a link to the actual report in step 3 (I didn&#8217;t) , you would see that the link to the report is from the zone that user belongs too. It was kind of interesting!<\/li>\n<li>I am using smarter mail as my mail server and client. Read my post <a href=\"https:\/\/blogs.devhorizon.com\/reza\/?p=694\">here <\/a>for more information.<\/li>\n<\/ul>\n<p>Click on the attachment and watch the result :<\/p>\n<p><a href=\"http:\/\/farm4.static.flickr.com\/3172\/2976286310_23a2f611b6_o.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/farm4.static.flickr.com\/3172\/2976286310_6482258146_m.jpg\" alt=\"ReportIsAttached\" width=\"240\" height=\"180\" \/><\/a><\/p>\n<p>Yeap! that&#8217;s pretty much all about it. Hope you have enjoyed our journey on SSRS data driven subscription topic.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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. [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43,47],"tags":[18,3,17],"class_list":["post-762","post","type-post","status-publish","format-standard","hentry","category-moss-2007","category-ssrs","tag-integration","tag-moss-2007","tag-ssrs"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SSRS:Data Driven Subscriptions in Integrated mode (Part 2) - Reza Alirezaei&#039;s Blog %<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Reza Alirezaei\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/\"},\"author\":{\"name\":\"Reza Alirezaei\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#\\\/schema\\\/person\\\/cdbb24d283697a65951cb4a14e474938\"},\"headline\":\"SSRS:Data Driven Subscriptions in Integrated mode (Part 2)\",\"datePublished\":\"2008-10-27T01:29:02+00:00\",\"dateModified\":\"2008-10-27T22:29:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/\"},\"wordCount\":722,\"commentCount\":6,\"image\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/farm4.static.flickr.com\\\/3048\\\/2975421183_257995af84_m.jpg\",\"keywords\":[\"Integration\",\"MOSS 2007\",\"SSRS\"],\"articleSection\":[\"MOSS 2007\",\"SSRS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/\",\"url\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/\",\"name\":\"SSRS:Data Driven Subscriptions in Integrated mode (Part 2) - Reza Alirezaei's Blog %\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/farm4.static.flickr.com\\\/3048\\\/2975421183_257995af84_m.jpg\",\"datePublished\":\"2008-10-27T01:29:02+00:00\",\"dateModified\":\"2008-10-27T22:29:19+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#\\\/schema\\\/person\\\/cdbb24d283697a65951cb4a14e474938\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/10\\\/26\\\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\\\/#primaryimage\",\"url\":\"http:\\\/\\\/farm4.static.flickr.com\\\/3048\\\/2975421183_257995af84_m.jpg\",\"contentUrl\":\"http:\\\/\\\/farm4.static.flickr.com\\\/3048\\\/2975421183_257995af84_m.jpg\"},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#website\",\"url\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/\",\"name\":\"Reza Alirezaei's Blog\",\"description\":\"Blogging from the field!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#\\\/schema\\\/person\\\/cdbb24d283697a65951cb4a14e474938\",\"name\":\"Reza Alirezaei\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g\",\"caption\":\"Reza Alirezaei\"},\"url\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/author\\\/rezaa\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SSRS:Data Driven Subscriptions in Integrated mode (Part 2) - Reza Alirezaei's Blog %","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/","twitter_misc":{"Written by":"Reza Alirezaei","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/#article","isPartOf":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/"},"author":{"name":"Reza Alirezaei","@id":"https:\/\/blogs.devhorizon.com\/reza\/#\/schema\/person\/cdbb24d283697a65951cb4a14e474938"},"headline":"SSRS:Data Driven Subscriptions in Integrated mode (Part 2)","datePublished":"2008-10-27T01:29:02+00:00","dateModified":"2008-10-27T22:29:19+00:00","mainEntityOfPage":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/"},"wordCount":722,"commentCount":6,"image":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/#primaryimage"},"thumbnailUrl":"http:\/\/farm4.static.flickr.com\/3048\/2975421183_257995af84_m.jpg","keywords":["Integration","MOSS 2007","SSRS"],"articleSection":["MOSS 2007","SSRS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/","url":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/","name":"SSRS:Data Driven Subscriptions in Integrated mode (Part 2) - Reza Alirezaei's Blog %","isPartOf":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/#website"},"primaryImageOfPage":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/#primaryimage"},"image":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/#primaryimage"},"thumbnailUrl":"http:\/\/farm4.static.flickr.com\/3048\/2975421183_257995af84_m.jpg","datePublished":"2008-10-27T01:29:02+00:00","dateModified":"2008-10-27T22:29:19+00:00","author":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/#\/schema\/person\/cdbb24d283697a65951cb4a14e474938"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/10\/26\/ssrsdata-driven-subscriptions-in-integrated-mode-part-2\/#primaryimage","url":"http:\/\/farm4.static.flickr.com\/3048\/2975421183_257995af84_m.jpg","contentUrl":"http:\/\/farm4.static.flickr.com\/3048\/2975421183_257995af84_m.jpg"},{"@type":"WebSite","@id":"https:\/\/blogs.devhorizon.com\/reza\/#website","url":"https:\/\/blogs.devhorizon.com\/reza\/","name":"Reza Alirezaei's Blog","description":"Blogging from the field!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blogs.devhorizon.com\/reza\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/blogs.devhorizon.com\/reza\/#\/schema\/person\/cdbb24d283697a65951cb4a14e474938","name":"Reza Alirezaei","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g","caption":"Reza Alirezaei"},"url":"https:\/\/blogs.devhorizon.com\/reza\/author\/rezaa\/"}]}},"_links":{"self":[{"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/posts\/762","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/comments?post=762"}],"version-history":[{"count":0,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/posts\/762\/revisions"}],"wp:attachment":[{"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/media?parent=762"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/categories?post=762"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/tags?post=762"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}