Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
One of the most famous demands from the business is “Can we get a formatted report that looks nice?”
SRC: Pinterest
Yes, you can generate reports in Alteryx in more than one way. But which one should you choose?
We will cover some different ways to create a formatted Excel report, when you would use them, and when not to.
The following are the different ways you can create a formatted Excel report, ranked based on the complexity Easy to Hard:
Render Tool
The Render tool is the Alteryx native tool that can generate beautiful reports (not limited to Excel).
The Render tool is part of the Reporting tool group. You can create formatted tables in Alteryx with Report Text and Table tools, and then output using the Render tool.
Below is the output generated using the snip above. The tools in orange are the Reporting tools.
The above example is only to demonstrate the kind of formatting that is possible using the Reporting tools.
When to use it?
Reporting tools are perfect for small to medium workflows.
Use these tools for generating reports if you are creating outputs in a format other than Excel. They are perfect tools for generating PDFs and emails.
When not to use it?
Excessive usage of these tools can slow down the workflow and can also cause failures. If you need a heavily formatted report, this is not the best option to take.
Additional Resources
- Reporting in Alteryx blog series – sending emails
- One File, Three Sheets, Three Different Formats
- Reporting tools interactive lessons
Predefined Templates
Another commonly used method is Predefined Templates. In this method, formatting is already present in the Excel report, and we want to update the data in it using the Output tool.
Predefined templates are perfect when you have a lot of fixed formatting like descriptions, instructions, branding, etc.
These are lifesavers as one does not have to create these in the Reporting tools, which can become a little tricky.
One can use an Excel formula for calculating final values based on Alteryx output. Hence, making the report more dynamic.
Sounds simple, but how is it more complex than the Reporting tools?
Source: Pinterest
Because we are dealing with predefined templates, the following challenges are very common:
Used Ranges
An Excel sheet can have a used range that visually appears empty.
What are used ranges? Used ranges are cells that Excel thinks contain data.
How can you identify them? Press Cltr+End inside Excel, it will take you to the last used cell. If it spans to empty range, then it is a bad template.
Problems with Used Range:
- The increased file size can slow down the Alteryx writing operation.
- In case of appending the data, the data will be appended after the used range, even if there is no data present in it.
Conditional formatting
This is a very common method of formatting the report, BUT it must be used carefully.
Conditional formatting, although it does not add any data to your workbook, makes your workbook heavier, i.e. larger in file size. A large file size is a bad template to work with.
Do not add conditional formatting to the whole sheet just to add borders!
Formulas
Formulas! What would Excel be without formulas?
Yes, formulas are your best friend when used wisely, but a nightmare if added to a large range of cells.
I've encountered an Excel template where a formula is applied to an entire column—spanning all 1,048,570 rows, with the first six reserved for instructions. The idea is that whenever data is entered into a row, the formula automatically performs a calculation.
While this may seem convenient, it significantly slows down the workbook and increases loading time. Applying formulas across such a massive range is inefficient and not recommended as best practice.
And at times, these are nested functions. I am glad that array functions are still rare in the world of Excel.
When to use it?
When you have a heavily formatted report that contains many fixed parts, like instructions, images, fixed-format columns, etc.
Use it as much as possible and wherever you can! Just avoid the above-mentioned challenges.
Add Excel formulas to your Alteryx workflow if necessary.
Process Example:
The workflow below picks data from an unformatted file and writes it to a template called Demo.xlsx.
Template used: Below is a predefined template with Instructions, title, and formatted headers.
As you can see in the screenshot below, I have defined a name range “Demo” highlighted by a red arrow and circle. We will use this named range to output our data correctly.
Named Range used:
Setting up the template location: Once we have our template ready, we will set up the paths as follows. Note that I am using a Named Range in the output path as shown in the pic above. This will ensure that the output is going to the right location, without the user having to manually define a range.
Final Output: Output looks like this.
I have demonstrated for one sheet; however, it can be set up for multiple pages of workpapers.
Additional Resources
- Output to multiple sheets within an Excel file
- Data Output to Template Excel File in Alteryx Designer
Note that while working with templates, it is necessary that the template remains unchanged. You must create a copy of this template every time you run the workflow, then write data to it. This can be achieved by different approaches, but I would recommend using Blob Tools.
Using Blob Tools
The Blob tools let you work with binary large objects (BLOBs) such as images, documents, or multimedia files by reading, writing, and manipulating them within workflows. They’re useful for tasks like importing pictures, storing files in a database, or converting file data into usable formats.
These tools allow you to input a formatted template file into your Alteryx workflow and write data to ranges in that Excel file.
See this example from @NicoleJ's popular blog on this topic—all of the formatting of the template file is preserved.
You can create multiple copies of the template using just one blob output; thus, there is no need to use a batch macro or a run command tool to run multiple copies.
In the workflow above, I read the file just once in blob input, generated 5 rows, and updated the file name to make the file names unique. With this approach, I was able to create 5 copies of the template.
When to Use it
Use the blob tools when other reporting methods fail, or when you need a more dynamic solution. This method can help you preserve things like conditional formatting. It is also useful when the file names or sheet names need to be changed according to data inputs.
Additional Resources
- The Blob: Reporting That Isn’t From a Horror Film
- Methods for Creating Formatted PDFs: Exploring BLOBs, VBA, Python, & Control Containers
Using VBA
This is the ultimate solution—come to this only if the first two do not work.
At times, there are requirements that are simply too much to do in the Reporting tools, but cannot be done using templates. Things like adding coloring to the Alteryx output report, adding a border, maybe making the output report into a table—these are some of the simplest asks.
So, as the ask gets complicated, so does the solution.
Now, VBA can only be done using a template; hence, it is a combination of Template + VBA. The template will be an .xlsm file, which will contain your VBA code that will essentially do the formatting/calculation/anything that was unreasonable to do from Alteryx.
But it is a hassle to explain to the end user how to run this code, isn’t it?
We will add an open workbook trigger, which will run the code as soon as the file is open.
Your default settings might prevent VBA code from running automatically, so we add a dummy page with instructions:
This dummy page will give instructions that click on “Enable Content” while none of the other sheets are visible.
Once enabled, the open workbook trigger will kick off the VBA code, and if written well, it will all happen in the blink of an eye. The end user never sees anything different.
But will it trigger every time I open this workbook?
Source: Pinterest
No! So here is the bonus tip: while writing your VBA code, end it by saving your file as .xlsx and removing the .xlsm file. Once the code saves the file as xlsx, your active workbook becomes a .xlsx and the .xlsm workbook becomes inactive, so you can delete that using VBA code.
In the end, we have a perfectly formatted workbook that nobody would know was formatted by VBA.
Here is the code that I use:
Private Sub Workbook_Open()
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
XPath = ActiveWorkbook.FullName
ypath = Replace(XPath, ".xlsm", ".xlsx")
If InStr(1, ActiveWorkbook.Name, "Template", vbTextCompare) > 1 Then
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub
Else
Call FormatMySheet
Worksheets("Introduction").Delete
ActiveWorkbook.SaveAs Filename:=ypath, FileFormat:=xlOpenXMLWorkbook
FSO.DeleteFile XPath, True
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
“FormatMySheet” is a function written inside a module that is being triggered by the above code.
Open Trigger checks whether the file that is opening contains “Template” in its name. If it does, then it does not trigger the “FormatMySheet” function. Much is needed to modify the VBA code.
Alteryx renames the file when it creates a copy of this file before writing output to it (missing template in its name), triggering the function when this output file opens.
When to use it?
Use it when you cannot achieve the formatting with the first three approaches.
You can use it if your company allows VBA codes to run. There are policies in some companies where these are blocked.
When not to use it?
If you are not familiar with coding, bad code will make it much more difficult for you or the backup to fix it in the future.
Note: Reduce and/or eliminate the selection of cells/sheets. That will make it slower, defeating the purpose of the whole activity.
Using Python
Using Python for formatting Excel is possible too. While there are many ways to use Python to format the file, I would recommend using it at the end of the workflow to add the formatting to the file.
Outputs can be created using the Output Tool, and Python will format the file at the end of it, see screenshot below.
Taking the same example as VBA, we will now perform the formatting using Python.
In this case, the Demo.xlsx output is plain output. This is where I will be passing my data to.
Python will run once the output is written. And creates an output as below.
I wanted to focus on formatting via Python here, so I have used the same file as the VBA method. I used the following Python code to achieve the above formatting.
Run `Alteryx.help()` for info about useful functions.
i.e., `Alteryx.read("#1")`, `Alteryx.write(df,1)`, `Alteryx.getWorkflowConstant("Engine.WorkflowDirectory")`
# List all non-standard packages to be imported by your
# script here (only missing packages will be installed)
from ayx import Package
#Package.installPackages(['pandas','numpy'])
from ayx import Alteryx
df = Alteryx.read("#1")
path = df["Directory"][0]
fileName = df["FileName"][0]
Alteryx.installPackages(['openpyxl'])
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
# Create workbook and sheet
wb = wb = load_workbook(path)
ws = wb.active
# Merge A1:B3, left aligned, font size 20
ws.merge_cells('A1:B3')
ws['A1'].value = "Merged Cell"
ws['A1'].alignment = Alignment(horizontal='left', vertical='center')
ws['A1'].font = Font(size=20)
# Color A1:B4 blue
blue_fill = PatternFill(start_color='FF0070C0', end_color='FF0070C0', fill_type='solid')
white_font = Font(color='FFFFFF')
for row in ws.iter_rows(min_row=1, max_row=4, min_col=1, max_col=2):
for cell in row:
cell.fill = blue_fill
cell.font = white_font
# A6 is bold
ws['A6'].value = "Bold Text"
ws['A6'].font = Font(bold=True)
# A7 is bold and Times New Roman
ws['A7'].value = "Bold Times New Roman"
ws['A7'].font = Font(bold=True, name='Times New Roman')
# A9:J9 green background, white font
green_fill = PatternFill(start_color='FF76933C', end_color='FF76933C', fill_type='solid')
white_font = Font(color='FFFFFF')
for col in range(1, 11): # Columns A to J
cell = ws.cell(row=9, column=col)
cell.fill = green_fill
cell.font = white_font
ws['A1'].font = Font(size=20)
# Save the workbook
wb.save(path)
When to use it?
Use it if you feel more comfortable with Python than VBA.
When not to use it?
If you are trying to do a lot of formatting. Python will take additional time to perform tasks, so if it increases your workflow time, switch to the VBA method.
Additional Tricks
- During development, especially while using templates, it can get very difficult to maintain a blank template, as the workflow will write to it during each run. We can use the blob method as explained above to store a blank template in one location, get the file to another location with a new name, and use this new template copy. This ensures that you maintain a single template, and development can be done on the template without having to worry about overwriting.
- Want to create a customized formatted report? You can accomplish this by using Reporting tools, then using the predefined template concept to populate the data. How is it better? Since reporting tools deal with limited data, they will still be faster than when they must deal with a larger dataset.
Note: You can use the run command tool to run VBScript without having to store it in an .xlsm file. But the catch is that for this method to work, Excel must be installed on the Server, which is not always the case. If the server does not have Excel installed, you may not be able to use VBScript via the run command.
Excel is not mandatory to run a VBScript; however, it becomes necessary if your VBScript is automating Excel—for example, opening a workbook, reading/writing cells, or using Excel formulas.
