SSRS subcription email with new entry

Question

I hope someone can guide me or point me to a guide. I have a report where it lists all the fees. It's very similar to the table; however, I would like for some users to get an email when a fee is edited. I was hoping to use the "Subscriptions" option in SSRS but I'm not very familiar. Is there a way for it to send an email when a new entry is entered? or for it to look at the "LAST DATE EDITED" and when is today/now to send the new information?

ID       FEE NAME   FEE AMOUNT    EDITED BY          LAST DATE EDITED     
 1        MILK         5            JOHN         2019-3-15 13:59:22.110  
 2        WATER        3            JOSEPH       2019-3-12 16:55:30.710 
 3        SUGAR        8            DANIEL       2019-3-10 15:45:13.567

score 2 · Answer 1 · edited Mar 17 '19 at 20:54

2

Go to Report Server and select Subscriptions. Then choose the type to "Data-driven subscription".

The schedule should be report specific schedule.

You can add a parameter ExecutionDateTime and to choose it in Report Parameters under Subscription page.

edited Mar 17 '19 at 20:54

Dale K

25,246
15
42
71

answered Mar 17 '19 at 11:21

Ana Iuliana Tuhasu

237
1
6

Thank you for your response. – sergio089 Mar 17 '19 at 21:16

score 1 · Accepted Answer · answered Mar 17 '19 at 10:58

Perhaps, the closest SSRS native functionality is a Data-Driven Subscription.

The subscription can be set on a daily basis

The dataset of the report is to get data of the last 24 hours:

SELECT [ID]
,      [FEE NAME]
,      [FEE AMOUNT]
,      [EDITED BY]
,      [LAST DATE EDITED]  
FROM yourTable
WHERE [LAST DATE EDITED] > GETDATE()-1

Data-driven subscription dataset will trigger email only if there is at least a single row changed within the searched time range

SELECT DISTINCT 'someone@examle.com' as email 
FROM yourTable
WHERE [LAST DATE EDITED] > GETDATE()-1

Worth to mention, that such type of subscription available in the Enterprise edition only

If SSRS usage in not a requirement, a similar solution can be achieved using a combination of the update trigger and the "database mail".

Nice example: Send e-mail from a trigger

Thank you for the clear response Alexander. Yeah, I'm also investigating about triggers. That might be the right path because I would like to keep information about the old value and new value and I think I might be able to do it with a trigger. — sergio089, Mar 17 '19 at 21:15

SSRS subcription email with new entry

2 Answers2