Specific question output Excel multiple tabs with reporting tools

[user_km]

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?

[rafalolbert]

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-...

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[user_km]

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]

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]

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 · Accepted solution]

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:

Formula tool must still go after the Table tool.

Please let me know if that helps.

Cheers!

Esther

[user_km]

This helped a lot. Thank you @estherb47