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.
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!
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!
Ever have to output tables of differeing schemas to the same Excel workbook? Ever need to output to different tabs? This article covers your bases with the cunning use of Reporting tools! Also included are links to other helpful "outputting to Excel" Knowledgebase Articles.
Believe it or not, data can be beautiful. Take your black and white data points and add some color to them in visuals with the suite of tools found in the Reporting Category https://help.alteryx.com/current/index.htm#Getting_Started/AllTools.htm#Report_Presentation_Tools ! If you’re looking to create reports, presentations, images, or simply output data with a bang, you can use the Render Tool https://help.alteryx.com/current/PortfolioComposerRender.htm paired with other Reporting Tools to create HTML files (*.html), Composer files (*.pcxml), PDF documents (*.pdf), RTF documents (*.rtf), Word documents (*.docx), Excel documents (*.xlsx), MHTML files (*.mht), Power Point presentations (*.pptx), PNG images (*.html), and even Zip files (*.zip) – packed with formatting and visual aesthetic that’ll make any data-geek’s mouth water.
If you’ve gotten accustomed to having headers and sub-headers (fields/sub-fields) in your tables, much like those you can build with the “Merge & Center” Excel feature, you’ll be happy to hear with just a couple formatting steps we can build the format below:
In Alteryx, with similar formatting:
The attached workflow goes into the details of the approach, but the essence is below:
Split your columns/fields your desired categories (making them sub-headers)
Create tables from your categories, add your supra-headers
Combine the reporting elements we have now, use the Union Tool to stack and the Summarize Tool to combine the reporting elements horizontally
Combine your sub-tables into a larger table, if needed (adding another header)
There you have it! Your tables are now rendered in style.
A close relative of the Layout Tool , the Visual Layout Tool is the newest, and coolest, Reporting Tool on the block. Sporting all the badassery of the original Layout Tool in its ability to format and arrange reporting objects, the Visual Layout Tool differentiates in that it provides an intuitive, visual, interface that allows for easy drag-and-drop organization of multiple reporting object inputs. Basically, it’s the reporting tool category equivalent of upgrading from Paint to Photoshop.
The consummate Reporting Tool , the Layout Tool isn’t named after its ability to “layout” your reporting work like Terry Tate, Office Linebacker . No, the name is borrowed from the tool’s core functionality to freely arrange reporting objects in your workflows to fit a templated, “layout,” format to be output by the Render Tool . Giving you the flexibility to organize reporting snippets horizontally or vertically, set the width and height of columns and rows, and to add borders and separators, the Layout Tool is the last requisite step in compiling, formatting, and generating a report in Alteryx - probably to never be made by hand again!
The Table Tool has many options for formatting data - e.g. prefixes and suffixes can be added, certain fields can be highlighted based on formulas and many more. Don't fret if you want to format more than one column at once - there's a workaround using the "Dynamic or Unknown Fields" option.
You know what they say; the devil is in the details. Precious little separates a good report from a great one and it often comes down to just a few details. Don’t let a report header be the detail you left out – use the Report Header Tool to painlessly add header reporting elements to your reporting creations!
With all the bells and whistles to play around with in the Reporting Tool Category , it’s hard not to leave some out of your reporting workflows every now and again. Just don’t forget about the Report Text Tool – the tool that’ll help you painlessly add text to your reporting objects, presentations, or documents to help spruce up their readability or formatting.
As long as you know where to look, data has all the answers. Sometimes, though, those answers aren’t clear as day. More often than not, they need to be communicated in an effective format - a format that can let the data talk and highlight the important motifs for you. Another favorite of the Reporting Tool Category , the Charting Tool can do just that by adding expressive visuals to any report or presentation. Offering an exhaustive list of charts to choose from (area, stacked area, column, stacked column, bar, stacked bar, line, tornado, pareto , box and whisker, scatter, bubble, polar, radar, pie), the Charting Tool will give you the ability to add descriptive visuals, with legends and even watermarks, to your reporting workflows that will help you find the answers in your data.
Any time you want to get a good point across, it’s best to show your data. Show your data off in style in your reports or presentations by adding formatting to otherwise bland data with the Table Tool! Found in the Reporting Tool Category, the Table Tool will make it easy flair to your raw data, and give it the pop it needs to really sink in.
This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Image Tool on our way to mastering the Alteryx Designer:
A picture is worth a thousand words, right? Save your breath and snap a picture to supplement your analyses and reports with the Image Tool, the camera icon tool residing next to all your other reporting needs in the Reporting Tool Category. Whether you’re looking to build a presentation, report, or email from scratch, or simply add graphics to accentuate your raw data – this tool will make it a breeze to access image files from disk, store image files in physical workflows, or dynamically access image files (even in Blob format!) to pair with any Alteryx output. Use the Image Tool to:
Add visual assets to reports and presentations (attached in Image.yxmd):
Perform dynamic image substitutions (attached in Image.yxmd):
Supplement reporting tables with graphics to make raw data more readable
By now, you should have expert-level proficiency with the Image Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at email@example.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.
Stay tuned with our latest posts every #ToolTuesday by following @alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.
I have configured my map to zoom to a specific layer, but the map is not zooming as expected. I have confirmed that the 'Zoom to Layer' checkbox in the Layers tab of the Report Map tool is checked only for the layer in question.
The Email tool is designed to send an email for each record that you input -- if you attach an Email tool directly to your output data stream, it will generate and send one email per record – e.g.: if you have one thousand records in a report you'd like to send, the Email tool will send one thousand emails.
The reason for this is that you may have a list of email addresses as recipients, or you might want to use a separate subject line for each department in your organization; that is what the input side is for – to allow you to dynamically populate fields such as the "To" and "From" fields, or even the body of the email itself.
If you're not populating your Email tool from your data stream, to keep the Email tool from sending a thousand emails, first separate a single record from your data stream, attach the email tool to that single record, and then hard code your configuration – including attachment -- into the Email tool. There are a lot of ways to accomplish this - a Sample tool, or a Select Records tool, or a Unique tool will all get the job done.
The Email tool will execute at the end of the module, regardless of what is attached to it. Based on this, once you've written out your results using an Output tool, another option is to simply attach an Email tool to a Text Input anywhere in your workflow and hard code your configuration into the Email tool with the attachment specified – just don't put more than one record in your text input!
In a workflow, not too far, far away...
Structured data has vanished. In its absence, the sinister Dirty Data Order has risen from the ashes of the Server and will not rest until Data Analytics have been destroyed.
With the support of the Alteryx Engineers, Solutions Engineer Tony Moses leads a brave RESISTANCE. He is desperate to find structured data and gain its help in restoring blending, joining and analytics to the galaxy.
Tony has sent his most daring Community Leader, Matt DeSimone, on a secret mission to Jakku, where an old ally has discovered a clue to the structured data whereabouts....
Welcome to the Star Wars universe!
Ever wanted to know the most important details of your favorite characters from Star Wars? Me too!
Our generous friends, Paul Hallett and team, have given us the Star Wars API - the world's first quantified and programmatically-accessible store of Star Wars data.
After hours of watching films and trawling through content online, Paul presents us all the People, Films, Species, Starships, Vehicles and Planets from Star Wars.
The data is formatted in JSON and has exposed it to us in a REST implementation that allows us to programmatically collect and measure the data.
Now, how was I able to retrieve this treasure of information via Alteryx? Easy! I've built a REST API connection using the Download Tool to pull information based on a user inputted query in an Alteryx Application (attached as v2018.1 Star Wars.yxwz).
Normally, once having retrieved JSON formatted data, structuring and parsing the data would be a nightmare! With Alteryx, this is just one tool away. The JSON Parse Tool allows you to identify the JSON field, in this case our download data field, and easily extract Name and Value columns. From there it's some simple formatting and using the reporting tools to present us a nice clean composers file (pcxml).
Man, if only the Rebels could process information as fast as Alteryx then they wouldn't have had to send poor R2 to find Obi Wan.
I'll be bringing you, the Alteryx Community, updates of the app with each new movie release!
I hope you enjoy the API and may the Force be with you!