Charts with Top 5 + Rest of World

This is Klaus from the BI Publisher team with a write-up on a recent customer request.

I have been asked on how to implement a chart that shows the top 5 customers (district, countries…) in a pie chart and then have a special slice that accumulates all the remaining customers.

I think this is very useful – pie charts with 20 or more slices are hard to read. If you just limit the pie chart to the top 5 customers, then the percentage value shows the percentage of a slice as a percentage of the top 5 and not of the complete data set.

A good approach is to keep the remaining customers in the chart in a summarized form.
I have seen these summarized columns called “Rest of World” in a chart showing countries.

You can download the demo files here. Please make sure that you copy the subtemplate.xsl to C:\test or change the import statement in the first form field to the correct location.

The solution requires XSL coding and the use of a sub template. These techniques are useful solve many complicated reporting problems. Subtemplates are a good technique to keep larger code blocks outside of the word document - we plan to add more support for sub templates in a coming release.

We are working for this example with the Paint Exec sample data that is used in the sample report /Executive/Revenue by Region. The data has the following form.

<ROWSET>
<ROW>
<Markets.District>CHICAGO DISTRICT</Markets.District>
<Products.Brand>Enterprise</Products.Brand>
<Measures.Dollars>1555548.0</Measures.Dollars>
</ROW>
</ROWSET>

We start with the Pie chart that is generated by the template builder for Word:

Regular BI Publisher Pie Chart

Top 5 Chart

Let’s first limit the pie chart to show only 5 slices.

1) First we fix the number of data rows for BI Beans to 5 – instead of the complete data set.

chart:
<Graph depthAngle="50" seriesEffect="SE_AUTO_GRADIENT" graphType="PIE">
<LegendArea visible="true" />
<LocalGridData colCount="1" rowCount="5">

2) We change from ordering the labels by Market District to ordering them by measure.Dollars per district.

<RowLabels>
<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)" data-type="number" order="descending"/>

3) We need to select the first 5 elements – this is not mandatory for now but will necessary for adding the rest of world. Note: XSL requires us to use the escape sequence &lt; for “,”.

<xsl:if test="position()&lt;6">
<Label><xsl:value-of select="current-group()/Markets.District" /></Label>
</xsl:if>
</xsl:for-each-group>
</RowLabels>
<DataValues>

4) We sort the data elements by revenue per district.

<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)" data-type="number" order="descending"/>

5) We select only the first 5 elements – this is not mandatory for now but will necessary for the next step

<xsl:if test="position()&lt;6">
<RowData>
<Cell><xsl:value-of select="sum(current-group()/Measures.Dollars)" /></Cell>
</RowData>
</xsl:if>
</xsl:for-each-group>
</DataValues>

The resulting graph looks less cluttered – but it does not show percentages of the total:

Chart with top 5


Rest of World
Now we need to calculate the remaining values.

6) Any complicated calculation should better be performed in a separate XSL stylesheet. We write a function in XSL called “others” that calculates the total of the remaing members.

<?xml version="1.0" encoding='utf-8'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" version="2.0">
<xsl:output method="xml" encoding="UTF-8"/>

<xsl:template name="others">
<xsl:variable name="list">
<xsl:for-each-group select="/ROWSET/ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)" order="descending" data-type="number"/>
<v><xsl:value-of select="sum(current-group()/Measures.Dollars)"/></v>
</xsl:for-each-group>
</xsl:variable>
<xsl:value-of select="sum($list/v[position()&gt;5])"/>
</xsl:template>
</xsl:stylesheet>

This template groups all elements by “Market.District” and sort the resulting values by the sum of “Measure.Dollars” per District. The result is stored in the variable v.

Then we sum all elements in the variable with a position greater than 5 with the expression:

<xsl:value-of select="sum($list/v[position()&gt;5])"/>

The escape sequence “&gt;” means greater then or “>”. We save this XSL-stylesheet with charater set UTF-8 - using for example notepad – to the location:

C:/test/subtemplate.xsl


7) We need import this sub template from the RTF template. For testing we access the file from the file system – for the production implementation to store in on a web server and access it from there.

We add the following statement to the beginning of the document (not the chart!) to import the sub template:
<xsl:import href="file:///C:/test/subtemplate.xsl"/>


8) We increase the row Count from 5 to 6 – replacing step 1- for the additional “all others” slice.

<LocalGridData colCount="1" rowCount="6">

(9) We need to add a label for the summary of the remaining district

<RowLabels>
<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)" data-type="number" order="descending"/>
<xsl:if test="position()&lt;6">
<Label><xsl:value-of select="current-group()/Markets.District" /></Label>
</xsl:if>
</xsl:for-each-group>
<Label>ALL OTHER'S</Label>
</RowLabels>

(10) Finally we need to add another data row with the sum of all other districts by calling the function “others” defined in (6).

<RowData><Cell><xsl:call-template name="others"/></Cell></RowData>
</DataValues>
</LocalGridData>

When we run the chart we see the chart with a new entry and the correct percentages:

Chart with top 5 and Rest of World

Below is the final code for the chart above.

chart:
<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT" graphType="PIE">
<LegendArea visible="true" />
<LocalGridData colCount="1" rowCount="6">
<RowLabels>
<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)"
data-type="number" order="descending"/>
<xsl:if test="position()&lt;6">
<Label><xsl:value-of select="current-group()/Markets.District"/></Label>
</xsl:if>
</xsl:for-each-group>
<Label>ALL OTHER'S</Label>
</RowLabels>
<DataValues>
<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)"
data-type="number" order="descending"/>
<xsl:if test="position()&lt;6">
<RowData>
<Cell><xsl:value-of select="sum(current-group()/Measures.Dollars)"/></Cell>
</RowData>
</xsl:if>
</xsl:for-each-group>
<RowData><Cell><xsl:call-template name="others"/></Cell></RowData>
</DataValues></LocalGridData></Graph>

I hope this is helpful,

Klaus Fabian
Senior Development Manager, BI Publisher;

Comments:

Isn't this a lot of work for something that you could do in a SQL statement!?

Posted by Matt on July 02, 2009 at 10:49 AM MDT #

With all these slices in one pie chart, aren't you going to get the pie chart expanding at run time? My pie chart expands with just one slice larger at run time than it looks on the template. I tried using: <PieFrame> <Rect height="495" width="651" ⁄> <⁄PieFrame> but it seemed to ignore it. - Jenny

Posted by Jenny Woodman on September 04, 2009 at 12:49 AM MDT #

Zune and iPod: Most people compare the Zune to the Touch, but after seeing how slim and surprisingly small and light it is, I consider it to be a rather unique hybrid that combines qualities of both the Touch and the Nano. It's very colorful and lovely OLED screen is slightly smaller than the touch screen, but the player itself feels quite a bit smaller and lighter. It weighs about 2/3 as much, and is noticeably smaller in width and height, while being just a hair thicker.

Posted by Chicago Bears jerseys on September 18, 2010 at 04:33 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
« 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