Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
We'll be completing standard maintenance for our Single Sign-On system on November 30th 6pm Pacific Time for approximately one hour. During this time My Alteryx and sign-in functionality to all My Alteryx programs (Licensing Portal, Community, Beta Program, Case Portal, and others) may be inaccessible. Thank you for your patience.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Automatically Creating excel charts in different tabs while reporting

OB1
5 - Atom

Hi All, 

I need a help. I have output of the workflow as  a column of metrics and the respective values over the days of the months and the week and the year . All these are read in one sheet of excel. I want to know, if I can plot the matrices in line/bar chart and report each metric in visual form in each tab of excel. This would be really helpful, please let me know. 

Please see the Example Table. I want one tab in output excel which plots the values of the metric A for the days of the month, week and Month . Similarly one tab for B,C and D. And For metric A, I need one chart for each places. Please let me know if this can be done and how. 

 

Example Table is: 

Metric Place 01 Oct 02 Oct 

03 

Oct

04 

Oct 

Week

2

Week 3Week 4 Month 

A

CB567812141356
BCB.3.4.5.61.71.81.95.6
CCB1002003004001100180015004000
DCB22.522.522.522.522.522.522.522.5
ANY5678121413s56
BNY.3.4.5.61.71.81.95.6
CNY1002003004001100180015004000
DNY22.522.522.522.522.522.522.522.5

 

 

 

 

 

 

 

 

 

 

 

 

6 REPLIES 6
ChrisHe
Alteryx Alumni (Retired)

Hey @OB1 ,

 

I'm not sure about the order of your columns in the chart - to keep it simple I just have line charts where the date axis values go Oct 01, Oct 02, Oct 03, Week 2, Week 3, Week 4, Month in that order.  I can definitely help you modify once I understand the date relationships between the columns a little better.

 

But overall the workflow I have attached should give you close to what you're looking for! It takes your sample data, transposes, and then creates a number of batched line charts with the Interactive Chart tool.  Then I use the first layout tool to arrange the two places for each metric horizontally and the second layout tool to create section breaks.  These section breaks are what allow the final Render tool to batch out the charts into different sheets in Excel that are each labeled with the appropriate metric name.

 

Take a look and let me know what questions you have. If this helped you out make sure to mark it as a solution so others on the community can see!

 

-Chris

OB1
5 - Atom

Hi , 

 

thank you so much. It solves most of my problems. But I believe I didnot make myself clear, I wanted to have the data as well in the first tab and the rest as charts. I am sure it is easy, just cant seem to figure out how to keep the data as the first tab of the workbook. Please help me out. 

 

Also, I checked that the render tool would in default give the output of the chart as png. But is there anyway to change is , and give output as chart in excel ?

Thanks 

ChrisHe
Alteryx Alumni (Retired)

Hey @OB1 ,

 

Glad we're making progress! I've added another stream in the workflow that turns the data into a Table so it can be brought together with all of the line charts. The other extra tools I added just sorted the different Layouts so that the table of raw data appears first in the Excel. I've also added some annotations and containers to help explain things. Let me know if this works!

 

-Chris

OB1
5 - Atom

Hi 

Thank you so much, but the charts are still coming as png. As I had mentioned before, I was hoping to render the charts to excel as chart and not picture, so that someone looking through the report can modify the chart and select data as and how she wishes. Please let me know if that is possible ? 

 

ChrisHe
Alteryx Alumni (Retired)

Hey @OB1  - Alteryx can't modify the XML of the an Excel workbook to create an actual Excel chart that's pointed at data and able to be manipulated due to limitations in the Excel driver.  One solution I've seen with my customers is to output the raw data that feeds the chart from Alteryx to a sheet in a templated Excel workbook.  Inside that template you can have pre-made Excel charts pointed at the raw data.  That way whenever you run the workflow the raw data is updated and the charts will as well.  Let me know if this helps!

 

-Chris

Carmela
5 - Atom

I created different Charts with Interactive Chart tool, I used the "union" to merge them and then the Vertical Layout Tool to prepare them to render in Excel.

I would like to have one Excel sheet for each Chart with a custom name.

I solved the problem for Tables, but I cannot solve it for Graphs.

I tried to reperform this thread but i cannot se the "Group by" because i cannot find anything similar to the "Metric" used in the example.

Labels