February 14, 2017 at 10:28 am
Newbie here in SSRS 🙂
Is there a way that i can run an email subscription based on column value?
Example SQL:
Select Max(finish_date) from table1
How can i run an email subscription when Max(finish_date) = today
Thanks in advance!
February 14, 2017 at 11:10 am
If you have the enterprise edition of SSRS, you can do it with a data-driven subscription: https://msdn.microsoft.com/en-us/library/ms159150.aspx
Part of the setup allows for a conditional query, where the subscription will only send if the conditions of the query are met.
February 14, 2017 at 11:39 am
you could also create a subscription in Standard edition, and use a SQL Agent job to call the subscription job if the criteria is met.
so assume you create a subscription that is set up to run once.
a job in the SQL agent gets created, say with the name "2CDAE955-AF5A-4E38-8017-581FFA2614F8"
Then you can use TSQL to make a test and run msdb.dbo.sp_start_job; yopu can test for multiple criteria as well:IF EXISTS(Select 1 from DatabaseName.SchemaName.table1 WHERE Max(finish_date) > DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) --midnight
AND datename(dw,getdate())='Monday'
EXEC msdb.dbo.[sp_start_job] @job_name = '2CDAE955-AF5A-4E38-8017-581FFA2614F8'
Lowell
February 14, 2017 at 11:44 am
also i use this proc to update the job description of SSRS subscriptiosn to have the report name; makes it a LOT easier to track down.
it uses dynamic SQL because the shop i work at uses names other than the default database names for SSRS:IF OBJECT_ID('[dbo].[sp_fix_ssrsjobs]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_fix_ssrsjobs]
GO
--#################################################################################################
-- modifies the Description of any SSRS jobs that are related to SSRS subscriptions to contain the SSRS report name
--#################################################################################################
CREATE PROCEDURE sp_fix_ssrsjobs
AS
BEGIN
DECLARE @cmd VARCHAR(MAX);
DECLARE @val VARCHAR(128);
IF OBJECT_ID('tempdb.[dbo].[#Results]') IS NOT NULL
DROP TABLE [dbo].[#Results]
CREATE TABLE #Results(DBName VARCHAR(128) );
--report server databases were renamed in some cases.
EXEC sp_msforeachdb '
IF EXISTS(SELECT * FROM [?].sys.tables WHERE name=''Catalog'' )
AND EXISTS(SELECT * FROM [?].sys.views WHERE name=''ExecutionLog2'')
INSERT INTO #Results(DBName)
SELECT ''[?]'' AS DBName';
declare c1 cursor LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
--###############################################################################################
--cursor definition
--###############################################################################################
select DBName from #Results;
--###############################################################################################
--the cursor below willfix any job descriptions that point to a report in the Report Server databases
open c1;
fetch next from c1 into @val;
While @@fetch_status <> -1
BEGIN
SELECT @cmd =
'
DECLARE @cmd VARCHAR(MAX);
WITH MyCTE
AS
(
SELECT
''UPDATE msdb.dbo.sysjobs set description = ''''SSRS Report Subscription: ''
+ e.path COLLATE SQL_Latin1_General_CP1_CI_AS + Char(13) + Char(10) + e.NAME COLLATE SQL_Latin1_General_CP1_CI_AS + '''''' WHERE job_id = '''''' + CONVERT(VARCHAR(40), b.job_id) + '''''';'' AS Fix
FROM ' + @val + '.dbo.ReportSchedule a
INNER JOIN msdb.dbo.sysjobs b
ON CONVERT(VARCHAR(40), a.ScheduleID) = b.NAME
INNER JOIN ' + @val + '.dbo.ReportSchedule c
ON b.NAME = CONVERT(VARCHAR(40), c.ScheduleID)
INNER JOIN ' + @val + '.dbo.Subscriptions d
ON c.SubscriptionID = d.SubscriptionID
INNER JOIN ' + @val + '.dbo.Catalog e
ON d.report_oid = e.itemid
)
SELECT @cmd = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT '';'' + Fix
FROM MyCTE
ORDER BY Fix
FOR XML PATH(''''), TYPE).value(''.'',''varchar(max)''),1,1,'''')
) s
PRINT @cmd
EXECUTE (@cmd)
';
print @cmd;
exec (@cmd)
fetch next from c1 into @val;
end;
close c1;
deallocate c1;
END -- PROC
GO
Lowell
February 14, 2017 at 12:30 pm
Thanks much for your replies 🙂 Used a data driven subscription with conditional query specified. Now its all good!
February 15, 2017 at 6:38 am
Data Driven Subscriptions were Reason #1 we first transition to Enterprise Edition. I've been using them for years now. How you are applying them now is one great example of their effectiveness. If you haven't yet, look into using them to send the same reports to various recipients with varied data. Very cool!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply