Sending emails conditionally based on totals in SSRS reports

Question

We have an SSRS reports like below

CompanyA

Currency Amount
GBP 100
USD 70
Total <> in GBP

CompanyB

Currency Amount
GBP 200
USD 10
Total <> in GBP
..
..
..

With many comapnies and if the Total is below some value then we need to email respective company representatives.
What is the easiest way to achieve this ?

Answer 1

If you have Enterprise Edition, then you can use the feature Data-Driven Subscriptions

Otherwise you have to modify the subscription related SQL Server Agent job to send the mail only when the condition in mind is fulfilled.

Answer 2

Hi @swanand bagve ,
As Olaf said, driving subscriptions is a good choice.
Please create one table in the database to store the subscribers, such as Tablesubscribers. This table has two columns: Actualsubscribers and Nullsubscribers.
In the Data-driven, please reference below T-SQL query.

if (condition) ---{Example:You want the total is below some value}  
select Actualsubscribers as subscribers from Tablesubscribers  
else  
select Nullsubscribers as subscribers from Tablesubscribers.  

When the subscribers is NULL, the subscription status will be like “Done: 1 processed of 1 total; 1 errors.”, but it will not affect the other following delivery action.

Additionally, there is another one work-around: you can replace the NULL value with one uncorrelated subscriber, such as your email account. However, if you using one invalid email account or un-existing email account, such as: “abcdefg” or aaaaaaa@Karima ben .com, the status will be “Done: 1 processed of 1 total; 0 errors.”. But you will receive one Undeliverable mail from you smtp server.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.