Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
JosephSerpis
17 - Castor
17 - Castor

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.

 

Overview.JPG

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.

 

Green_Control_Container.JPG

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.PNG

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.JPG

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.JPG

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.

 

Control_Container.jpg

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.

 

Blue_Control_Container.JPG

 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.JPG

 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_Template_Results.PNG

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.