Export Sanity Checks

[MNewt9]

Hello All,

I had a question about exporting data.  I've got a lengthy work flow that uses some queries along with some static input data tables.  Periodically I need to incorporate a 'sanity check' to ensure the static table does not need updates.  Let's say I have 4 total.  I want to be able to export to a single Excel workbook with each sanity check on a different tab.  I am not sure that is possible, or if anyone else has a best practice that could meet my needs.  I've seen some exports of a single action divided up onto multiple tabs of a workbook, but not multiple actions along the workflow.

Thanks in advance.

[neilgallen]

Completely possible!

You would use a separate output tool for each sanity check, but when configuring the tool ensure that the filename and location is the same, with a new sheet name.

One caveat is that depending on the timing within the workflow, you may need to incorporate a block until done tool in order to allow the sheets to be written. You cannot write multiple sheets to the file simultaneously. 

Other options would also be a parallel block until done tool found in the crew macro pack, depending on the situation. Both can be useful.

[grossal · Accepted solution]

Hi @MNewt9,

You could do this using multiple output tools and it would work as long as it's guaranteed that they won't try to write at the same time.

I have also tested it without a wait tool and 100k records and did not run into an error, but I'd still try to avoid it and actually use the way others described. One Output tool and break it up into multiple columns.

Best

Alex

[Blake · Accepted solution]

Hi @MNewt9 - I would use reporting tools to create a table for each of your sanity checks, a formula to give each one a sheet name, union those records, use a layout to group each set of records and separate them, and then a render to output to Excel. 

1. Use a table tool to create a basic table for your sanity check. 

2. Use a formula tool to create the name of your sanity check/your sheet names when you export. 

3. Union your sanity check table reporting objects and their associated sheet names. You can see I have 3 sanity check tables, which corresponds to 3 reporting snippets/excel sheets. 

4. Use a layout tool to set up your report. I've highlighted the 4 most important settings in the layout tool below. 

5. Render your report to Excel. Depending on how many fields your sanity checks have, you might need to change the paper size in the render tool. 

I have attached a workflow that shows a basic example, ignore everything before the table tools.