Alteryx How To: Automated Email Lists Using Alteryx
It is quite a common requirement to email reports to set distribution lists. In this blog, I will show you how this can be easily done in Alteryx.
If you have never used Alteryx's Reporting tools before, I would recommend having a look. The tools currently allow you to generate standardised reports from your data in a simple manner and deliver them in a number of formats. They also allow you to email your reports to recipient lists. This is a great approach for building a repeatable reporting workflow that can be used again and again.
By developing a generic approach to the emailing requirement, you can take the reusable component further by allowing you to use the same process in multiple workflows.
My example workflow is shown below:
The working example consists of four parts:
1. Recipient List table – in my example, I use a database table (shown) that allows me to configure different report names and whether the report recipient is active or not.
The SQL script to generate the table is included at the end of the blog. You just need to run the script against a database that you want the table to be created in and insert your own values.
It would be equally viable to use a Text Input or a text file to maintain this list. I have chosen a database table specifically for ease of reuse across multiple developers and central maintenance capability, since we make good use of Aliases in our work to standardise database connections.
2. Report Recipient List generator - allows you to generate the recipient list for a specific ReportName.
This uses a Text Input to allow you to enter the Report Name in a single place, and a Dynamic Input to retrieve the list of recipients. Finally, the Summary tool generates the CSV list of Recipient email addresses. This is a reusable component of the workflow and can be used whenever the recipient list is required. It is possible to encapsulate this component into a Macro, but I have kept this as part of the workflow for ease of demonstration.
Here we have a Text Input tool , which will determine which report we would like to generate.
Data Input: Recipient List
Here, we use a Dynamic Input tool , which accepts an input from our previous tool. The dynamic input tool allows us to parameterise the query, based on the ReportName set in the Configuration: ReportName text input.
We replace the specific text in our query to only retrieve the active report subscriptions held within our database for the specific report required.
NOTE: You need to make sure that the Connection String defined in the Dynamic Input is pointing to the database table that you created to hold your recipient lists.
To do this, simply click on the "Edit" button, as shown above, and change the data source and database.
Generate Recipient List:
Here, we use a Summarize tool , to concatenate the list of email addresses, separated by a comma. This is the Recipient List that can then be used by the Email tool.
3. Generate Report section:
This part builds the report and will change for each report workflow. This is the bespoke part of the workflow.
4. Email Report:
This section is configured to append the concatenated recipient list to the report part needed to be emailed out.
The Email tool is simply configured to use the concatenated email recipient list in the To: field and the required report section in the body of the email.
You may need to configure SMTP, depending on whether you have a service that is detectable.
In summary, this is a very simple to implement Alteryx solution to a common requirement. The workflow and table script is available for download.
As always, comments and suggestions are welcome.
[ReportRecipientID] [int] IDENTITY(1,1) NOT NULL,
[ReportName] [varchar](50) NULL,
[RecipientName] [varchar](50) NULL,
[EmailAddress] [varchar](50) NULL,
[ActiveFlag] [bit] NOT NULL,
CONSTRAINT [PK_ReportRecipientID] PRIMARY KEY CLUSTERED
) ON [PRIMARY]