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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Specific question output Excel multiple tabs with reporting tools

user_km
8 - Asteroid

I have a workflow that outputs to multiple tabs in the same Excel workbook and I'm using multiple output data tools to do this. One of the outputs (Sheet1) has a group ID column which I use to split into multiple sheets using the append to file/table name function in the output data tool. So the output for Sheet1 would be Sheet1-1, Sheet1-2, etc. There is another output Sheet2 which has a completely different field schema from Sheet1 so I use a different output tool to output it to the same workbook.

 

 

Attached is an example of what I'm currently doing with the output data tools. Can the same thing be done with the reporting tools?

 

 

6 REPLIES 6
rafalolbert
ACE Emeritus
ACE Emeritus

Hi @user_km,

 

You can achieve this when distinct schemas are collapsed into single cell and run through Wait Until Done tool, please see my solution from a few days ago here:

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/I-need-2-or-more-of-my-output-data-to-...

 

img1.JPG

 

Thanks,

Rafal

 

#Excuse me, do you speak Alteryx?

user_km
8 - Asteroid

Hi @rafalolbert,

 

My question relates to how to use the reporting tools but this example still uses the output tools. Apologies if my question wasn't clear but attached is an example of what I am currently doing using the output tools. I'm looking for a way to do the same or similar using the reporting tools so I can also add cell formatting to the sheets.

 

 

estherb47
15 - Aurora
15 - Aurora

Hi @user_km 

 

You would set up separate tables for each sheet, using the Table tool in the Reporting tools category. Then, you'd create a text field with a formula tool, called "Name", that has the name you'd like to use in the sheets. Union together, and layout with the layout tool, using the Name field for the section configuration. Then Render out to Excel

 

@afv2688 nails it in his solution here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Reporting-Tool-Multiple-tabs-in-one-ex...

 

Let me know if this helps!

 

Cheers,

Esther

user_km
8 - Asteroid

Hi @estherb47 ,

 

I tried this but I'm running into issues with the Sheet1 output. Sheet1 needs to be further divided into multiple tabs based on the group id column. I tried concatenating "Sheet1-" with "groupid" to create the name column, but I get an unrecognized variable error. I then moved the formula tool before the table tool which resolved the error. The output is generated, but the Sheet1 output is all on one tab but I am trying to create multiple tabs for Sheet1 called "Sheet1-1" and "Sheet-2". Do you know a way to do this and also keep the Sheet2 output in the same workbook?

estherb47
15 - Aurora
15 - Aurora

Hi @user_km 

 

So the table tool, on its own and without any grouping levels applied, will just output a table, without any other variables. That's why the formula tool fails when it's after the table.

 

If you tick off "Grouping ID" in the Table dialog box as below, then that field is included in the output, and two separate tables are generated. Here's a screenshot:

 

image.png

 

Formula tool must still go after the Table tool.

Please let me know if that helps.

 

Cheers!

Esther

user_km
8 - Asteroid

This helped a lot. Thank you @estherb47 

Labels