Report Regrouping to Different Tabs of Excel Files

[WendyChen]

Hi Community,

Below is my workflow. After transposing my data, I have four records in each of the three tables with four same unique identifiers in each table. I want to regroup the records into four same different reports based on unique identifiers, then spitting out those reports into four tabs of the same excel file.

Can anyone help me with the solution? Thank you.

[JosephSerpis]

Hi @WendyChen  I would suggest you built a filepath in your workflow and with your unique identifiers determine which sheet/tab they sit on and then in a output tool you can use a field to change the filepath of the output this creating 4 tabs in an excel produced by your workflow.  

[CharlieS]

There's a few particular steps to producing named sheet output from the Render tool.

1. Prepare your data so that you have two fields: Sheet name and Layout. The sheet name should be a string to be used as the sheet name. The layout field should be the Layout you'd like on that sheet as produced by other tools in the Reporting tool category.

2. Use a Layout tool with the following configuration:

  - Mode: Each Group of Records

  - Group by: Sheet Name

  - Orientation: Vertical with Section Breaks (section breaks will become sheet breaks)

  - Data Field: Layout Field

  - Section Name: Sheet name

3. In the Render tool, configure the Data Field to Layout and "Insert Section Breaks Between Records". 

Check out the attached workflow for an example.

[WendyChen]

Hi @CharlieS, 

Thank you for your reply. I believe I was not able to state my problem very clearly above. Below is the illustration of what I was talking about. I think it is a bit more complex. And the number of records in each table are actually determined by the query coming from in-db tools. I am interested in knowing how I can assemble records into the reports based on same unique identifiers in the picture attached. Thanks!

[WendyChen]

Hi @JosephSerpis 

The number of records for each tables are actually coming from in-db queries and are dynamically determined by users' interests. Thank you!

[CharlieS · Accepted solution]

Nice sketch! Rather than a Union, I would Join those fields and use a Layout tool to create the content for each sheet before the final Layout. I've attached an example to show how this can be done.

[WendyChen]

@CharlieS,

Thank you for the solution. I have another question that my email tool sends out multiple duplicate emails each time I run the workflow based on the number of records coming out from the layout tool. What tool should I put in front of the render tool to make sure it only generates one file for the email tool? I know there are solutions in the community but since my records are dynamic, they either dont work(Unique or Sample tool) nor not in details(summarize tool).

Thanks,
Wendy

[CharlieS]

The email tool will send an email for each record fed into it. So you need to find a way to combine all the components into a single record. I could help if you post an example of your field layout and what you would like the email tool to send.

[WendyChen]

@CharlieS ,

From your solution above, I assembled my reports into multiple tabs and rendered it into an Excel file. I tried to use email tool to email that particular Excel file out, but duplicate emails are sent due to multiple records on the layout tool. I look forward to your reply soon. Thanks!

[CharlieS · Accepted solution]

I modified the attached workflow above to show how this file could be written and included as an email attachment. I recommend defining a (temporary) file path ahead of time so that gives a common reference to

1. write the file.

2. group by to have one record for the email tool.

3. so the email tool can find the file to attach. 

Check it out and let me know if this works for you.