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
