Specific question output Excel multiple tabs with reporting tools

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?

reporting tool example.yxmd

Reporting

Accepted answers

estherb47

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

All comments

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-be-saved-in-single-excel/m-p/442927#M84439

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

worflow.yxmd

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.

reporting tool example.yxmd

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-excel-file/td-p/420777

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?

reporting tool example.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703