Stacked Chart with only two data fields?

RTF Template

I got involved in an interesting issue yesterday with Julie. She had two columns of data coming into her data set but she wanted to generate a stacked vertical column report, normally you would have three data points. One of the data points would be along the X-Axis. The count of the instances of the other data point would make up the column. Its an interesting problem ... well I thought it was. Read on if you are interested. The change in font will be explained at the end.


Using the following data:


<ROWSET>

<ROW>

  <STE>CMP</STE>

  <ACT_LEV>SHORT</ACT_LEV>

</ROW>

<ROW>

  <STE>CMP</STE>

  <ACT_LEV>SHORT</ACT_LEV>

</ROW>

<ROW>

  <STE>CMP</STE>

  <ACT_LEV>LNG</ACT_LEV>

</ROW>

<ROW>

  <STE>CMP</STE>

  <ACT_LEV>OPEN</ACT_LEV>

</ROW>

<ROW>

  <STE>CMP</STE>

  <ACT_LEV>OPEN</ACT_LEV>

</ROW>

<ROW>

  <STE>COM</STE>

  <ACT_LEV>SHORT</ACT_LEV>

</ROW>

<ROW>

  <STE>COM</STE>

  <ACT_LEV>SHORT</ACT_LEV>

</ROW>

<ROW>

  <STE>COM</STE>

  <ACT_LEV>SHORT</ACT_LEV>

</ROW>

...

</ROWSET>


Notice that

1, Data is de-normalized

2. The ACT_LEV for a given STE can contain any of 4 values


ACT_LEV

LNG

OFFLINE

OPEN

SHORT


This is the required chart:


stack1.png


It's a stacked column chart showing the count of each ACT_LEV value for each STE value. Note that the count is not available directly in the data ie


STE

ACT_LEV

CMP

SHORT

2

LNG

1

OPEN

2

OM

SHORT

14

LNG

25

CVD

SHORT

8

DIF

OPEN

13

SHORT

2

DRY

OPEN

3

LNG

8

SHORT

6

IMP

OPEN

1

INS

SHORT

1

LIT

OPEN

1

SHORT

2

LOT

OPEN

1

LNG

1

MTL

OPEN

1

LNG

3

SHORT

1

PHT

LNG

4

SHORT

1

PTS

OFFLINE

4

WET

SHORT

2


This is going to require some customization of the chart commands. Once modified by hand the chart dialog will not be able to re-read the chart definition without losing the customization.


Start using the dialog to get the following:


stack2.png




Notice:


  1. Drop the STE field into the Labels field. Set the Group Data checkbox

  2. Drop the 'measure' ACT_LEV for as many times as you have possible values for ACT_LEV. In the attached data set its 4.

  3. Set the chart to Bar-Vertical Stacked

  4. Add your legend values

  5. Be sure to set the aggregation to count. The graphic is showing Sum. I think there is a bug in the chart dialog, if I create the base chart, Close it and re-open then the aggregation is reset to Sum ... grrrr!


This will give us the following chart:


stack3.png


This is still editable by the chart dialog. It will stack the same ACT_LEV count values on top of each other for every STE. At this point we need to get counts for each of the possible values of ACT_LEV. We do this using an XPATH expression, its like an inline if or where condition e.g.

count(ACT_LEV where ACT_LEV = 'SHORT')

count(ACT_LEV where ACT_LEV = 'LNG')

count(ACT_LEV where ACT_LEV = 'OPEN')

count(ACT_LEV where ACT_LEV = 'OFFLINE')


In our XPATH we use a specific format:


count(ACT_LEV[.= 'SHORT'])

count(ACT_LEV[.= 'LNG'])

count(ACT_LEV[.= 'OPEN'])

count(ACT_LEV[.= 'OFFLINE'])


  • The [ ] enclose the command

  • '.' refers to the current value ie ACT_LEV

  • Then we test against a hard coded value of ACT_LEV


Now we need to get at the chart commands.

In Word 2000/2/3 - double click the chart image and go to the Web tab

In Word 2007 - right click the chart image -> Size -> Alt Text tab.


Rather than work in the window, copy and paste the text into the main document, its much easier. You'll get the following:


chart:

<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT" graphType="BAR_VERT_STACK"><LegendArea visible="true" /><LocalGridData colCount="{count(xdoxslt:group(.//ROW,  'STE'))}" rowCount="4"><RowLabels><Label>Short</Label><Label>Long</Label><Label>Open</Label><Label>Offline</Label></RowLabels><ColLabels><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Label><xsl:value-of select="current-group()/STE" /></Label></xsl:for-each-group></ColLabels><DataValues><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData></DataValues></LocalGridData></Graph>


I have highlighted the pieces we need to change in bold. Right now they are all showing the same value, ACT_LEV. Just make the changes to add the XPATH expressions e.g. count(current-group()/ACT_LEV[.='SHORT']) 


chart:

<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT" graphType="BAR_VERT_STACK"><LegendArea visible="true" /><Y1Axis majorTickStepAutomatic="false" majorTickStep="1.0"/><LocalGridData colCount="{count(xdoxslt:group(.//ROW,  'STE'))}" rowCount="4"><RowLabels><Label>Short</Label><Label>Long</Label><Label>Open</Label><Label>Offline</Label></RowLabels><ColLabels><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Label><xsl:value-of select="current-group()/STE" /></Label></xsl:for-each-group></ColLabels><DataValues><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='SHORT'])" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='LNG'])" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='OPEN'])" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='OFFLINE'])" /></Cell></xsl:for-each-group></RowData></DataValues></LocalGridData></Graph>


To get the lines on the Y-Axis for each unit we added the following line


<Y1Axis majorTickStepAutomatic="false" majorTickStep="1.0"/>


Its in bold above for the position it needs to be set at.

Once you have made the changes to all four to handle the four different values. Copy and paste the XML back into the Web/Alt Text tab


Now you will have a chart that looks correct:


stack1.png


If the template builder throws and error similar to Error in expression: 'count(current-group()/ACT_LEV[.=Â’SHORTÂ’])'. It means that you have used smart quotes '' rather than plain ol quotes in the XPATH. Just open the Web/Alt Text tab, then delete and replace the quotes. In the dialog it will only use plain quotes.


If you need to make the chart larger or smaller, just resize the chart image. It will distort the chart in the template but should come through crisp in the output.


Don't forget, if you open the chart with the chart dialog it will blow away your customizations. It might be a good idea to store the base chart format without your changes so you do not have to re-build it each time. You could add it to the end of your template and wrap and if statement around it so that its hidden at runtime.


I actually built a self explaining template for all of this, rather than then copy and paste from Word over the to the blog client I have. I just ran the template to HTML, then copied and pasted the whole thing from firefox into the blog article, have you seen Word HTML ... bleeeeuch!. It came across pretty well, just a different font. If you are interested in the template and data you can get them

here.
Happy Charting!.
Comments:

Hello, I was wondering if Org Chart is possible using BI Publisher? thanks

Posted by deep on July 22, 2010 at 08:28 AM 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
« July 2015
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
31
 
       
Today