Stacked Charts with Totals

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.

stacked1.jpg

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

here
.

Comments:

All in all I think at this point the macbook pro has my money.

Posted by Tambra Shop Dress on October 06, 2010 at 08:44 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today