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

[hellyars]

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?

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

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

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.  

[patrick_digan]

@hellyars Are you using header or footers in the render tool? That could make things more interesting. In addition to your three reporting elements, you need a 4th field that is a string containing your desired sheet name. And the layout tool is going to mess up your headers/ footers. I'll try and get a quick working sample and post back.

[hellyars]

It is a bit complicated.  Each table is actually 3 table elements.  This is necessary to replicate a desired format.  The first table creates the Title (H1).  The second table creates the Column Headers.   The third table is the actual Table Data.     This was driven by the need for Title (H1) to sit directly on top of the table (something Alteryx does not do).  I also need to format the background and text colors of Title based on a value.  

 

[patrick_digan]

@hellyars and then what does your render tool look like?

[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?

[hellyars]

@patrick_digan  To be more specific.  I want to group my 400 tables by the field [ASSMNT] -- think market segment / sheet name. 

I can't set Layout Mode to "Each Group of Records"  by [ASSMNT] because it does not allow for more than 1 data field. 

I can't set Orientation to "Vertical with Section Breaks" using the "Use Field" because I have more than one data field.  

[patrick_digan]

@hellyars What does your render tool look like? I'm assuming you're just rendering a single reporting element field?