How to make data driven subscription using stored procedure.

Tested with SQL Server 2012

What is data driven subscription?

Suppose if we want to deliver an SSRS report to a particular user every day at 6 Am in the morning, we can setup a subscription. Suppose we need to send the same report to all department heads (say 5 people) each one should get the report in pdf which is rendered with their own dept code passed as parameters. This we can achieve by setting up 5 different subscriptions each one with each dept code as parameter and dept head’s email id as subscription email. But what will happen if we need to send the report to 1000 customers each one with different parameter and to different email id. We can use data driven subscription in this case. We can have a table which contains parameter( s ) and email id. Report has to use the information from table dynamically. 

Why do we need TSQL for data driven subscription ?

SSRS does have an inbuilt data driven subscription which is available only on Enterprise edition of SQL Server . This is a valid reason for using data driven subscription using TSQL. Another reason for using this is tracking the delivery. If we have few thousands of mail delivery and we may need to track the outgoing mails, this would be the best solution as we may be able to track the mails delivered and it is possible to resend if it got broken in between. Another reason for using this everything can be encapsulate in SQL server and we will have full control over it. Also we can initiate notification if a delivery fails.  Also this can be used to keep a decent log of actions for each email delivery.

See the specs on how to do it.

1. Create a table with columns for one or more parameter and ToEmail id. You can customise it by addint CCEmail, BCCEmail etc. And each email id can be single email or multiple email id seperated by ;. Lets call this table as tblSubscribers

2. Create the report and publish. Create a subscription of the report with any dummy parameter and email id. No need to schedule it. Once you create the subscription, an sql agent job will be created automatically. You can get the subscription ID by querying like this in the ReportServer database:

SELET cat.Name AS SSRSReport,rsd.ScheduleID

FROM Catalog cat

INNER JOIN Subscriptions sub ON cat.ItemID = sub.Report_OID

INNER JOIN ReportSchedule rsd ON cat.ItemID = rsd.ReportID

AND rsd.SubscriptionID = sub.SubscriptionID Where cat.Name = ‘<SubscriptionName>’

3. Create a stored procedure usp_Subscription which will have the following steps

a) Table SubscriptionID from ReportSchedule using the scheduleID we got already

b)Get into a loop or cursor looping through each row in the table tblSubscribers

c)In the Subscriptions table there is a field called extension settings which is an XML value which contain few inputs including ToEmail id. Either replace that ToEmail there or create another xml with our own value and update that to the table

d)There is another field Parameters in the same Subscriptions which has to be replaced with our parameter value from the table

e)call the job msdb..sp_start_job with parameter as scheduleID

f) do a wait if necessory

g) add the logging code here. You can take the log status from LastStatus column from the subscriptions table

e) end loop

Introduction to BI Semantic Model in SQL Server CTP3 Denali

  • Analysis Services Denali has two enginges: the traditional OLAP one (the only one it had until 2008) and the new Vertipaq one (introduced by PowerPivot and now directly available without SharePoint and/or Excel).
  • In order to cover these two tecnologies under the same hat, a new acronym has been introduced: BISM (Business Intelligence Semantic Model)
  • When yuo create a project for BISM you can use either BISM Multidimensional (corresponding to a classic SSAS project based on the OLAP engine) or BISM Tabular (corresponding to the new project type based on the Vertipaq engine).
  • In documentation, books and article you will read Multidimensional (corresponding to BISM Multidimensional) and Tabular (corresponding to BISM Tabular).
  • Both Multidimensional and Tabular models can be queried by using MDX. However, internal calculation are based on DAX in Tabular and DAX can also be used to query a Tabular model. In a possibly near future, we should expect to use DAX also to query a Multidimensional project (I hope this will happen very very soon – months and not years).
  • When you install Analysis Services Denali you have to choose what type of engine you want to use for the instance you are installing. If you want to be able to use both engines, you need to install two instances of Analysis Services. During the setup you can choose which one you desire and Multidimensional is the default.
  • The UDM acronym (Unified Dimensional Model) is no longer used. An UDM project is now a BISM Multidimensional project. THere is a 100% compatibility for existing projects and from the point of view of the existing feature, BISM Multidimensional is 100% corresponding to UDM in 2008 R2. Any existing book, documentation and whitepaper is still valid in BISM Multidimensional. The Analysis Services release has improvements (there is no longer a 4GB limit for string stores, for example), fix and optimizations, but there aren’t new major features in Multidimensional for this release.

– courtesy Marco Russo