Is there anyway to change the body of an email subscription everyday?

Question

I honestly don't think this is possible but I wanted to at least try. Everyday I have a report that is generated daily and sent out via SSRS email subscription to various people based on the transactions that I do in a proprietary program. Some days there are actually no transactions so it sends a blank excel file. I dont think it's supported natively so is there so way to hackily add the message "There were no transactions today" in the body of the email if there is no data in the excel file? and on days there is say something like "Transactions in attached Excel file"

So can someone please confirm. Is this possible? Or no?

Answer 1

Actually, this is natively possible and it is awesome! What you're looking for is a data driven subscription. You create one by going to the subscription tab under the management page for the report. There are two options: Create Subscription and Create Data-Driven Subscription. Click on the Create Data-Driven Subscription.

I apologize for the length from here but there is just so much to say about these!

I can't elaborate on the steps for everything here because it can be as in-depth a process to set up as you want it to be; there are a ton of options! Here's the MSDN article on Data-driven subscriptions, but you'll find it minimally helpful. Here's the TechNet Tutorial on how to create one, but I think you'll find that it doesn't delve as deep as you might like to go. I just learned everything by trial and error.

There's oh so much to say about these, but essentially you write a SQL query that evaluates something in your data and, as a result, gives the subscription different values for different variables, such as "Comments" (the body of the email written in html), "Include Report" (true/false), and "Render Format" to name a few.

It's important to note that for each line your query returns, one email will be sent. So if you wanted to send out three transaction report emails, you would want to make sure your query returns three rows with all the appropriate data.

For your own edification, here's a redacted copy of one of my queries that drives a report. You'll notice the comment field is quite long as it needs to be written in html. But SQL can handle the very long strings so long as you can formulate them correctly.

So, in your case, you would want to make Include_Report false when there are no transactions and then change the comment to a proper message about why no report is attached.

The purpose of this query is to look for server issues and if issues are found, send out an email (without the report attached) that tells end users it will be coming later. (I'm not in charge of the server performance and the people that are frequently don't fix it in time).

You'll notice that I have a field for every input variable into the SSRS subscription. This way, I can control how the report is emailed based on any script I can dream up. It also makes setup a little easier. I've also built in a quick way to test the subscription so I can play with it without it going to End Users and changing it to go out to end users takes seconds.

/*********************************************************/
/*     Change @Testing to 'TEST' to have all emails      */
/*               sent to Christopher Brown               */
/*********************************************************/
/*     Change @Testing to 'PROD' to have all emails      */
/*             sent to normal recipients.                */
/*********************************************************/

Declare @Testing varchar(4) = 'TEST';

With Problems as (

/*Script that looks for hardware failures or anything that would
 cripple the accuracy of the report.*/

)

Select Case
        When @Testing = 'TEST'
        Then 'Just.Me@work.com'
            When @Testing = 'PROD'
            Then 'End.Users@work.com'
        Else 'Just.Me@work.com' 
        End as 'To'
    , Case
        When @Testing = 'TEST'
        Then 'Just.Me@work.com'
            When @Testing = 'PROD'
            Then 'IT.Group@work.com'
        Else 'Just.Me@work.com' 
        End as 'CC'
    , '' as 'BCC'
    , 'Just.Me@work.com' as 'Reply-To'
    , Case
        When @Testing = 'TEST'
            Then '***TEST***'
            Else ''
        End +
        Case
        When /*Problems Indicated*/
            Then '@ReportName - Report Delayed'
            Else '@ReportName for ' + CONVERT(varchar(10),getdate(),101)
        End as 'Subject'
    , 'Normal' as 'Priority'
    , Case
        When /*Problems Indicated*/
            Then 'False'
            Else 'True'
        End as 'Include_Report'
    , 'PDF' as 'Render_Format'
    , Case
        When /*Problems Indicated*/
            Then 'High'
            Else 'Normal'
        End as Priority
    , 'false' as 'Include_Link'
    , Case 
        When /*Problems Indicated*/
            Then '<html><Body><Font Face="Constantia","Times New Roman"><p>This Report could not be created at this time. We will send out an updated Report once the server issues have been resolved. If you have questions, please contact us.</p></Font></body></html>'
            Else '<html><Body><Font Face="Constantia","Times New Roman"><p>Attached is the Report. When the report is run on a Monday, it does one thing.</p><p>Every other weekday, the report does something slightly different. Please note that the report is scheduled to run every weekday, Monday through Friday, regardless of holiday schedules.</p><p>If you have questions about the report, please contact us.</p><p>If the attached report is empty or missing vital information, click <a href="mailto:IT.Group@work.com?Subject=Problem%20with%20Report">here</a> to notify us.</p></Font></body></html>'
        End as 'Comment'

From Problems