How to send email at specific time interval in SSRS? [SOLVED]

SQL Server Reporting Services (SSRS) is a powerful reporting tool that allows users to create, manage, and deliver a wide range of reports. One common requirement in SSRS is the ability to send reports to recipients via email at specific time intervals. This feature is particularly useful for organizations that require regular reports, such as weekly or monthly reports.

In this guide, we will explore how to send email at specific time intervals in SSRS. We will discuss the steps involved in setting up email delivery in SSRS and configuring a report to be delivered via email at a specific time. We will also cover some best practices and tips to help you get the most out of this feature. Whether you are new to SSRS or a seasoned user, this guide will provide you with the knowledge you need to automate report delivery via email.

 

Overview of SSRS tool of SQL

The reporting tool known as SSRS, or SQL Server Reporting Services, enables you to create structured reports with tables in the form of data, graphs, pictures, and charts. These reports are kept on a server that can be used whenever with user-defined settings. It belongs to the Microsoft SQL Server Services collection.

The ability to build reports with dynamic information based on user input and support for custom data visualizations are a few of SSRS's standout features.

In addition, SSRS provides a number of delivery methods, such as email, SharePoint integration, and report scheduling.

 

What are SSRS reports used for?

• Business intelligence reporting: SSRS reports can be used to build interactive and analytical reports that aid business analysts in deriving meaningful conclusions from data analysis.
• Financial Reporting: With SSRS reports, financial reports including balance sheets, income statements, and cash flow statements can be produced.
• Performance Reporting: Performance reports that track an organization's or department's performance over time can be made using SSRS reports.
• Operational Reporting: SSRS reports can be used to produce operational reports that assist in keeping tabs on an organization's day-to-day business activities.
• Reporting on Compliance: SSRS reports can be used to produce compliance reports that help to make sure a company is adhering to legal standards.

 

Types of SSRS reports

Following types are just a few examples of the types of reports that can be created with SSRS. The type of report that is best suited for a particular situation will depend on the requirements of the report and the data being analyzed.

• Table Report: The most basic SSRS report type, a table report shows data in a tabular style. It is helpful for structuring and displaying enormous volumes of data.
• Matrix Report: An alternative to a table report, a matrix report can provide summarised data because it supports dynamic row and column grouping.
• Report with a Chart: A report with a chart is used to present data graphically. Bar charts, line charts, and pie charts are just a few of the many chart kinds supported by SSRS.
• Sub-report: A report that is nested within of another report is referred to as a sub-report. It can be used to display linked facts and build more intricate reports.
• Drilldown Report: An additional piece of information can be revealed by clicking on a data point in a drilldown report. It is handy for displaying data at multiple levels of detail.
• Parameterized Report: Report with Parameters: A parameterized report enables users to enter filters for the data it displays, such as date ranges, client names, or product categories.
• Linked Report:  A report that is connected to a parent report is referred to as a linked report. It is helpful for constructing reports that contain similar components, like headers and footers.
• Mobile Report: Reports that are designed specifically for viewing on mobile devices are known as mobile reports. Usually, it has interactive elements like touch-based navigation.

 

How to distribute a SSRS report by e-mail

Following are the two steps to distribute SSRS report by email.

1) Configure the SQL Server Reporting Services (SSRS) Report with data source

2) Configure Report server for email delivery either a standard subscription or a data-driven subscription for e-mail delivery

 

Configure the SQL Server Reporting Services (SSRS) Report with data source

To create SSRS report we have used Microsoft visual studio 2019

Create a report in SQL Server Reporting Services (SSRS) and configure it to display the data you want to include in the email.

Create a new Project Report Server Project Wizard

---

Configure the project using Report Wizard

---

Select the data source to display the data you want to include in the email

---

Edit the data Source and Configure with the database of which data you want to display in SQL Server Reporting Services (SSRS) Report

---

Click on query Builder to make query to fetch the data from table.

---

Design the query and using query builder to fetch data

---

---

Select the Report Type

---

Design Report and also if you want to display data in group based on any column click Group

---

Give a name to report, check report summary and see the preview.

---

Finish the Wizard and see the result of report in design view.

---

Preview the final resulting report in preview view.

 

Setting up Email Delivery for SSRS Reports

In SQL Server Reporting Services (SSRS), a subscription is a configuration that allows a report to be automatically generated and delivered to a specified destination, such as a file share, SharePoint library, or email address. There are two types of subscriptions available in SSRS: standard subscriptions and data-driven subscriptions.

A standard subscription is a basic subscription that allows users to create a schedule for the report to be generated and delivered via email to a specific recipient or a group of recipients. With a standard subscription, the recipient list and other subscription settings are fixed, meaning they do not change based on any data conditions.

On the other hand, a data-driven subscription is a more advanced subscription that allows users to create dynamic subscription settings based on data conditions. For example, a data-driven subscription could be used to automatically send different reports to different recipients based on their roles or departments within an organization. Data-driven subscriptions require users to create and configure a data source and a dataset to retrieve the subscription settings.

Both standard subscriptions and data-driven subscriptions can be configured for email delivery in SSRS. The main difference between the two is that data-driven subscriptions offer more flexibility and customization options, while standard subscriptions are simpler to set up and manage.

Following are the steps to configure Report Server for email delivery either a standard subscription or a data-driven subscription for e-mail delivery

 

Step 1: Open and Configure Report server configuration manager

---

Configure Web Service URL

---

Configure Report Server database by clicking on the change database option as given below

---

Configure the URL for Web portal where you have access of all reports. Home - SQL Server 2019 Reporting Services

---

Deploy the Report on web port URL.

---

Run the Report on browser and preview the Report result on Browser window

---

Open SQL Server Reporting service web portal by click on web-port URL

---

Click on top right corner of the report and select manage option.

---

We will get multiple option to manage the report , click on subscription option available on left panel

---

Click on subscription and add new subscription

---

Configure new standard subscription

---

Schedule delivery of report hourly by clicking on Edit Schedule

---

Schedule the delivery start and end date also the time of delivery

---

Click on Apply button to apply all schedule configuration settings

---

Once you click on apply button, it will ask to configure destination method

---

If you did not configure email settings in report server configuration then you will see only window file share as option in destination

Configure email settings

---

Once you configure email setting, you can have option of email in destination of subscription

Configure delivery option of email as per your requirement, specify receiver email address in To, Specify other receivers email address in cc or bcc as per requirement, also specify subject which will be sent as email subject.

 

Summary

SQL Server Reporting Services (SSRS) allows users to send reports via email at specific time intervals, including hourly intervals between certain hours. This is done by setting up a subscription that specifies the delivery schedule and destination for the report. In SSRS, there are two types of subscriptions: standard and data-driven subscriptions. Standard subscriptions allow for a fixed recipient list and subscription settings, while data-driven subscriptions offer more dynamic and customizable options based on data conditions. To send emails hourly between certain hours, users can configure a subscription with a schedule that specifies the start and end times, as well as the interval for report delivery. SSRS provides a flexible and powerful solution for automating report delivery via email, which can be a valuable time-saver for organizations that require regular reports.

 

Read More

Tutorial: How to Locate and Start Reporting Services Tools (SSRS)