Alteryx Tips Automatic Email Groups

06 Sep 2016

Alteryx How To: Automated Email Lists Using Alteryx

Weelin Lim

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:

Alteryx - Example Workflow

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.

Alteryx Recipient List Table

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.

Alteryx - Generate Report Recipient List

Configuration: ReportName

Here we have a Text Input tool Text Input Tool, which will determine which report we would like to generate.

Alteryx - Configuration ReportName

Data Input: Recipient List

Here, we use a Dynamic Input tool Dynamic Input Tool Alteryx, 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.

Alteryx- Data Input Recipient List

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 Summarize Tool Alteryx, 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.

Alteryx - Generate Recipient List

3. Generate Report section:

This part builds the report and will change for each report workflow. This is the bespoke part of the workflow.

Alteryx- Generate Report Section

4. Email Report:

This section is configured to append the concatenated recipient list to the report part needed to be emailed out.

Alteryx - Email Report

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.

Alteryx - Email Configuration

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.

Table Script:

CREATE TABLE[dbo].[ReportRecipient](

[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
(
[ReportRecipientID] ASC
)
) ON [PRIMARY]

GO

Weelin Lim

About the author

Head of Business Intelligence at Concentra. I am a Business Intelligence and Data Warehouse specialist with over 15 years’ experience in a variety of industries. I enjoy visualising freely available Government data and as a parent, I am particularly interested in Education data right now. My blog articles will (hopefully) be a mixture of business and technical experiences, as well as visualisations of interest to the wider public.

Related to Alteryx How To: Automated Email Lists Using Alteryx