cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
husss Regular Visitor
Regular Visitor

Power BI Report Server SSRS Subscription

Hello,

 

I have a SSRS report and I imported it to PBIRS. I have arranged e-mail subscriptions and that is ok.

 

My problem is, sometimes my report returns no result and I do not want PBIRS to send any e mail. Now, it sends e-mail with no result, just empty colons.

 

How can I handle this?

 

Best regards.

4 REPLIES 4
philipplenz Established Member
Established Member

Re: Power BI Report Server SSRS Subscription

You can create a data driven subscription. In the query where you select  the data, you can make the check if the report should send. If not, the row is not in the resultset.

husss Regular Visitor
Regular Visitor

Re: Power BI Report Server SSRS Subscription

Hello @philipplenz,

 

Can you give more detail? For instance, my mail query is below:

 

if (select COUNT(*) from MIX.rapor.vIkMailRaporu) > 1

begin

 

 

select

'mail adresses of my company'

as Mail

 

end

 

else begin

select ' ' as Mail

end;

 

 where should I make this check? I want PBIRS not to send e mail if my report has no result. 

 

Best regards.

em172967 Frequent Visitor
Frequent Visitor

Re: Power BI Report Server SSRS Subscription

You can only use the data driven subscription if the PBIRS instance is on an enterprise license.

 

If it is not, you can stop the schedule in PBIRS but still leave the job intact so you have the To: , cc: etc and get the nice report server formatting.

 

Then from the SQL Agent create you own scheduled job that calls the PBIRS job. You'll need to look up the SQLAgent guid.

You can use this and change 'YOUR REPORT NAME%' accordingly:

use ReportServer
go
select S.ScheduleID as SQLAgent_Job_Name
	,SUB.Description as Sub_Desc
	,SUB.DeliveryExtension as Sub_Del_Extension
	,C.name as ReportName
	,C.Path as ReportPath
	,SUB.LastStatus
	,SUB.LastRunTime
	FROM ReportSchedule RS
inner join Schedule S on (RS.ScheduleID = S.ScheduleID)
inner join Subscriptions SUB on (RS.SubscriptionID = SUB.SubscriptionID)
left join [Catalog] C on (
		RS.ReportID = C.ItemID
		and SUB.Report_OID = C.ItemID
		)
where C.name like '%YOUR REPORT NAME%' --Enter Report Name to find Job_Name
ORDER BY SUB.LastRunTime desc

Pseudo code for the agent job would be like

 

If @rows > 0

Begin

USE msdb EXEC sp_start_job @Job_name = 'A00E3318-9D97-45E8-8476-526118CA62B1' /*Enter SQLAgent_Job_Name for PBIRS job*/

End

 

Else

Return /* nothing to do */

 

philipplenz Established Member
Established Member

Re: Power BI Report Server SSRS Subscription

Additionally, if you have a PBI RS you have the Enterprise Edition. So you have the option to create data driven subscriptions.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 20 members 1,149 guests
Please welcome our newest community members: