Data Driven Subscriptions Ssrs Standard Edition 2008

January 31, 2024 Post a Comment

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 ad

Solution 1:

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.

Solution 2:

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:

CREATEPROCEDURE[dbo].[DDR3] 
   @reportNamenvarchar(200),
   @parameterSetnvarchar(200),
   @nonZeroQuerynvarchar(2000),
   @toEmailnvarchar(2000)
ASBEGIN--SETNOCOUNTONaddedtopreventextraresultsetsfrom--interferingwithSELECTstatements.
   SETNOCOUNTON;

   selectddr.dbo.RunADDR(@reportName,@parameterSet,@nonZeroQuery,@toEmail) asDDRresult;

   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

Solution 3:

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.

Solution 4:

R-Tag supports SSRS data driven reports with SQL Server standard edition

Solution 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).

Spark Sql Blog