Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
In 2019 I wrote a blog to answer the question “Have you ever needed to output your results from Alteryx into an Excel report and does that report require formatting” which I spoke about using the blob tools to achieve this. There was one challenge with this approach which was order of execution ensuring your Excel template moved to the output location before the rest of the workflow completed. With the release of Alteryx Designer 2023.1 and Control Containers you can manage order of executions or the sequence in which tools run.
High Level Approach
Using the Blob Input and Blob Output tools you can pick up an Excel template and move it to your output location. The Output Data tools which inevitably will be near the end of your workflow simply need to be configured to write the data to the same output location and filename as the template that has been moved by the blob tools. With Control Containers you can ensure the blobs tools execute first, moving the Excel template. Then the remainder of the workflow can execute after this using another connected Control Container.
Alteryx Workflow Overview
Alteryx Workflow Excel Template
The approach to use Blob tools to move an Excel template from one location to an output location is relatively simple. I use a Sample Tool to limit my record to one as I want one template to move to my output location.
Blob Tools Excel Template Overview
The Formula Tool creates two file paths fields, Input which is where my Excel template is located, then Output is where my template should be saved to. In my output field I can also rename the file to Sales Report instead of keep Sales template. The Select tool in this scenario is removing unwanted fields however is actually redundant as the Blob Tools would ignore any extra fields in this situation.
Formula Tool Overview
Configuring the Blob Input is relatively simple choose a dropdown option for “Modify File Name Using Optional Input” which I have selected “Replacing Entire Path With Field” and I’ve used my column called Input. This will pick up my template from the filepath location and convert it into a Blob field type.
Blob Input Overview
The Blob Output tool is a similar setup to the Blob Input Tool whereby I select “Replacing Entire Path with field” for “Modify File Name By” and I select the column Output for the field. This will then move my template to the output location.
Blob Output Overview
By using the Blob Tools, I essentially pull in my Excel template into Alteryx Designer convert it to a Blob which does not affect the formatting or any formulas within the Excel files and then outputs to the location specified, ready for the data to be outputted by the rest of the workflow. The Output Data Tool needs to write to the same location where you are saving your template to. By encasing this section in a Control Container and then connecting the remainder of the workflow in another, this will ensure that the Excel template is moved to the correct location before the rest of the workflow completes.
Step 1 - Blob Tools Excel Templates Moved to Output Location
The line between the Control Containers is what is controlling the sequence therefore step 1 completes before Step 2.
Output Excel Report
The blue Control Container in this example just represents the function an Alteryx user would perform to get the data ready to produce an Excel report. The key thing here is the Output Data configuration.
Step 2 - Data Prep/Transformation Output data to Excel Template
In the Output Data configuration, I am writing to the same location and file that I moved the Excel template to earlier in the workflow.
Output Data Configuration
In my example I write to a back sheet via the Output Data Tools. The front sheet has been formatted and contains conditional formatting and also has a graph based on the data inputted into this template. By using this technique of using the Blob tools I can create a template to match a company’s color branding, add in their own logos, customize the report for the end user while being able to automate populating the data from Alteryx and ensure that my template is not corrupted.
Excel Formatted Output
Conclusion
With the release of Designer 2023.1 and Control Containers it has become easier to implement a reporting process in Alteryx where you perform your data analysis in Alteryx and output to an Excel template as you can now control the sequence the tools run.
Hope you find this blog helpful. Please leave a comment or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.
This article was originally posted on Intersections and Overlaps.
