Reposted from https://www.sqlmessenger.com/docreader.html?id=555
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.
- 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 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.
You can also send these reports via Slack. Simply select the members of your Slack workspace as recipients when configuring the task.
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.
Step 2: Write the body template for the task and add the sales summary table into the body template.
- In the body, right-click the location where you want to display the sales summary table, then click the "Insert SQL Table" menu item.
- In the table wizard, enter the SQL query and select the fields to be displayed in the table.
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.
Step 3: Add an attachment template to the task for sending the sales reports of each city.
- 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.
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.
Top comments (0)