Combining Reporting Tables in one .xlsx-File

[MathiasL]

Hi,

i know the possibility to write dataframes in separate sheets in one .xlsx-file by explicitly naming the sheet via the standard "Output Data"-tool. On my current project i now want to customize the table layout already in Alteryx Designer, so i chose the tools from the Reporting section and wrote it to an .xlsx-file. That was no problem, but actually i'm dealing with more than one dataframe which should be written to the same .xlsx-file but into a different sheet. Does anyone else had this problem already and found a practicable solution?

Furthermore i would probably then have the problem that Alteryx says that the file is locked or is currently used by other when writing at the same time to one file but in different sheets? (same problem as writting it via the standard "Output Data"-tool) 

Unfortunately here the Block Until Done will probably not work because i have parallel starting input streams which should write data to the file before they get joined.

Thanks in advance,

Mathias 

[DataNath · Accepted solution]

Hey @MathiasL, you can combine your tables by using a Union tool to bring them into a single column. Then, when you come to Render your output, under the Report Data section, you can select your incoming data field (Table) and choose to Insert Section Breaks Between Records (for reports like PDFs this is a new page, for Excel it represents a new sheet and therefore creates a new sheet for each record - one record being a table in your case). The outcome is as follows:

Overall workflow & Render configuration:

Two individual tables:

Output into two separate sheets:

Workbook is attached. Please let us know if you need further help!

[MathiasL]

Thanks @DataNath  - that's perfect.

Further i have two more questions on this topic:

• My first Table has a lot of columns so i set the "Custom Size" in the Render Tool to 6000 x 4000 mm for perfect fitting. The second table only has a few columns and automatically gets the same report size as the first table, which now doesn't look good because it may needed only size 1000 x 1000 mm. Is there a possibility to render or set the size of the two sheets separately?
• Is it possible to give the sheets of the file self-defined names? (instead of sheet1 and sheet2)

Regards,

Mathias

[DataNath · Accepted solution]

No problem @MathiasL - happy to help. In terms of the sheet names issue, you can tackle that with the following workflow:

The addition of the Layout tool here basically does the section break splitting for you that we were previously doing in the Render too itself, and allows the sheet name to be assigned. The sheet names themselves are just created by adding a Formula tool to each of the streams going into the Union - make sure the field name is the same so that this is easily handled in the union.

When it comes to the sizing, I'm not aware of a way of getting varied size for each of the different tables - It's easily achievable in Alteryx itself when looking at a Browse tool, but once rendering it seems that the table width becomes fixed to the paper size you set regardless. Would be keen to see any way of getting around this if anyone else knows.

[grazitti_sapna]

@MathiasL , Here is the answer to both of your questions.

1. The best workaround I could offer would be to remove the custom page settings you are using. Try Legal Landscape, I attached what this looks like (Output 2022-09-07.xlsx), and the sizing is still stretched to match the page size, but it's legible on a single view from a computer.

2. Yes you can give names to the sheets. I have made few changes in the workflow and added layout to select the self- defined sheet names.

Please accept my solution if this was want you needed.

Thanks!!

[MathiasL]

Alright, thanks again @DataNath - you've helped me a lot.

[DataNath]

No problem at all @MathiasL - if any posts have helped to answer your issue then feel free to mark them as a solution so that others can find them easily in future if they have the same problem. If you need any further help then please do shout up and we can assist!

[MathiasL]

Thanks @grazitti_sapna , but the problem ist still remaining, since switching to Legal Format with Landscape settings is even to small for my first dataframe (as i mentioned above the size is 6000 x 4000 mm). So this is unfortunately no help for my problem.

Regards,

Mathias