Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Using Reporting Tools to create Multiple Excel Files with Multiple Tabs

PaulT
Alteryx Alumni (Retired)
Created

Alteryx has a great many ways to output data, not the least of which is the spreadsheet giant Excel. Almost everyone in an analytics space has dealt with data in Excel in some way shape or form in their career, and many use it as their go to format to deal with data or present reports. There are many different ways to output your data to Excel in Alteryx such as:

  1. Outputting to multiple tabs with identical schemas in a single output file using the Output Tool (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-to-multiple-sheets-within-an-Excel-fil...)
  2. Outputting to multiple tabs with differing schemas to a single output file Using the Output Tool (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Multiple-Excel-Files-to-One-Excel-File-with-M...)
  3. Outputting completely separate Excel Files with identical schemas using the output tool (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-to-separate-Excel-files/ta-p/1247)
  4. Output different tables to multiple tabs in Excel (via the Render Tool) (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-different-files-to-multiple-tabs-in-an...)

This article will explore how to output tables of differing schemas in their own tabs in multiple Excel file via the Render Tool. This addresses the specific use case where you have data in two separate tables that are very different in structure. However, in this example we’ll assume that the data in those tables is categorized in to different regions (North, South, and West). The end result is that you want to have 3 Excel files (one for each region), and you want to have two tabs specifically named in each of those excel files (one tab for each table of a unique structure that you want to output).

The first step is to complete all of your data processing, once we move to the Reporting Tool set you will lose much of the flexibility to manipulate your data once your tables are converted to “Report Snippets.”

To create a Report Snippet for each of your tables, drag down a Table Tool (located in the Reporting Tool set) on to the canvas. Assuming you have data from each region in all of your tables, you will want to be sure to select the “Group By” function in the Table tool. Here you will select the field that contains the Region information. Thiswill tell the Table Tool to output completely separate tables (one for each region). These separate tables will be the basis of creating unique .xlsx files.

Following the Table Tool, you can add in a Formula tool and create a new field that can be used to name the individual tabs you are sending your data to in the desired Excel file. This is a best practice when using this method to create multiple tabs and will help the end user intuitively know what tabs contain what data.

Now you canuse a Union tool to bring everything together. At this point you should have a field you chose to group by in the table tool (in the case of the attached example it will show up as FileName), the Sheet name that you created in the Formula tool, and a Table field which contains the report snippet for each unique table you have in your data.

The last two steps are to configure a Layout tool and then finally the Render Tool. In the Layout Tool, select the Orientation option of “Vertical with Section Breaks.” This will tell Excel that you want the data to output to separate tabs. Now you can click on the “Table” entry at the bottom of the tool and mark the Section Name check box, and select the field Sheetname that we created earlier in the Formula tool.

Layout Tool.png

This will tell Alteryx how to name each of the tabs inside each of the unique Excel files you are creating. The final step is to configure the Render tool. In the previous step I have told Alteryx I want to have multiple tabs and that they should be named based on the field “Sheetname.” In this step we are going to tell Alteryx that we have multiple files we want to output, and they will be named according to the field “FileName”

Render Tool.png

Choose the output mode for “Choose a Specific Output File” and then designate a place for that file to go. Then check the Group Data in to Separate Reports and choose the FileName field. Here you can make some selections regarding how you would like your files to be named. You can choose to append the Group name (in our case “FileName”) to the test.xlsx to create testNorth.xlsx, or you could prepend the FileName or you could even replace test.xlsx altogether with the FileName field.

Make your selection and choose your Data Field to be Layout with no spacing between records.

Note: Excel outputs from the Render tool will try and accommodate the size of the sheet of paper the Render is trying to write to. Therefore, if you have particularly wide tables, it is best to set the Orientation to Landscape, and alter your paper size to accommodate your large tables. Otherwise fields or field names may get truncated.

The attached workflow was built in Alteryx version 10.5 but was adjusted to work in version 10 and above.

Attachments
Comments
Scott_Snowman
10 - Fireball

PaulT, this was an invaluable explanation. Thank you!

 

I followed these steps and used a Formula tool to attach to each of my data sets, denoting the string to be used for the sheet name.

 

I discovered that the Render tool appears to arrange the sheets in alphabetical order by their names, rather than the order in which they are output from the Union tool. There doesn't appear to be any way around this aside from adding numbers to the beginning of my sheet names to force them to keep the same order through the sort.

 

I'd like to avoid that; is there any way to get around this sorting behavior?

 

EDIT: It looks like I had my Formula tool in the wrong spot in the workflow. I was placing it upstream of the Table tools, rather than downstream. Once I moved it to match the sample workflow, everything came out as I put it in. I did, however, need to check the box on the Union tool to force a specific output order; it appeared that without this parameter, the tool could end up arranging the tables in order based on when they arrived at the tool while the workflow was running. Since some of the data analysis finished earlier than others, the resulting outputs for the sheets were coming through at various times.

michael_heitz
8 - Asteroid

Excellent breakdown! I was stuck until i found this and was able to get my workflow to work! One thing I am wondering - if I have a report that breaks out into 5 regional reports - they all go to the same file location, but 5 separate emails get sent. Any way to have just ONE email with the Email tool linked off this?

AnujnaN
6 - Meteoroid

Thanks a lot for this detailed explanation! A lot of times people don't understand/appreciate the complexity behind creative informative and well presented reports!

Ragini
7 - Meteor

Excellent explanation! Thank you so much for this!

NathanOch
8 - Asteroid

Hello!

 

This post was very helpful, thank you so much for putting it together. I ran into one issue I was hoping to get insight on. It isn't necessarily about constructing the report, but more so in an anomaly I am seeing. In the layout tool after selecting the orientation as 'Vertical with Section Breaks' I go to select my field in the Section Name criteria at the bottom and when I do my field name comes up in the picklist, however the selection disappears and greys out if I toggle to another node. When I go back to the layout node my field selection is no longer there until I click on the 'Table' value again.

 

NathanOch_1-1612994718166.png

 

 

 

NathanOch_0-1612994692162.png

 

Any thoughts as to why it would do this?

 

Thank you!

Nate

gupta_sahil
8 - Asteroid

Hey @PaulT ,

 

Thanks, this is really helpful however it does not work when AMP is turned on for the workflow. The output shows only one tab when AMP is on. While this works perfectly fine when AMP is off. Please help in resolving this issue, is this a bug in alteryx?

nbondarchuk
Alteryx
Alteryx

@gupta_sahil Could you please share which designer version are you using? I've checked on 2023.1 with AMP Engine selected. As output, I have three xlsx files (North, South, West) with 2 sheets each (Region Data 1, Other)

 

On the screenshot below you can see the example of xlsx file generated with AMP Engine.

 

Screenshot 2023-07-04 at 13.49.37.png

 

 

 

gupta_sahil
8 - Asteroid

Hey @nbondarchuk , 

 

I am on version: 2022.3.1.395. For me it shows only one sheet named "Region Data 1" when AMP is on but two sheets when AMP is off.