by Kiran Manjunatha and Bhumika Agarwal (@BA02)
Once upon a time, before you read this awesome article:
Stakeholder: “Hey! Can you merge those cells like we do in our Excel workbooks? Also, providing a common header would be a good idea too.”
Alteryx developer: “Naah, I don’t think so. Not possible using Alteryx.”
What if we say, “Yes, it is possible! And here is how.” (Given you are a “jugaad” developer.)
With the intention of not making this guide very long and boring at the beginning itself, here’s some good news! It will be a three part series. Woohooo! Here is the outline of the series:
1. How to Create an Alteryx Report with Merged Rows (you are here)
2. How to Create an Alteryx Report with Merged Columns
3. How to Create an Alteryx Report with Both Merged Rows and Merged Columns
Due to our lack of capability to generate cool datasets, we shall be using a subset of publicly available superstore sample data.
Let’s get to it!
Prepare the data in the desired format with all the required columns you wish to see in your output. Refer to the example below.
Now let's start by converting the data into the desired report first.
The concept here is that your merged cells row will become the parent row, and all the other rows in other columns will become the rows in the child table.
Insert a Basic Table tool and configure it like below:
Additionally, configure the tool to add the borders, background column, alignment, etc. [Optional]
This specific step (step 2) will create row merged cells.
In a Text Input tool, input headings as a row like below:
Insert a Basic Table tool and configure it as:
Now use the union tool to add the headings on top of the data tables. Make sure to configure the output order accordingly.
Add a Render tool and configure it to write it to Excel.
Excel report output:
“We don’t want it in Excel. Any other formats which Alteryx can render to?”
For output formats other than Excel, we need to add an extra step to get the borders to align. Add a Layout tool and configure it as below:
After the Layout tool, add a Render tool and configure it to the required file format. This table format after the Layout tool can also be used in the body section of an Email tool!
You might have observed that in formats other than Excel, you can see the extra line like the one below the Technology row.
You can make the extra lines less visible by using white borders and light grey background for the table cells like below. This will give a more elegant look. Nice!
We hope this was helpful. Feel free to reply to this post and hit us up with your queries if you are facing any issues or need any clarifications. We shall be happy to help.
Cheers and Happy Analyzing :-)
Stay tuned for the next part of this blog! We hope you’re as excited as we are for this blog series!
Read Part 2 Here.
Read Part 2 Here.
I was excited when I saw this blog series, thinking there was finally a new tool and/or setting in an existing tool to configure these types of table output options. The step-by-step instructions here are really helpful, but I would love Alteryx to develop a much easier way to output data in these types of merge cell formats with settings inside a single table tool. Something like an additional section in the table tool to, select the fields you'd like to [merge rows/merge columns] and number of rows/columns to merge [ numeric value ]. I'm sure it's way more complicated than that, but just a feature request.
@collin_pace You can give a like to this idea that someone submitted for Designer to try to resurface it :) https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Multiple-row-headers/idi-p/29317
Thanks @MeganDibble for the link. Funny enough, after clicking it, I found that I already liked this idea at some point in the past. Happy to see you bring more attention to it! Maybe it gains some traction.
this is very helpful. thank you especially for the easy-to-follow steps.
This is some cool + easy stuff !👍 Thank you @atcodedog05 and @BA02 ...
I was able to follow it and do it on my own! Woohooo!! Thanks @atcodedog05 & @BA02
This is great stuff. :)
Thank you 🙂 @archelaus @AsmiDesai @Deepali77 @Joyce-Swapna
Happy to hear that you folks found it helpful and easy to follow 😀
Nice work @atcodedog05 !!!!!
Hello @atcodedog05 I don't know why I only see this series today. Very cool and thank you!
This is SO helpful. Thank you very much!!
Need of time, Thank you
wow this is very helpful. The only issue I run into after rendering to excel, is when I filter on a merged column, it will only pull up one row. In the sample dataset, in excel if I filter on Segment to Consumer, only one row of data will appear. Is there a way to get the other categories aligned to Consumer (Office Supplies and Technology) to appear?
Never too later to see such a terrific post!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.