How to pass variables to Pivot Table

[Kuha]

Hi,

I am creating a pivot table using this process.

I have to create about 20 pivot tables. Is it possible to pass the info required for the 'Cross Tab' from a text file using micro, So I don't need to create 20 processes?

I am new to Alteryx and not familiar with micro. Any suggestions would be a great help. Thanks.

Kuha

[martinding · Accepted solution]

Hi @Kuha,

Please see below:

I've set a relative path in the macro, but feel free to change it to your desired saving location in the formula tool.

[Kuha]

Hi @martinding,

Thank you very much for the solution. It works fine with my production data.

Kuha

[Kuha]

HI @martinding ,

I was experimenting further using your macro to enhance the process, but I am unable to get it right.

This is what I did. I added another column (FilePathe) with file details in the hope of saving the pivot data in the same file and sheet.

Report ID	Filter	Column Headers	Values	Method of Aggregating	FilePath
Report01	Region	Rep	C_Jan-23	Sum	C:\DbTemp\Alteryx\Pivot_Report_03-Mar-2023.xlsx|||Pivot1$A1:L4
Report02	Rep	Item	C_Feb-23	Sum	C:\DbTemp\Alteryx\Pivot_Report_03-Mar-2023.xlsx|||Pivot1$A9:F20
Report03	Region	Item	Total	Sum	C:\DbTemp\Alteryx\Pivot_Report_03-Mar-2023.xlsx|||Pivot1$A25:F28
Report04	Item	Region	Total	Sum	C:\DbTemp\Alteryx\Pivot_Report_03-Mar-2023.xlsx|||Pivot1$A32:D37

Any idea how to make this work? Or is it even possible?

Kuha

[martinding]

Hi @Kuha,

Yes, it is possible to save the data on the same sheet and in the same excel file.

There are multiple approaches:

1. Make use of the reporting tools (the approach I took)

2. Saving as ranges (I haven't tried this approach but I think it is less dynamic as it may be difficult to predict what the ranges will be before creating the tables).

There might be other approaches as well that I am not aware of, hopefully, other people in the community can help (feel free to post this as another question, I would also be really interested to see other solutions and approaches!)

Please see below for the Reporting Tool approach (I've updated the workflow and macro slightly): 

[Kuha]

Hi @martinding 

Thank you very much for your time and effort. It is very useful. I never tried any reporting tools. It is a starting point.

I have now managed to create and save the Pivot outputs to the same Excel file. I am just learning macro and your help is greatly appreciated. Thanks a lot.

Kuha