Forum Discussion
Avoid to send scheduled report with no data
This is how to do it in SQL server, not elegant but it works
--Insert data into a temp table or temp variable. If the query was simple or low cost then test if it returns data
select *
into #myReportTempTable
from BlahBlah
if exists(select top 1 * from #myReportTempTable)
begin
--return the data
select * from @items
End
else
begin
--THROW AN ERROR IF THERE IS NO DATA
RAISERROR (N'There is no data to match your criteria, please change your criteria and try again. Note this is NOT an error.', 16, 1)
end
After following StefanQ's instructions to return an error from SQL server, I triggered the subsctiption by clicking "run now" button. I expected no email to be sent, but instead received this email (see below). Within the body of the email, I highlighted where the error message from the query is passed into the message, which seems nice, but certainly does not achieve the goal of avoiding sending an email when the report contains no data.
I've solved this entire issue with Power Automate, but that technique is much more cumbersome to maintain, especially if I need to modify recipients and/or filter criteria. In Power Automate, I first check the if the query will return any results. If yes, then I run the paginated report and forward results to the recipients. I do not use the subcription manager for my solution.
If anyone knows how to avoid sending an email if the query returns no content, using only Power BI Paginated Reports, please reply! Thanks!
This is the email I received after changing the data source to return an error when the query returned no results:
-----------------------------------------------------------------------------------------------------------------------
Subject: There was an error with your subscription {report name} and it could not be delivered.
Body: There was an error with your subscription {report name} and it could not be delivered.
DataSource_UserDefined_Gateway_ErrorDataSource_Sql_-2146233088
A data source used by this report returned an error. If you continue to experience this error contact the report author or data source owner. SqlException encountered while accessing the target data source There is no data to match your criteria, please change your criteria and try again. Note this is NOT an error. Learn more: https://aka.ms/pbiservicegatewaylearnmore.