Q: I am a database administrator. I need to send some reports to my colleagues every week. I want to automate this process. How should I do it?

Specifically, I would like to send the following report every Monday and on the last day of each month.

In the body of the email, a summary table of the sales for a specific region should be displayed. Additionally, the sales reports for each city should be included as attachments to the email.

A: Yes, SQLMessenger can help you easily achieve these functions. Here are the detailed steps.

Step 1: Create a task in SQLMessenger and set the email subject and recipients for it.

Create a task in SQLMessenger

Set "Sales Performance Report for Your Region (#@@Date#)" as the email subject of the task.

Tips: "#@@Date#" is a system variable, indicating that the system should display the date when the task is executed in this place.

Set the email subject for the task

Set recipients for the task.

Tips: If the recipient is not in the list, you can add the recipient's email address to the address book in SQLMessenger first and then select the address in the task configuration.

Set recipients for the task

Setting Recipients for Task

You can also send these reports via Slack. Simply select the members of your Slack workspace as recipients when configuring the task.

Send reports via Slack API

Sending Messages to Slack Workspace Members via Slack API

Note: When sending reports via the Slack API, the email body will be converted into a text message and cannot display table content. However, attachment files can be sent normally. To preserve the formatting of the email body, you can choose to convert the email body in PDF format.

Convert the email body in PDF format

Step 2: Write the body template for the task and add the sales summary table into the body template.

Write the body template for the task

In the body, right-click the location where you want to display the sales summary table, then click the "Insert SQL Table" menu item.

Right-click the location where you want to insert the table and click "Insert SQL Table"

In the table wizard, enter the SQL query to retrieve the data and select the fields to be displayed in the table.

Enter the SQL query to retrieve the data

Select the fields to be displayed in the table

Set the display style of the fields

After the table is added, the system will display a table icon at the specified location. Double-click this icon to modify the SQL query and display style of the table.

The icon of the table

Step 3: Add an attachment template to the task for sending the sales reports of each city.

Add an attachment template to the task

Set the attachment template name, attachment type, and attachment file name. Then click "New Query" to set the SQL query for the attachment.

Tips: There is no need to add a file extension to the attachment file name; the system will automatically add it.

Set the attachment template name, attachment type, and attachment file name

Enter the SQL query for the attachment template.

Enter the SQL query for the attachment template

Select the fields to be displayed in the attachment file

Set the style of the attachment table

After completing the settings, click "OK" to return to the task editor.

Step 4: Add schedules for the task.

This task requires two schedules. The first is set to run at 4:00 PM every Monday. The second is set to run at 4:00 PM on the last day of each month.

Schedule 1: Run the task every Monday at 4:00 PM

Schedule 2: Run the task at 4:00 PM on the last day of each month.

After the task configuration is completed, click the "Deploy" button to activate the new task settings. The system will then execute the task every Monday and on the last day of each month, sending the report to the specified recipients.

Q & A

Q: Can the system send each region's report separately to the manager of that region?

A: Yes. Please refer to another example in SQLMessenger: Demo Task - Sales Report Distribution.

Q: Can the query conditions in the SQL statement vary based on the region or time? For example, query the report for February in February and the report for March in March.

A: Yes. You can use variables in the SQL statement to adjust the query conditions. Please refer to the SQLMessenger manual: Using Variables in SQL Statements.