Reporting in Alteryx: Creating 100 PDFs with Maps

[MeganBowers]

This is the second article in the Reporting in Alteryx series. You can read the first article on how to generate a PowerPoint in Alteryx here.

I’m back with another Alteryx reporting tools use case—this time, it’s PDFs! And lots of them.

Generating a single PDF in Alteryx is a nice feature. But using the batching capability of the Report tool and generating 100 different PDFs is an awesome feature.

Source: GIPHY

When I was a data analyst, my team used the tools in this workflow (along with the interactive chart tool) to generate 150+ PDFs—one for each manufacturing plant location with their monthly KPI data. They needed to all be separate files because each plant manager would print out their charts and post them to a board in the plant for a lean manufacturing initiative.  

I enjoyed working on that use case, but I thought I would try something slightly different with this workflow. I wanted to showcase the Report Map tool, so I needed some example spatial data.

For this article, I will be using data scraped from Movinga about the top cities in the world for raising a family (from 2019). My goal is to create reports for each city with a simple map view and statistics so we can easily see the city location & compare statistics between different options. After that, I want to create a map with the top 20 cities and a legend indicating their rank so we can visualize the best options at once.

Let’s dive in.

The Tools

No surprise, the Reporting tools are the backbone of my workflow. Here are the Reporting tools I used (which I did not cover in the last article):

Table

The Table tool allows you to create a formatted table with your data. I kept it simple for my workflow, but you can apply conditional formatting based on cell values, color cells or data, change the look of headers, and more. The Table tool is your friend if you want to make your data tables pretty or replicate formatting from Excel.

Report Map

If you have spatial data, you can use the Report Map tool (and a Browse tool) to visualize it on a map. There are multiple options for the background map layer and opportunities to customize the look and feel of the map.

Report Map Legend Splitter

If the default legend that the Report Map tool generates isn’t exactly what you want, then the Report Map Legend Splitter can help. It allows you to split up the components of the default legend and edit them with other tools.

Report Map Legend Builder

Once you build out the components of your custom legend, you can bring them back together with the Report Map Legend Builder. I will go more into depth on the legend splitter and builder towards the end of the article.

Generating 100 PDFs

The data from Movinga contains the latitude/longitude coordinates of the top cities for raising a family, the city name, the overall score, and all of the subcategory scores (which factor into the total score), like Kids Activities. Here is a sample of some of the columns:

The workflow begins like most—with some data preparation.

Since I wanted to display the cities on maps, I used the Create Points tool to turn the latitude/longitude coordinates into spatial objects (points). Additionally, I created a “trade area” with a 1.0 mile radius around the points so that we could see a sample of what the center area of the city looks like on a map.

Once the data was prepped, I began to create the elements I needed for my PDF report: a map, a table with the data, and a title.

Here is the configuration for the report map: I used the Alteryx Streets reference base map to see some features of each city.

In the data tab configuration, I selected the trade area as the spatial field and the grouping field as RecordID so that there would be a map created for each record (instead of all trade areas showing on the same map).

I did not add a legend for this map. Here is the resulting output:

Next was the table—I selected the data points I wanted to view on the PDF next to the map. I also grouped by Record ID for this tool so that I would have a table for every city:

And finally, I used the Report Text tool to create a title for each PDF with the City, Country, and RecordID (which represents the number ranking since the data is sorted). This tool default generates text for each record, so no grouping was needed.

With all of the elements for the PDFs ready, I used a Join Multiple tool and joined on Record ID to get the components all in the same record of data. Here is what the data looked like:

I then used a Layout tool to configure the layout of the PDF components on the page:

After checking the formatting with a Browse tool, I was ready to create the PDFs with the Render tool. This is where I can generate the 100 PDFs by checking the “Group Data Into Separate Reports” option and grouping them on the Record ID field. I set the output file name to “Best_City_” and modified the filename with the “Appending Group To Filename” option.

The result is 100 PDF files, named Best_City_1.pdf to Best_City_100.pdf. And below is what the final output looks like:

Now we have reports for all the best cities to move to for families! The Alteryx Streets base map details the neighborhood names, significant streets, transportation hubs, parks, water, and some landmarks. The table below the map includes the data to show how the city got its ranking.

After completing this exercise, I thought of another use case for this data. What if we wanted to see the locations of the top cities all at once on a map?

Generating a Map with the Top 20 Cities

This portion of the workflow uses the Map Legend Splitter and Builder tools. I want to see all of the cities and have a legend indicating where the cities are in their total score as a good city for a family.

To see all of the top 20 cities on the same map, I selected the first 20 records and configured the Report Map without a grouping field. I set the thematic field to “Total” because I wanted the points to be colored according to the city’s total score.

I also added a layer to the map to include a legend.

After this, here is what the map looked like:

This was very close to what I was looking for, but the legend is a bit off. I want to clarify it by eliminating “#1” (since there is only one layer) and simplifying the categories. This is where the Map Legend Splitter and Builder come into play.

Below is the general pattern for using these tools: split the legend, parse or apply formulas to the legend text, and finally, put the legend pieces back together, including your new elements.

The Splitter configuration is simple; select the legend you want to split, and you’re done. Once you’ve edited the pieces of the legend, using the Builder is straightforward as well: make sure to select the new fields you created or edited. In my workflow, I used the RegEx tool to parse out the text from the legend I wanted to keep, so I selected the text field as the output from that tool.

Once my legend was ready, I had to create another Report Map like before, this time without a legend. Then I appended my legend from the output of the Builder tool. Finally, I used the Layout tool to place the legend next to the map.

And here’s the final result!

Now we have a map with a simplified legend that shows the score buckets for the cities displayed. And the Report Map tool did this bucketing for me—all I had to do was select the Total field as the thematic field, and the tool generated even groupings for the legend and colors.

Conclusion

The Report Map tool may not be one that you have used before, so I encourage you to play around with it! Even if your final output does not require a map, it can be helpful to visualize your spatial data as you move through your workflow. These reports could also be great additions to project summaries or presentations.

As we saw in the first section, the grouping functionality of the Render tool is pretty powerful. This can be used for any output from the tool: PDF, Word, Excel, PowerPoint, or Image. And when there is no spatial data or map creation, Alteryx workflows can churn out 100s of reports in seconds.

I’ve attached my workflow to this article if you want to try it out. You might want to disable the output if you don’t want 100 PDF files written to your computer :)

Thanks for reading!

100 PDFs.yxzp