Render multiple report layouts to a single excel sheet

[sureslala]

Hello
I have 3 distinct outputs producing different structured data (each output is unique with different columns) and I want to extract them to different tabs within one excel sheet.

Is it possible to do this?

I've come across some discussion on this issue but couldn't find anything that would work in my case.

Thanks

Sures

[CathyS_Slalom]

@sureslala - please refer to Dan's response from this discussion to create different tabs with different schemas using render tool.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Workflow/m-p/925574#M226336 

[CharlieS · Accepted solution]

It's definitely possible to render different layouts to separate sheets in the same Excel. The only downside to this is that the render size will be the same between all the sheets (we can dive into that more if you'd like)

In the workflow you pictured, it looks like you've got one Layout field per sheet: which is exactly how you want it set up! The next step I recommend is to use a Formula tool after each sheet to add a string field called something like "SheetName" so each datastream has two fields: {Layout] and [SheetName]. These allow you to have custom names for each sheet. Note: the named sheets will always be sorted by name alphabetical order. If you want a different order, I would add a numeric prefix to your name fields (like "1_DataSummary", "2_DataTable", "3_Map", etc)

Union those three together and then there's one more Layout tool after that, there's some important configurations:

1. Layout by "Each Group of Records"

2. Orientation "Vertical with Section Breaks" when it comes to rendering to Excel, "Section Breaks" = "Different Sheets"

3. Select "Layout" as your field (important they all have the same name)

4. Section Name: "SheetName" or whatever you named your field. 

A render tool set to Excel will know what to do with all this and that's it. Check out the attached workflow and let us know how it goes!

[sureslala · Accepted solution]

Thanks Charlie
Your solution worked perfectly!

Cheers!

Sures

[dlouderm]

You wrote, "The only downside to this is that the render size will be the same between all the sheets (we can dive into that more if you'd like)".  I would definitely like to know how to get around this restriction; is it possible?

Thanks.