Output Multiple Pivot in 1 Sheet in excel

[RichardAlt]

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! 

[grossal · Accepted solution]

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 solution]

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.

[RichardAlt]

@grossal 

Thank you it works, But I have new problem. For ex. I have new column User Group in that User Group it contains (HK,SG,PH etc.), 

Because your sample is 1 excel output only, How can I separate that same file by User Group. 

I've added the other tool to separate the output by user group, But it will create new file. How can I use the existing file like you did but it will separate them by user group.

[grossal]

Hi @RichardAlt,

have you seen the options at the bottom?

You can use these to output the data based on one field to multiple files or sheets. If you want to output it to different sheets, you have to use a Formula Tool in front to create the filepath and select the option "Change Entire File Path".

(Third line describes the schema - I use a relative path here, that's why I have the explanation below)

This will create or write to a specific sheet for every User Group. 

Best

Alex