Output Multiple Pivot in 1 Sheet in excel

Question

Hi guys, I have a little problem in outputting a 4 pivots into 1 sheet in excel. I can output only the data in Report Details container in my workflow. The output file must contain 2 sheets the first sheet is the Report Details data and the other sheet is the 4 pivots. I have a hard time to analyze how to output the pivots.

Please give me some ideas.

I've attached the source files, workflow and my desired output.

Please see my desired output pivot.

Thank you so much!

SourceFile.zip

Desired Output.xlsx

New Workflow1.yxmd

grossal · Accepted Answer

Hi @RichardAlt,

you are already done, you just didn't knew it! The trick is rather simple, we don't need to create the pivot tables in Alteryx. We just use that are already in the Excel-File. Just overwrite the first Sheet and you can refresh the Pivot-Tables in the second and it works totally fine. The changes in the workflow are rather simple:

I have removed all the additional work and just replaced it with an Output-Tool. Two things are important here:

1) We need you to write to the original file and within the right range.

2) We want to make sure we use "Overwrite Sheet or Range" and not "Overwrite File (Remove)"

I'd do one more thing in the Excel-Template. Set the Pivot-Tables to refresh when the file is opened. This will make sure that the Users doesn't work with an un-refreshed Pivot-Table.

I'll attach everything for you. Let me know what you think. (Maybe you have to change some file paths)

Best

Alex

AbhilashR · Accepted Answer

Hi @RichardAlt, Alteryx natively doesn't build pivots in excel. I have seen Users typically take one of two approaches:

1) use Reporting tools and attempt to create well-formatted tabular data in excel. This approach may get you close to how your final data table needs to look like, it isn't the same. The trade-off between the amount of effort and the desired result tends to be disproportionate.

2) pre-build pivot tables in an existing excel file referencing a datasheet tab. Have Alteryx output to this datasheet tab (i.e. overwrite the existing data), and just refresh the formulas in excel.