Stacked Charts with Totals
By Tim Dexter on Sep 24, 2010
With OpenWorld out the way for another year we can move on with our lives :0) I was not there this year; sadly, I missed the madness of 40,000 folks descending on San Francisco ... what a shame!
Got an interesting chart requirement this week that was not that unusual. The customer needed a chart that looked similar to this.
Just a stacked chart right? Yep, but with a wrinkle thou; they needed to show the cumulative totals for the stack above the bar.
The Template Builder for Word does not quite offer enough exposure to the required properties that are needed to create the chart. So if you have a similar requirement, you'll need to dip your toe into the chart XML. To get at the XML
- In Word 2000-2003. Double click the chart image and go to the Alt Text tab
- In Word 2007 - right click the image and goto Size --> Alt Text
The data for this sample has the following structure
ROW TYPE VAL1 VAL2 TOTAL ROW
We do not actually need the TOTAL element in the final chart but we do need a third value in the chart to get the chart wizard inside Word to do some of the work building the data portion of the chart XML.
The chart uses TYPE on the X axis. The other values are used on the Y1(val1 and val2) and Y2(total) axes.
We start with a stacked line chart. In the template builder use the chart dialog to build a stacked line chart with 3 values. The total value must be the last. This is as far as you can get with the dialog, you now need to get into the chart XML to create the required output.
We hide to Y2 axes (set colors to white) and fix the maximum height to something less than the highest value on the Y axis - see red text below. You may need to play with this value to get the values to sit above the stacked bars.
<Y2Axis lineColor="#ffffff" axisMaxAutoScaled="false" axisMaxValue="68.0" majorTickStepAutomatic="true"/>
We also set the val2 and val2 values to be of type MT_BAR and the total to be MT_MARKER using the SeriesItems tag.
In the example below I have thrown out the TOTAL value and replaced it with a calculation
<Cell><xsl:value-of select="VAL1+VAL2" /></Cell>
So we need the total value initially to get the chart dialog to build the data portion of the chart XML. Then we can modify the XML with the calculation.
Once you have this all done, you will be good to go. So the key to not getting completely bogged down is to get the plugin to Word to build the data portion of the XML. Sample file is available.