Reporting Excel table as pivot

[SanjayVyas]

Hi Team, I wanted to create my Excel output same as pivot. I'm able to generate the output from Alteryx however, not able to format the same as Pivot. Which consists (Headers and Grand total in Blue and False breaks in Gray color along with grid). The main challenge I'm facing is Total of Rows coming on top when I sort them descending (as I want Real breaks to be appeared first then false and then Total). So, in nut-shell below are my issues:

1. Total of Rows (records) to be appeared at last (after Real Breaks, and False Breaks)

2. Headers and Grand Total in Blue color with False Break in Gray color to high-light

Attached is the screen for your reference. Thanks

[atcodedog05]

Hi @SanjayVyas ,

Pivot tables are not creatable in Alteryx.

Only workaround is you need to load data into a raw sheet (hide it in excel) and an another sheet with pivot table from the raw sheet in the desired format.

Hope this helps : )

[SanjayVyas]

Let me ask this in another way. I've already got the output with the help of Cross Tab query through Alteryx and attached screen was the same. Now, I want to format that output same as another table. The challenges I'm facing is 1. sorting wherein Row Total should come last rather that 1st row. And 2. color formatting for headers

[atcodedog05]

Hi @SanjayVyas ,

Its definitely possible using reporting tools.

Can you provide the sample data and workflow.

[SanjayVyas]

Here you find attached the workflow along with sample data. 1st tab - my Alteryx output 2nd tab the desired output formatting. Please let me know in case of any clarification needed. Thanks

[atcodedog05 · Accepted solution]

Hi @SanjayVyas ,

It took me while. It worked on the Alteryx output since the the actual workflow was throwing some issues due to the missing data.

Here is the workflow

Output

To edit formatting options

Click on Table tool > Edit Table rule button highlighted below

 Add rules as required.

Hope this helps : )

If this helps please mark the post as solution.

[SanjayVyas]

Hi,

Thank you so much for your solution. I've got the idea and some how able to resolve the issue.

However, I'm getting the desired output in Alteryx only, while trying to use output tool to get that in Excel, it is giving me error line for formats. (attached is the excel out I've got along with my Alteryx output and workflow). something like this - <div class="d6597a015a0704d19b6bb96f1d7f25b96" width="100%"><style>

[atcodedog05]

Hi @SanjayVyas ,

A report cannot be output using output tool. You need to use a render tool for it and select output as excel file.

Hope this helps : )

[SanjayVyas]

Hi @atcodedog05 ,

You are right, but I have 2 more sheets to insert and this (Pivot look like) is the 3rd and last sheet. Hence using Output Tool. When I'm using Render tool it is giving the desired output in Excel but with 1 sheet only. Not sure:

1. Can I get 3 worksheets as an output by using Render?

2. Else can I get desired pivot look like report with 3 worksheet by using Output?

I'm getting both the things as an output but the issue is, if 3 tabs are there in output - it is not creating that Pivot look like output. When I tried by Render tool, I'm getting Pivot tab but not rest of the worksheets.

[atcodedog05 · Accepted solution]

Hi @SanjayVyas ,

Sorry for missing your response.

I found this article which might help you out.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Using-Reporting-Tools-to-create-Mul...

Cheers and Happy Analyzing 😀