I’ve searched for an answer to this question both on this board and through google, but can’t seem to phrase my search correctly to get the desired results. I’m trying to determine if it’s possible to display a specific message in the email body of a scheduled report when it has blank results. I’ve seen some solutions for making a report fail and not sending it at all when it’s blank, but the users still want to get the email every morning - they just don’t want to open the excel file to find out if there are results.
For example, when the report is not blank, they want the email to say, “The attached report will show the restricted customers with downloads in the last three days.” When it’s blank, it should say, “There have been no downloads by restricted customers in the last three days.”
If this question has been answered elsewhere on the board, I apologize - my searches kept dropping my keywords (such as “message,” “email,” etc) because they’re too common!
You can’t really insert dynamic text into the email body of a scheduled report. You could probably do something with a Publication using the mHTML option though, which exposes a Report into the Email Body. Just configure another report tab to include a conditional message based on the number of records returned, and expose this to the Email Body in the Publication using mHTML.
Perfect, thank you for confirming that the message idea wouldn’t work - I’ll pass that along to my manager.
I’ve never worked with Publications before, but it seems pretty easy to set up. Unfortunately, security is locked down very tightly here and it doesn’t look like I have rights to set that up; after I get it scheduled, it returns errors saying that I don’t have the clearance to make changes. I’m checking with our DBA to see if it’s something his team can set up for us (being that they’re the admins), but if nothing else it’ll be useful to carry through to future clients!
In your case you can use the Notification feature of the schedule.
Use the technique to force an empty report to fail that you have researched.
When the report works the email body will be whatever you type into the email.
But in the fail notification - send an email to your users with the message that no data was generated. They will receive an email without an Excel telling them that there is no data.
It would be perfect if I could use that feature…unfortunately, I looked in the scheduling menu and Notification wasn’t an option. Is that something the administrator needs to enable?
Thanks for your guidance! I don’t currently have access to the CMC, but I’ve requested it from our technology team; I’ll post an update when I’m able to test this to close this question.
I was granted access to the CMC, and was running into issues getting my report to force-fail since the query was returning results (and those results were getting filtered at the report level).
I modified my query with some custom SQL to get the query itself to return no results, then used the solution provided in this thread to force the fail.
I was able to successfully test everything together in our Dev environment, and acheived the desired result. Thanks to everyone for the guidance!
(BOB member since 2010-09-22)
(BOB member since 2008-10-09)