We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
ChrisWaspe
9 - Comet

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.

 

image001.png

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.

 

image002.png

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.

 

image003.png

Figure 3: Quarterly Car Sales Generation

 

image004.png

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.

 

image005.png

Figure 5: Dynamic sheet naming for Car Makes

 

We ensure the previously mentioned processes occur in the correct order using control containers.

 

image006.png

image006.png

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.

 

image007.png

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.

 

image009.png

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.

 

image011.png

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.  

Comments
pmaier1971
Alteryx
Alteryx

Love this! And, for those that want to run something similar on server, but run into issues installing the additional R packages: https://community.alteryx.com/t5/Engine-Works/Using-Alteryx-and-R-in-Designer-and-Server-Installing-...

Charles_B
5 - Atom

This looks very useful! Do you have a example workflow you can share?