1 Overview

SQLMessenger can automatically convert SQL query results into HTML reports or Excel files and email them to specified recipients.

In this document, we will demonstrate how to configure a task that automatically queries and generates sales statistics reports for Washington DC from January 2024 to June 2024, and sends them to the regional manager.

Below is the email generated by SQLMessenger after automatic conversion.

An email generated by SQLMessenger

2 Create Task

In SQLMessenger's Task Manager, click the "New Task" button on the toolbar to open the Task Editor.

Click the "New Task" button to open the Task Editor

Select "HTML Email" for "Task Type"

3 Configure the Attachment Templates

(1) Add an attachment template to the task to store the sales statistics for the city. This statistics report, in Excel format, will be sent as an email attachment. Using Simple Tables

Add an attachment template to the task to store the sales statistics for the city

(2) Design an Excel template file with charts in Excel software, to be used for generating charts in the email body. Using Custom Excel Templates

Design an Excel template file with charts

(3) Create an attachment template of the "Customize Spreadsheet Template" type in the task. Import the designed Excel template file into the task and set the corresponding SQL query for the attachment template. Using SQL Query in Templates

Create an attachment template of the "Customize Spreadsheet Template" type in the task

After the task executes, only display the charts from this attachment in the body of the email, without sending the attachment files. Therefore, we need select the "Don't send data files generated by this template" option for the attachment template. Attachment Template Management

Select the "Don't send data files generated by this template" option for the attachment template

4 Configure the Email Body

(1) Write email body text. Variables can be used in the text to display different content.Using Variables in Tasks

The text content in the email body template

(2) Add SQL table to the body template. The system can convert SQL query results into an HTML table displayed in the email body. Using Simple Tables

The text content in the email body template

(3) Add chart to the body template.Inserting Charts Into HTML Body Template

Insert a chart into the email body template

Note: This feature requires Excel 2010 or a later version to be installed on your computer.

5 Previewing Task

After configuring the task, click the Preview button to preview the execution results of the task.

Click the "Preview" button to preview the execution results of the task

The email generated after the task execution

After verifying that the task execution results are correct, click the "Deploy" button to deploy the task configuration.

6 Send Reports and Attachments to Recipients Via Slack

You can also send the reports to recipients via Slack.

First, configure the Slack Channel in SQLMessenger. Click here to view How to Config Slack Channel

Then, in the recipient dialog of the task, select the corresponding Slack Member in "Send Via Slack" as the recipient. SQLMessenger will automatically choose the appropriate delivery method based on the recipient's type.

Select Slack members as recipients

Note: When sending messages via Slack API, the message body will be converted into a text message. If you want to preserve the format of the body, you can convert it into a PDF or image before sending.

Convert the message body into a PDF or image

7 Q&A

(1) Can I modify the SQL query conditions to retrieve sales reports for different cities?

Yes. You can use variables as query parameters in SQL to achieve retrieving sales reports for different cities. Using Variables in SQL Statements

(2) How should I proceed if I want to send the sales report for each city individually to the regional manager of that city?

You can use "Information Distribute Task" to achieve this function. Using Information Distribute Task

(3) Can the task be automatically executed at the time I set?

Yes. You can configure "Task Schedules" for the task, and the system will automatically execute the task according to the schedule you configured. Using Task Schedules

Using Custom Excel Templates, Using SQL Query in Templates, Using Task Schedules, Sending Messages to Slack Workspace Members via Slack API