Data Driven Subscriptions SSRS Standard Edition 20

Question

I'm fairly new to MSSQL and SSRS.

I'm trying to create a data driven subscription in MSSQL 2008 Standard SSRS that does the following.

Email the results of the report to a email address found within the report. Run Daily

For Example:

Select full_name, email_address from users where (full_name = 'Mark Price')

This would use the email_address column to figure out who to email, This must also work for multiple results with multiple email address's.

The way I'm thinking of doing this is making a subscription to run the query, if no result is found then nothing happens. But if a result is found then the report changes the row in Subscriptions table to run the report again in the next minute or so with the correct email information found in the results. Is this a silly idea or not?

I've found a couple blog posts claiming this works but i couldn't understand their code enough to know what it does.

So, Any suggestions on how to go about this or if you can suggest something already out there on the internet with a brief description?

Answer 1

I've implemented something like this on SQL Server Standard to avoid having to pay for Enterprise. First, I built a report called “Schedule a DDR” (Data Driven Report). That report has these parameters:

Report to schedule: the name of the SSRS report (including folder) that you want to trigger if the data test is met. E.g. "/Accounting/Report1".

Parameter set: a string that will be used to look up the parameters to use in the report. E.g. "ABC".

Query to check if report should be run: a SQL query that will return a single value, either zero or non-zero. Zero will be interpreted as "do not run this report"

Email recipients: a list of semicolon-separated email recipients that will receive the report, if it is run.

Note that the “Schedule a DDR” report is the report we’re actually running here, and it will send its output to me; what it does is run another report – in this case it’s “/Accounting/Report1” and it’s that report that needs these email addresses. So “Schedule a DDR” isn’t really a report, although it’s scheduled and runs like one – it’s a gadget to build and run a report.

I also have a table in SQL defined as follows:

CREATE TABLE [dbo].[ParameterSet](
            [ID] [varchar](50) NULL,
            [ParameterName] [varchar](50) NULL,
            [Value] [varchar](2000) NULL
) ON [PRIMARY]

Each parameter set – "ABC" in this case – has a set of records in the table. In this case the records might be ABC/placecode/AA and ABC/year/2013, meaning that there are two parameters in ABC: placecode and year, and they have values "AA" and "2013".

The dataset for the "Schedule a DDR" report in SSRS is

DDR.dbo.DDR3 @reportName, @parameterSet, @nonZeroQuery, @toEmail;

DDR3 is a stored procedure:

CREATE PROCEDURE [dbo].[DDR3] 
   @reportName            nvarchar(200),
   @parameterSet   nvarchar(200),
   @nonZeroQuery   nvarchar(2000),
   @toEmail        nvarchar(2000)
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   select ddr.dbo.RunADDR(@reportName,@parameterSet,@nonZeroQuery,@toEmail) as DDRresult;

   END

RunADDR is a CLR. Here's an outline of how it works; I can post some code if anyone wants it.

• Set up credentials
• Select all the parameters in the ParameterSet table where the parameterSet field matches the parameter set name passed in from the Schedule A DDR report
• For each of those parameters
  • Set up the parameters array to hold the parameters defined in the retrieved rows. (This is how you use the table to fill in parameters dynamically.)
• End for
• If there’s a “nonZeroQuery” value passed in from Schedule A DDR
  • Then run the nonZeroQuery and exit if you got zero rows back. (This is how you prevent query execution if some condition is not met; any query that returns something other zero will allow the report to run)
• End if
• Now ask SSRS to run the report, using the parameters we just extracted from the table, and the report name passed in from Schedule A DDR
• Get the output and write it to a local file
• Email the file to whatever email addresses were passed in from Schedule A DDR

Answer 2

Instead of creating a subscription to modify the subscriptions table, I would put that piece somewhere else, such as in a SQL agent. But the idea is the same. A regularly running piece of SQL can add or change lines in the subscription table.

A Google of "SSRS Subscription table" returned a few helpful results: Here's an article based on 2005, but the principles should be the same for 2008: This article is for 2008, and is really close to what you are describing as well.

I would just look at the fields one by one in the subscriptions table and determine what you need for each. Try creating a row by hand (a manual insert statement) to send yourself a subscription.

Answer 3

This takes me back to my old job where I wrote a solution to a problem using data-driven subscriptions on our SQL Server 2005 Enterprise development box and then discovered to my dismay that our customer only had Standard.

I bookmarked this post at the time and it looked very promising, but I ended up moving jobs before I had a chance to implement it.

Of course, it is targeted at 2005, but one of the comments seems to suggest it works in 2008 as well.

Answer 4

There are some tools on the market , which support SSRS data driven reports :

• R-Tag
• CRD.

These are Desktop tools but I guess you are not looking to replace SSRS , just to extend it. You can use them with SQLServer Standard edition.

Answer 5

You can use SQL-RD, a third-party solution, to create and run data-driven schedules without having to upgrade to SQL enterprise. It also comes with event-based scheduling (triggers the report on events including database changes, file changes, emails received and so on).