Avoid to send scheduled report with no data

[PowerSchillo87]

Hello, I have a paginated report published on Premium Workspace on Power BI Service.

We have scheduled some reports filtered by one parameter.

If for a parameter, there is no data, can I avoid to send the e-mail message to destination?

Thanks

Riccardo

[StefanQ]

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

[StevenJacobs]

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.

[Power-CJ]

Hello

thank u for reply. Sadly In my case I'm using a Power Bi Dataset as Datasource.

Is there no Way with Power Automate to check the content of tzhe PDF Attachement bevor sending a Mail- 

[StefanQ]

mmm... power automate, not a lot of control there =[

in power query though you could have a step the does a row count and if zero then throw an error. if there is no create error function then try 1/0 to create a divide by zero error

[Power-CJ]

Hello,

is there maybe now a solution.

I made an alerting Reporting, in which will be maybe once a week data, but every evening the report will be delivered.

I will avoid to open 80% of the days an empty report.

Best Regards

Chris 

[skiwii]

This is an inherit problem with its SSRS underpinnings.  The only way I've avoided this in the past is to throw an SQL error so the subscription fails.  To do this, insert your data into a temp table, and if the row count =0 raise an error.

Ugly but it works.

[v-xiaoyan-msft]

Hi@PowerSchillo87,

I'm afraid this is not possible with Power BI paginated reports ,there are some  considerations for paginated report subscriptions.

If your report author has set expression-based parameters (for example, the default is always today's date), the subscription uses that as the default value. You can change other parameter values and choose to use current values, but unless you explicitly change that value as well, the subscription uses the expression-based parameter.

There is no After Data Refresh option for frequency with paginated reports. You always get the latest values from the underlying data source.

For more information，you can refer to this：Paginated-reports-subscriptions 

Hope it helps.

Best Regards,

Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.