• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Blog
  • Power Platform
    • Power Apps
    • Power Automate
    • Power BI
    • Power Virtual Agents
  • Dynamics 365
  • Azure
  • About Me
Joe Gill Logo

Joe Gill

Microsoft MVP - Power Platform Consultant

Home » SQL » Reporting Services – Data -Driven Subscription

Reporting Services – Data -Driven Subscription

22nd October 2009 by Joe Gill Leave a Comment

 In Reporting Services data driven subscriptions are used to run a query on a scheduled basis which returns a list of recipients to receive a report.  What I have tried to do here is to show a simple example which will use this functionality to monitor an application and email out a report when any alerts that have been triggered.

 
You need the enterprise version of reporting services to use data driven subscriptions. In addition SQLAgent needs to be running and the data source must use stored credentials.
 

The example will use a simple table to hold details any events that need to be reported on. The application will insert rows into this table for each event that needs reporting on

create table ReportTriggers(
    TriggerID int IDENTITY(1,1) NOT NULL,
    TriggerDate smalldatetime NULL,
    TriggerDescription varchar(50) NOT NULL
)

 
The stored procedure prGetReportTriggers will be queried and it will only populate the email recipient list if new records have been added to the ReportTriggers table that have not already been reported based on the fact that the triggerdate is null
 

Create procedure prGetReportTriggers
as
begin
    if (select COUNT(*) from ReportTriggers
        where TriggerDate is null) > 0
        begin
                select ‘yourname@yourdomain.com’ as Email
                update ReportTriggers set TriggerDate = getdate()
                where TriggerDate is null
        end
                select NULL as Email
end       
        

The report that will be called by the subscription should use the following SQL to get those records that have just been updated by the prGetReportTriggers stored procedure.

select * from ReportTriggers
where TriggerDate = (select max(TriggerDate) from ReportTriggers)

 
Follow the following basic steps to create the data driven subscription 

Reporting Services - Data -Driven Subscription Joe Gill Dynamics 365 Consultant

 
Call the stored procedure 

Reporting Services - Data -Driven Subscription Joe Gill Dynamics 365 Consultant

Populate the To value with the returned email list

Reporting Services - Data -Driven Subscription Joe Gill Dynamics 365 Consultant

Set up the subscrption schedule to run as frequently as you require

Share This On Social:
  • Tweet
  • Reporting Services - Data -Driven Subscription Joe Gill Dynamics 365 Consultant

Filed Under: SQL

Reader Interactions

Leave a Reply Cancel reply

You must be logged in to post a comment.

Primary Sidebar

Popular Categories

  • Power Platform
    • Power Apps
    • Power Automate
    • Power Virtual Agents
  • Azure
    • Logic Apps
  • Dynamics 365
  • .NET
  • AI
  • SQL

More to See

Power Platform Requests – Base Request Capacity

17th December 2020 By Joe Gill

Power Automate Desktop

Power Automate Desktop – UI Flow

26th November 2020 By Joe Gill

extract table from pdf and write as csv file using Power Automate Desktop

Extract Tables from a PDF using Power Automate Desktop

23rd May 2022 By Joe Gill

Dataverse Anonymization

25th April 2022 By Joe Gill

Tweets

Footer

Joe Gill

Microsoft Business Applications MVP – Power Platform, Dynamics 365 and Azure.

An architect with over twenty years experience designing and developing technology solutions. Specializing in the Microsoft technology stack including Power Platform, Dynamics 365 and Azure. Microsoft MVP Profile

Connect on Social

Useful Links

  • Home
  • Blog
  • About Joe Gill
  • Power Platform
  • Dynamics 365
  • Azure

Featured Posts

Power Platform Requests – Base Request Capacity

Power Automate Desktop – UI Flow

Synapse Link for Dataverse – Metadata

Extract Tables from a PDF using Power Automate Desktop

Dataverse Anonymization

Dataverse Metadata in Synapse

© 2022 · Joe Gill