This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Alteryx defaults to using the US/English Standard when it comes to number formats. However, for reporting purposes, it is important to remember that not all countries report their numbers in the same fashion. This article shows a quick and easy way to use Raw PCXML to convert numbers in to the Continental European Standard before outputting a final report. Throughout the workflow building process, numbers will be represented in the US/English Standard of 1,000.00. However, when building an automated report, it is important to remember who the audience will be. In the case of users in countries that use the Continental European Standard, it may best to have Alteryx change the numerical formatting system before outputting the final report. The following example is specific for the Spanish-Spain numbering convention. Process
1. Pass the data through a Table tool to create a Table Report Snippet. 2. Insert a Report Text Tool and format as seen below. The LocaleID is what is specifically driving the formatting change. For more information on other locale ID's check out this article. 3. Complete your layout and use a Render tool to complete your automated report. Please see the attached workflow for an example in practice.
Here at Alteryx we believe in working smart, not hard. Building out reports to highlight business-critical metrics is a pretty smart way to track goals. Customizing those reports to everyone in the department, then distributing them as attachments to individual emails? That sounds like a lot of hard work. Scheduling those reports from a refreshing data source each month so you don’t have to remake or rerun the reports yourself - that’s genius. Logging into your work computer to open up Alteryx, then having to check the scheduled results before having any peace of mind those reports were delivered without a hitch? Hard.
The Email tool is one of the critical last steps in putting that status report on cruise control. As of 2019.3, you can send those reports out through any SMTP server that requires credentials, with Gmail being one of the most popular. To ensure you can connect to your corporate Gmail account, we want to provide you with a step-by-step guide to be successful.
Alteryx Designer Version 2019.3
Once you drop the Email tool onto the canvas you will be able to configure it. Let’s dissect that configuration below:
For Gmail, connect to smtp.gmail.com on port 465, if you're using SSL. Connect on port 587 if you're using TLS. Another example would be Yahoo - smtp.mail.yahoo.com
Encryption method (if required)
It is HIGHLY likely you will need to complete this step with SSL/TLS being the most widely used.
Username and Password
Username can include or not include the @domain part of email address
Configure the tool as usual by adding recipients of your analysis, a subject, etc. If you want more detail on configuring this tool please see this article.
You might need to go to your email account to configure some additional settings as security is an imperative for every company and your SMTP email provider likely has some guardrails against letting other applications send an email through their server. Gmail, for example, requires you to change your account settings but you can do this easily using this link, or go to myaccount.google.com/security and scroll down to the option: “Less secure app access”.
Turn on “Less secure app access” (see below screenshots). Go back to Alteryx and send a test email to ensure it works. Then, kick your feet up and put those reports on cruise control.
And of course, if you need additional help, please visit our help documentation, reach out to a customer support representative, or start a conversation with a peer through Community. I found the information about Gmail SMTP server at this Google support thread: https://support.google.com/a/answer/176600.
You know that a huge part of conveying information to your audience is your visual presentation. Here's a way to increase the amount of information that can be shared with just a glance: Segment the data in a chart with color.
The Report Map Tool (master it here) allows to create thematic maps by selecting a "Theme" field on the Data tab and then further defining the theme on the Layers tab, for example:
The above example creates a map of Texas showing average annual rainfall totals where orange is the least rainfall and blue the most:
Pretty nice, right? But what if you want to change the map and instead of applying the theme to the fill color for the polygons, you want to apply the theme to the outline and just show that?
That is a little trickier because the Report Map Tool allows you to adjust the outline color and size of your polygons, it doesn't automatically apply the theme, so a workaround needs to be built.
You could feed in each polygon as an individual layer but that is difficult to manage - to keep the color gradient consistent, making sure they are in the right order. And what if a new rainfall range is introduced? You might have to adjust a couple of layers to account for it.
A better option would be to turn the polygon outlines into line polygons themselves. That would allow you to apply a theme right to the outline polygons.
In order to do this, we will use the following tools:
A RecordID is assigned so that we can pull the data apart and put it back together again.
The polygons are split into detailed regions using the Poly-Split tool and rows flagged as 'holes' are removed.
The polygons are split into points.
Those points are reassembled as a sequence polyline. The create the polyline, the data is grouped by the RecordID to keep each polyline separate. (A polyline contains multiple line segments, where a line has one start and one endpoint, but can have any number of points in between. A polyline can be quite complex as in the case of road systems, or rivers.)
The sequence polylines are joined back to the original data set.
Using the reporting tools to create the maps with rainfall range as the thematic field.
With that workaround you can create a map that looks like this:
For details on tool configurations, see the attached workflow.
Let's start with the basics of how to create a report map in Alteryx. To start off, ensure that the layers you want to show in your map have a spatial object field. This can be checked by placing a select tool and confirming that there is a column of type 'SpatialObj.'
The attached Alteryx Workflow takes the color schemes from www.ColorBrewer2.org and adds them to a new XML file to be utilized as the ReportSettings.xml file installed with Alteryx. You can utilize the new color palettes under the Report Map tools. Note that these palettes will not be added to the Interactive Chart or Insight tools.
The Report Map tool allows the user to define theme settings/ranges and to modify the size, icon, and color of the display for each range, and this can be done rather easily. First, in the Map tool on the Data tab, pick which column you would like to theme off in the Thematic Field selection area: Once this column is selected, go to the Layers tab in the Map tool and expand the layer options for your theme layer. Click on the Theme and options will appear on the right. For the purpose of defining your own theme settings, you will want the Tile Method to be on Unique Value, which gives the Specific Values area. The Specific Values area is where you list what you want to theme on. For this example, we are theming off the DMA_Name so you would enter each of the DMA names you would like to theme. If you have a lot of ranges, you could also use a Summarzie tool in your module and Group By your theme column, thus giving you a list of your theme values. Run the module once to populate the Browse tool and you can then click and hold on the first row and drag down to the row of your choosing, selecting them all. Ctrl+C will copy the rows and you can paste them into the Specific Values area using Ctrl+V Once the values are entered, click on Refresh and a layer option will show up for each of the theme values you set. Now that the theme values are layers, you can go to the Style option under each layer and change the Point Style , bring in a Custom Point , change the Size , Color , modify the Outline Color and Outline Size . If you don't define all the values that are contained in the data you are bringing thru, the Map tool also provides options on what to do with these. This can also be done with number ranges with a few small changes. For Tile Method , choose Manual Tile . Enter in the cutoff for each range that you would like to be able to theme. Hit Refresh and the new layers for the theme ranges will be displayed, allowing you to modify each one. Also note that layers are created for the ranges below and above what you specify in the Cutoff Values area.
The Email Tool is a tremendously useful shortcut when it comes time to disseminate your analyses and other results straight from your workflow. However, in order to do so, it must communicate using Simple Mail Transfer Protocol (SMTP), which is often restricted by IT infrastructure and firewalls to protect organizations from spam. As a result, many users excited to try the tool get the direct, yet demoralizing, error below (among others):
That’s why we’ve detailed in this article the steps you can take to investigate what, exactly, is giving you trouble:
Manually-entered SMTP server
First make sure a colon and port number are appended to the server name:
Does this SMTP server use SSL/TLS or require username/password authentication?
Unless the SMTP server uses windows authentication you won’t be able to use the Email Tool, as SSL and TLS are not yet supported through the tool. You can, however, look into other approaches to sending emails in the Designer that can accommodate those requirements.
If not, do you have the required ports open in your network firewall? You can check with your IT team for port numbers and statuses, but the default ports you can check yourself are usually 25, 445, 465, 587, and 993:
You can check to see if a server and port are open using the Telnet utility; if you have Telnet installed, open the command prompt and simply type telnet.
If you do not see the second prompt above then you’ll have to install a Telnet/SSH third party client like PuTTY.
From either the Telnet prompt or client, you can open a connection to the server and port to test:
In Telnet, connect to the server and port using the command below.
In PuTTY, opening the port will look like the following.
Either approach will then send you to the following prompt.
Then use these commands ( <CRLF> is the enter key ) to send a test email that, if received, will indicate that your port is open.
HELO <CRLF> mail from:<mail from address> <CRLF> rcpt to:<mail to address> <CRLF> data< CRLF> subject:<email subject here><CRLF> <CRLF> <type the body of the message here> <CRLF> . <CRLF>
To send the email, you must end the body by hitting the enter key (<CRLF>), then period, then enter again (please note that after specifying your subject you must also press the enter key twice – not doing may neglect the message body argument). The test should look something like the below:
If the email sends and the mail to address confirms receipt, then your port is open. Otherwise, you should receive an error that should help your IT team diagnose why the traffic is being blocked.
Use the steps above to determine likely causes for the error and you’ll be able to take steps to get the Email Tool unrestricted in your network. Once that happens, bid adieu to whatever repetitious emails you might have to send in the future!
Have you had to create a report for a large dataset that, while having many rows, only has a few columns? If yes, then you are familiar with the following look:
A narrow strip of data and a lot of whitespace. This does not make for the nicest looking report, nor is it the best use of space and paper. With a few tools and utilizing some basic math functions, we can create a report that looks more like this:
The records in this report span across the page in multiple groups of columns. So, how is this done?
Here are the steps:
Step 1 - Add a Record ID tool after your data input
Step 2 - Add a Formula tool
Here we will create a PageID column by using the Ceil function and dividing the RecordID by the number of rows that will fit on the page. In this example, the maximum number of rows that will fit given the font size, layout and margins used is 42. So, for as long as the PageID is less than or equal to 42, the PageID will remain 1. Then it will jump to 2 for the next 42 records, 3 for the next, and so on and so forth.
Step 3 - Add a Table tool
Now we will group the table by the PageID created in the previous step, giving us separate tables for each set of 42 records. You will need to make adjustments to the table and column widths so that the groups of columns fit across the page. In other words, your table width in inches multiplied by the number of groups of columns must be smaller than the page width.
Step 4 - Add another Formula tool
This is used in the next step to great a PageGroupID
Step 5 - Add a Multi-Row Formula tool
Here we are using the Mod function to add 1 to the PageGroupID each time the PageID is evenly divisible by 3 (has no remainder).
IF Mod([Row-1:PageID],3)=0 Then [Row-1:PageGroupID] +1 Else [Row-1:PageGroupID] EndIf
Step 6 - Add a Layout tool
In the Layout tool we will group by the PageGroupID in order to keep each group together on one page and we will configure each group horizontally. So, in essence, this is inserting each member of the group, starting from the left to the right, until all three members of the group are inserted.
Step 7 - Add the Render tool
In the report data section, make sure to choose "Insert Section Breaks Between Records" as the Separator.
If your text is too long and wraps within a cell, this will throw off how many rows can fit on the page and likely ruining the format of the entire report. You can either adjust the width of the cell or use the Left formula function to trim the column to a length that you then verify will fit. You can even possibly use the Length function for each column and use the Row Rule in the Table tool to make the text size smaller depending upon the length of the text.
This workflow is attached for you to download. Also included is a separate stream that is similar to above, but I have utilized several Multi-Row Formula tools to remove repetitive text, such as Region, County and Type. This can make the report easier to read.
If you are reading this, you are probably interested in maps. If you are interested in maps and of a certain age, then you have probably used a Rand McNally Road Atlas in the past. Waaay back in the past. In any case, the Road Atlas has a nice feature where you can look up a city within a sorted list, find it's assigned index value (for example B7), and then easily locate the city by using the x,y grid that was overlaid on the map. In Alteryx, it's possible to create a similar indexed map using several spatial and reporting tools. Below is one example of creating an Indexed Map.
In this example, we will be creating an index for a sample store file, which will allow the user to easily locate a specific store by narrowing the search down to a 10 x 10-mile grid section. Attached is the workflow.
Create the grids to encompass the stores
Using the Make Grid Tool, input '10' for Grid size and select the option 'Generate Single Grid for Entire Layer'.
Prep the grid values so they are in more of a user-friendly format
Using a Formula Tool and the SUBSTRING function, extract just the grid values.
Parse out the X and Y values.
Using another Formula Tool, add 1 to all grid values so that you don't have a grid of 0_0.
Using the Find Replace Tool, convert the Y values to letters so you get a label such as A1 as opposed to 1_1.
Finally, append the X and Y values together to get a single grid alphanumeric value.
Create the map and assign a grid section to each store
Using the Report Map Tool, create the map of your stores and overlay the grid.
Using the Spatial Match Tool, match the grid section to each store. In this example, I used the Store for the Target and the Grid for the Universe and matched where the Store touches or intersects with the Grid. Important: Without using the Touches option, the first store would be left out as it falls directly on the line of the grid.
I then sorted the stores by store number and created a table of the stores.
Create the Final Report
Join the table and map together into one record.
Using the Layout Tool, organize the table and map on the page.
Insert a Report Text Tool to add a title to the report.
Output the report using the Render Tool.
This particular workflow creates a report like this:
If you have a use case where you need multiple columns for your list of geographies, please see the Community article on Spanning Data Across Columns.
I hope you will find this useful. Please feel free to post any comments or questions.
Those of you who have used the Report Map tool to create thematic maps have likely been unimpressed with the way Alteryx outputs the thematic legend text. Alteryx added two little known/used tools: the Map Legend Splitter and Map Legend Builder. With a little finesse, you can get the legend to go from completely unformatted to fully customized.
Not only does this allow for an easier to read the legend, but it also can save valuable space on your map or document. The example above simply involves taking the default thematic output legend text and replacing it with user-defined text for those layers.
Here's How You Do It
The entire workflow is illustrated after all of the steps below.
In the Report Map tool on the Legend tab, change Position to "Separate Field". This will output the map and legend as separate objects, allowing you to work with just the legend.
Add two Select tools after the map. In the first Select tool, select only the legend. In the second, only the map (and BoundingRect, if needed).
Add the Map Legend Splitter tool after the Select tool that selects the legend, and select "Legend".
Add a Record ID tool which will be used later to re-sort the legend back to its original order.
Add a Filter tool using the [ThemeName] field in order to extract just the records which make up the thematic part of the legend. For this example: [ThemeName] = “Block Groups”.
Create a Lookup table containing the Record IDs and the new text for the legend rows that you want to replace.
Join the lookup table to the legend stream using RecordID. Deselect the original “Text” field and rename the “NewText” field to “Text”. Deselect the second RecordID.
Union the new modified legend rows back with the non-modified legend rows using “Auto Config by Name”.
Sort the records back to their original position.
Use the Map Legend Builder to rebuild the new legend. The default configuration is all that is necessary.
From this point, you can choose to either overlay the legend on the map (using the Overlay tool), or join the legend back to the map (using “Join by Record Position” in the Join tool) and position the legend adjacent to the map as desired using the Layout tool.
Below is the entire workflow numbered by the steps above. Attached is a sample workflow created in 10.0.
Multi-Line labels can be useful when you want to display multiple data points. Instead of using one long concatenated string, you can tell Alteryx to create a new line once it encounters a specific character specified by the user. To create a multi-line label simply follow these steps:
Use a Formula tool to concatenate the fields that you want to display while also adding a wrap character in between each value. In this example, we are using the backward slash as the wrap character.
In the Report Map tool in the 'Data' tab, select the new field you created in Step 1 for the 'Label Field'.
In the 'Layers' tab, check the 'Wrap Character' box and specify the wrap character used in the previous step.
The results should look something like this:
Things to Consider:
Make sure that the character you use as the wrap character is not present in any of the data that you are including in the label. Otherwise, you will get more lines than you bargained for, along with the possibility for some strange data. There is a section of the attached workflow that performs this check.
Though this would be complete overkill, I tested a 50 line label and it worked. In other words, there doesn't seem to be a practical limit to the number of lines you can create for your label.
Please see the attached completed workflow.
Thanks for reading!
We get quite a few requests asking how to add the current date to Excel spreadsheet file names. The difference in adding information to your file name in Excel and other output configurations is that you have to use the Reporting tools for Excel to accept the new file name.
Why would I have to use the Reporting tools, you ask? Simply put, Excel thinks that when you are adding a prefix or suffix to the file name that you are stating that additional information is the “Sheet Name” and not appending the file name from the Output Tool. The Render tool in the Reporting tab on your palette basically tricks Excel into thinking that you are creating a separate report and is able to append the date.
In order to add the date to the file name you will connect these tools to your workflow in the order, you see below.
In the Formula Tool, create a new field that is a String type. Go to the DateTime functions and add the DateTimeToday() expression. No configuration of the expression is necessary.
The Table tool will allow you to Group By this new field.
In the GroupBy Configuration, choose the Date field you created in the Formula.
In the Per Column Configuration, uncheck the new field. This will allow you to group by the new field name in the Render Tool and still remove it from the data so that your new field does not appear in your report.
Use the Render Tool to output your Excel spreadsheet with today’s date in the file name.
Output Mode - Choose a Specific Output File.
Output File - Specify the Excel 2007 Spreadsheet (xlsx) and point to where you would like to save the documents. The file name you specify will be replaced with the date in step 5 below.
You will then check the box that says Group Data into Separate Reports.
Field to Group on - You will choose your new field that you created.
Modify Filename By – Replacing Filename With Group.
Report Data – You will choose Table and can leave the rest as defaults.
We recently had a user that was looking to distinguish polygons between each other using dashed lines, a style not currently available in the Report Map Tool. But that’s alright, we can use the opportunity to showcase how you can be creative in Alteryx by using a few tools.
In cifically filter out the record IDs you want to change or use the Sample tool to pull random records, or 1 of every N Records. (FYI – if you use the record ID, you will want to remove that column after you have split the records, due to the record ID used later in the mapping process.)
Once you have selected the polygons you want to use, you will need to break those polygons into individual points using the Poly-Split tool. Here, choose Polygon field and Split to Points. Splitting the polygon into points will allow you to adjust the polygon by each point.
Then, you'll want to remove some of the points to create the “dotted line” effect by using the Sample tool. This tool's settings will want 1 of every N Record selected (you can change the N=3 to any number you like that will have the spacing effect you want).
Now that you have removed some points, you'll want to do a few things to give the points a grouping effect. In order to do this, add another Record ID tool, then filter the record ID by odd and even numbers. You can do this using the Filter tool and using the expression mod([Record ID],2)>0. Then add Record ID tools to the T and F anchors to complete the grouping effect when you add them both to a Union tool.
Grouping the points allows you to build your Polylines. After the Union tool, add a Poly-Build tool. The Build method will be Sequence Polyline using the SpatialObj and the RecordID, as the Source and Group fields, respectively.
Your final step is to add a Map tool and pull in the data from your Poly-Build tool, as well as the original centroid points of the polygons with which you created the split lines, and finally the remaining polygons you want to be represented as full lines around the radius. When configuring the Map tool, remember that the points coming from the Poly-Build are actually lines and not Polygons. Your layering will need to have Points, Lines, and Polygons to complete the map.
When creating reports within Alteryx, you can name Excel tabs by specifying a column to use in the Layout tool. This is done in a 3 step process.
Choose your grouping column; below, the Table tool is being used and [DMA_Name] is being used for the Group By field. This would also apply with the Charting and Map tools.
In the Layout tool, change the Layout Mode to Each Group Of Records. Next, choose the column you would like to Group By; for the example we will use [DMA_Name]. Next, change the Orientation to Vertical with Section Breaks. By doing this, the Section Name option will appear at the bottom of the tool configuration and allow you to choose the column you would like to use to name the Excel tabs.
Update the Render tool to create an Excel spreadsheet. In our example, each of the Excel tabs was named for the DMAs contained in the data.
Keep on reporting!
There have been a couple instances lately where users have wanted to use the Email tool to send email notifications as part of their workflow but run into issues because their email service is web based like Gmail or Hotmail.
Unfortunately the current Alteryx Email tools don’t support email from an online provider.
However, there is still a way to send email from Alteryx with a web based email. It’ll require the use of the Event tab or the Run Command tool and the installation of a third party tool. In this example a tool called SendEmail is used (Alteryx does not recommend or endorse this tool, it happened to be free and worked).
From the Events tab or the Run Command tool enter in the command that executes the tool then in the arguments section add in the tags that are required for the particular email tool.
Run Command tool:
There is another option in this post in the Ideas section by
If you’ve ever wanted to spruce up your reports by adding images, you’re probably familiar with our Image Tool in our Reporting Tool Category. While it often comes in handy when you want to append images to a report, you can also append images conditionally, and to your tabulated datasets. This can help to identify patterns or communicate trends in your data more visibly and improve readability for your target audience. After all, a picture is worth a thousand words!
We’ve gone ahead and built out a brief sample of the approach in the attached v10.5 workflow export, PictureInTable.yxzp. This workflow uses a simple Formula Tool assignment to conditionally specify an image to be visualized next to reach row of data. In this use case, we wanted to show a down arrow image next to a negative (or 0) number in our Test field (trending down) and an up arrow next to a positive number (trending up):
Since the image files are in the same directory as my workflow, the Image Location field now specifies exactly where/what the images are for each row, allowing me to specify this field in the Image Tool. This will append the image at those file locations to each row of my data:
Then I simply build a table around the already tabulated data, excluding my Image Location field that was used to append the images. After rendering the table, you should see the images included:
There you have it - you can extend the approach to really liven your reports!