Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
I recently wrote a blog highlighting the multiple methods an analyst can use to create formatted PDF reports using Alteryx. In this blog, we will continue exploring the different methods that can be used in Alteryx to improve your outputs. We will be focusing on creating a contents page at the beginning of a formatted Excel report, with hyperlinks to various sheets within the workbook.
We will begin by utilizing the Blob tools to create our formatted Excel report and the fantastic Control Containers (available from 2023.1) to order our workflow and ensure all processes run at the correct time. Finally, we will take advantage of the versatility offered by Alteryx, using R to manipulate our Excel report and adding hyperlinks to the sheets in our report. While it is possible to use the Report Text tool to create links in Rendered Excel Outputs, the Render tool does not give you the versatility to have unique sheets such as Car Sales Report (seen in the previous blog and mentioned later) or ordinary Excel sheets.
Use Case
Our manager has tasked us with creating a report summarizing the Quarterly Car Sales. This report must be done in Excel but follow a very specific format. Our manager would also like the original data, along will a separate sheet for each car make dynamically output to the same Excel workbook. As there are 56 car makes, meaning 56 extra sheets, the manager would like a contents page, with hyperlinks, to each of the sheets in the workbook. We are going to create a process that does all this in Alteryx.
We will begin with an Excel workbook template which contains four sheets, namely an empty Contents sheet, the Front Sheet, Sheet1, and Original Data. The front sheet is a formatted report that references data in Sheet1.
Figure 1: Excel workbook front sheet
Now let us take a look at how we make use of this prepared Excel template in Alteryx. As mentioned, the initial process is explained in detail in the initial blog. In short, we read in our Excel template and write out a new Excel file that mimics all the work done in the template using the Blob tools. We dynamically name our Output files in the formula tool.
Figure 2: Blob tools
Once our data transformations are done in Alteryx, we write out to Sheet1 in our newly created formatted Excel workbook. The front sheet gets populated, and we have our report.
Figure 3: Quarterly Car Sales Generation
Figure 4: Quarterly Car Sales Report
The next step in the process is to populate our Original Data sheet and create an individual sheet for each Car Make. Our Original Data sheet is created straight from our input data, using a formula tool to dynamically populate the sheet with data. The original data is then sorted by the Car Make field, which is then used to dynamically create the sheet names in a formula tool, and selected as the Output file name in the Output data tool.
Figure 5: Dynamic sheet naming for Car Makes
We ensure the previously mentioned processes occur in the correct order using control containers.
Figure 6: Workflow orchestration
The first step in creating the Contents page for our Excel workbook is to summarize and pivot our data. We use two Summarize tools to create duplicate columns of our Car Makes. We then Cross Tab the Car Makes to have a heading and one row of data for each Car Make. We manually add in our Front Sheet and Sheet1 fields and ensure they are at the beginning of the columns. We can now populate our Contents page using the Output data tool.
Figure 7: Create contents page using car make list
The contents page is populated with data, but we still need to create a process to convert each row of data into a hyperlink to the appropriate sheet. We could do this with the Report Text tool, however the Render tool would overwrite all our previous hard work. It is not possible to Render only a single sheet and append it to an existing workbook. Therefore, we must come up with another solution.
Firstly, we use the Formula tool to create the Excel function necessary to create the hyperlinks, which is the HYPERLINK function. The expression is made of the location the hyperlink must go to (preceded by a ‘#’) and the text to appear in the cell. We dynamically use the Contents field to create this.
Figure 8: Hyperlink formula
The data needs to be pivoted before feeding it into the R tool, with each Sheet Name as a heading, and the corresponding formula as a record of data.
Figure 9: Preparing data for the R tool
Most of the hard work is done with Alteryx. We use R and the ‘openxlsx’ package to load in the workbook and convert our previously written formulae to a working Excel formula. Initially, we dynamically create a list of the formula. Then we load in the workbook, the location of which is taken from the first field of our data. We use the ‘writeFormula’ function in a loop to convert the text into an Excel formula and finally save the workbook.
R code below:
#install.packages("openxlsx", repos="https://www.stats.bris.ac.uk/R/")
#remove.packages("openxlsx")
#install.packages("remotes", repos="https://www.stats.bris.ac.uk/R/")
#remotes::install_github("ycphs/openxlsx")
library(openxlsx)
wbdata <- read.Alteryx("#1", mode="data.frame")
vardata <- list(NULL)
for (i in 1:length(wbdata)) {
vardata[[i]] <- paste(wbdata[,i],sep = '')
}
wb <- loadWorkbook(vardata[[1]])
listlen <- ((length(wbdata)-2)/2)
contentssheet <- 3+((length(wbdata)-2)/2)
for (var in 1:listlen) {
writeFormula(wb, sheet = 1,
startRow = (1+var),
x = vardata[[(var+2)]]
)
}
saveWorkbook(wb, vardata[[1]], overwrite = TRUE)
df <- as.data.frame(vardata[[1]])
colnames(df) <- "FileName"
AlteryxMessage("Workbook updated with links", msg.consts$INFO, priority.consts$LOW )
write.Alteryx(df, 1)
Conclusion
Hopefully, I have demonstrated with the example above that, as with all things Alteryx, there is almost always a solution to a problem. Combining the versatility of the standard Alteryx tools, the control from the Control Containers, and a little R code, we were able to achieve just what the stakeholder requested.
