Render Tables and then use field not in table to split by sheet or workbook

This is a render to Excel question using the reporting tools.

I need to create 400 tables with specific formatting.

I created an iterative macro that takes the 1-N records needed to create an individual table and creates the desired format. The # of columns for each table is the same, but the # of rows can vary.

After the iterative macro, I use a Render Tool to output to a single Excel sheet. All 400 tables render to the same sheet. But, I don't want them to render to the same sheet. I want to use a field not used in the rendered tables to split by sheet or as a last resort split by workbook.

I have very little experience with reporting tools. I suspect my problem might be compounded by my use of an iterative macro to create each table first. Should I have used a batch macro instead?

Reporting

Macros

Accepted answers

patrick_digan

@hellyars perfect. So it sounds silly, but you should add another layout tool after your current layout tool, select the vertical with section breaks option, and there you should be able to select your ASSMNT field as the section name. Then if you want it all in 1 excel output file on different tabs, don't group by in the render tool and just save to a single file (and the section names should put it on the separate sheets)

hellyars

If Alteryx will not add whitespace, I can brute force by adding a 4th table element that is just blank that sits above my H1 title element. Basically, you just create a title element that replaces the values with "".

All comments

patrick_digan

@hellyars A layout tool gives you the option to specify the sheetname if you us the "vertical with section breaks" orientation. You can then specify a section name by field, which when you render to excel becomes the sheetname. So all of your table snippets would just need an additional field not in the table itself that specifies the sheetname (ie your data should have two fields - your table element and then the sheet you want to write to). Let me know if you run into any trouble or need more details!

echuong1

You can use the group option in the render to output to multiple sheets/files. Take a look at the below links:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Using-Render-tool-to-format-multiple-sheets-in-the-same-file/td-p/170763

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Render-tool-creating-multiple-files/td-p/481704

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Output-to-different-sheets/td-p/131707

hellyars

@patrick_digan I am running into a problem. I currently carry over 3 fields. I can select any of the 3 fields and click Use Field, but the value is [None]. So, I am missing something.

Screen Shot 2021-02-17 at 8.36.33 AM.png

hellyars

@echuong1 @patrick_digan

Okay. I now have a better understanding of my problem. I can easily append a Sheet Name to each "table." BUT, each of my "tables" is created from 3 other tables (to work around the fact Alteryx can't place a title directly on top of a table). You can see this in one of my prior comments. This appears to be the root of my issue. So, how can I modify the referenced solutions (if at all) to address this issue?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703