Oracle BI Publisher Blog

Got a chartin' question today, 'can we build Bubble charts with BIP' - the short answer is 'Yes.' Those of you that have spoken to me will know Im no good at short answers - so here comes the long answer and a 'how-to' 

For those of you that have not seen or heard of them.

A Bubble chart is a variation of a Scatter chart (if you dont know what a scatter chart is ... go look it up :) in which the data points are replaced with bubbles. A Bubble chart can be used instead of a Scatter chart if your data has three data series, each of which contains a set of values.

For example, the following XML contains values for three types of data not including the product line name: number of products in product line, dollar value of sales for the product line, and percentage size of market share for the line - not grate data but you get the idea, I hope.

<BUBBLES>
 <BUBBLE>
  <PROD_LINE>Electronics</PROD_LINE>
  <PROD_NUM>10</PROD_NUM>
  <SALES>100000</SALES>
  <MKT_SHARE>15</MKT_SHARE>
 </BUBBLE>
 <BUBBLE>
  <PROD_LINE>Household</PROD_LINE>
  <PROD_NUM>13</PROD_NUM>
  <SALES>135000</SALES>
  <MKT_SHARE>25</MKT_SHARE>
 </BUBBLE>
 <BUBBLE>
  <PROD_LINE>Garden</PROD_LINE>
  <PROD_NUM>20</PROD_NUM>
  <SALES>200000</SALES>
  <MKT_SHARE>30</MKT_SHARE>
 </BUBBLE>
</BUBBLES>

Now, we can plot these as a bubble chart - there are 3 components to the bubble plot the x-axis and y-axis position and the diameter of the bubble. Sadly, the charting wizard in the Template Builder does not handle Bubble charts so we have to get our hands dirty and get into the chart XML definition. We need to pass the data above to the charting engine, its needs to be in a specific format.

<DataValues>
 <RowData>
  <Cell></Cell>
 <RowData>
</DataValues>

The RowData group needs to repeat for as many data points or bubbles we need to plot. For our data, we have 3 bubbles so we would need to pass.

<DataValues>
 <RowData>
  <Cell>10</Cell>
  <Cell>100000</Cell>
  <Cell>15</Cell>
 <RowData>
 <RowData>
  <Cell>13</Cell>
  <Cell>135000</Cell>
  <Cell>25</Cell>
 <RowData>
 <RowData>
  <Cell>20</Cell>
  <Cell>20000</Cell>
  <Cell>30</Cell>
 <RowData>
</DataValues>

The first Cell value maps to the x-axis - the Number of Products in the product line
The second Cell value maps to the y-axis - the total sales
The third Cell value maps to the diameter of the bubble - the market share
These are all bound to the product lines in the chart legend.

Now its not so tough to get the data we have mapped to the required format in the chart: definition. Heres the bubble chart definition.

chart:
<Graph graphType="BUBBLE">
 <X1Title text="No of Products" visible="true"/>
 <Y1Title text="Sales" visible="true"/>
 <Title text="Market Share Analysis" visible="true" horizontalAlignment="CENTER"/>

 <LocalGridData colCount="{count(.//BUBBLE)}" rowCount="{count(.//BUBBLE)}">
 <RowLabels>
  <xsl:for-each select=".//BUBBLE">
   <Label>
    <xsl:value-of select="PROD_LINE"/>
   </Label>
  </xsl:for-each>
 </RowLabels>
 <ColLabels>
  <xsl:for-each select=".//BUBBLE">
   <Label>
    <xsl:value-of select="PROD_LINE"/>
   </Label>
  </xsl:for-each>
 </ColLabels>
 <DataValues>
  <xsl:for-each select=".//BUBBLE">
   <RowData>
    <Cell><xsl:value-of select="PROD_NUM"/></Cell>
    <Cell><xsl:value-of select="SALES"/></Cell>
    <Cell><xsl:value-of select="MKT_SHARE"/></Cell>
   </RowData>
  </xsl:for-each>
 </DataValues>
 </LocalGridData>
</Graph> 

Ignore the top part right now and focus on the <DataValues> section:

 <DataValues>
  <xsl:for-each select=".//BUBBLE">
   <RowData>
    <Cell><xsl:value-of select="PROD_NUM"/></Cell>
    <Cell><xsl:value-of select="SALES"/></Cell>
    <Cell><xsl:value-of select="MKT_SHARE"/></Cell>
   </RowData>
  </xsl:for-each>
 </DataValues> 

This XSL will map the current data into the required format for the charting engine. The rest of the XML for the chart is regular stuff just need to set the graphType to "BUBBLE" and we're done.

Yes, bubble charts are possible - just requires some effort, that I hope we'll eliminate in a future release of the Template Builder but at least you now know how to build em. Template, XML and output here. Before you ask, I have tested bubbles back to release 5.5 - not so good quality as SVG output was not supported back then but it works.

Update

A couple of follow ups ensued which are worth sharing:

How do I remove the grid lines and big quadrant lines?

To remove the regular gridlines:

<O1MajorTick visible="false"/>
<X1MajorTick visible="false"/>
<Y1MajorTick visible="false"/>
<Y2MajorTick visible="false"/>

These will turn off all grid lines - but not the 'quadrant' lines. For those you need:

<QuadrantLine lineColor="#64ff" visible="false"/> - I just left the lineColor attribute in there for those of you wanting to chage the color rather than remove them.

How can I change the Bubble colors?

Inside the graph tag you can create a set of 'series' to hold the colors.

<SeriesItems>
<Series id="0" color="#cc0000"/>
<Series id="1" color="#ffcc00"/>
</SeriesItems>

You'll need as many id's as data points or as you want different colored bubbles. If there are more bubbles than colors then the colors will loop around again.

I have been exchanging mails with a colleague in the field regarding the 'smoothness' of our charts. By default in our PDF outputs we generate SVG chart output to get the best quality output. His customer was still not happy - he sent me a copy of their chart:

 and a snap shot of their problematic output.

Now I got to see the issue - they are already using the SVG output but at 1600% resolution you can see that the chart lines are actually made up of individual smaller lines. Im not SVG expert but I assume this is the easiest way to implement a chart - using an arc to get the curves smoother would take alot of time, working out which way the curve ought to go.

Im also assuming that the customers printers are of such a resolution that the quality of the print outs makes the chart all but unreadable.

Its a limitation of the BIBeans charting engine that the chart is not smoother - in the engine's defense there are 100s of data points in this chart. Looks like share price or similar chart with so many points. This got me thinking about the finance charts you get to see on Yahoo and Google - the newer charts are very cool and very Flash like (more on that another day) - both sites a feature a 'time slider' allowing you to move a time slice across the total stock chart, heres the Oracle chart on Yahoo - check out the Time range widget bottom right corner.

If you play with the slider you'll hopefully notice that the charting engine appears to have some algorithm to reduce the numer of data points as the range is extended, effectively 'smoothing' the chart. If not, check out the video, focus on the chart as the slider moves, you'll see the chart suddenly 'smooth' as the data points are effectively reduced.

Chart Smoothing Video

Now this would be a fantastic enhancement to the BIBeans charting engine - not there yet thou. An area I'll try to investigate or even better you can investigate if you have nothing better to do over the holidays is to come up with some algorithm in the template or an extension to check how many data points there are and reduce them if necessary to give the chart a smoother look . Not going to be easy but might prove a very useful feature in lieu of the charting engine enhancement.

I spent some of today trying to solve a none too common (that I have seen) problem, that is to add a gantt chart to your BIP or BIEE outputs. BIBeans, the charting package we use does not support them - at least not in a way we can use them, they have a solution for EBS OAF pages but not for a report.

For the uninitiated a gantt chart ( I dont know why but my brain wants to call it a 'gnat chart'- no disrespect Mr Gantt) shows a timeline for say a project and all of its components, when will one sub project end so another can start - the best known and the bane of my life a few years back is/was MSProject - we had a monster of a project plan that stretched into the distance and over the horizon at times, were the developers even going to be here in 12 months time? This was back in the dot com boom  - 'interesting times'as they say in Ankh Morpork!

The example above it pretty simple but you get the idea. I sat and thought for a while and came up with a couple of options.

1. Get 'noodling' (non-technical term for hacking) with other bibeans charts and try and combine a few together to get the desired result.

2. Have a bash with the MSWord shapes to create a gantt output.

Option 1 was going to be lots of work as the BIBeans XML chart defintion is not well documented - I dont want to wade through that DTD again. I have multiple chart entries on this blog to try and clear the mist but my glasses are still fogged up. There is some good news on the horizon on that front - the new version of the template builder has taken a huge stride forward when it comes to inserting charts. It even supports inserting the bubble charts I wrote about a while back.

Option 2 was much more palatable for me and ought to be some fun. I have not touched the shape support for a while and I have been looking for a good business requirement for them ever since we released them other than the gauge I built last year. Should just take a bit of calculation to get the shapes to behave and we would be good to go.

For those of you that dont know, you can create drawings in Word, using predefined or custom shapes, you can combine them together to form more complex objects, such as the gauge. Now Publisher, gives you the ability to then manipulate the shapes or sub shapes - you can duplicate, add text, skew, move, even write text along a line. If you take another look see at that gantt chart up there and break it down, its nothing more than a rectangle thats moved, stretched and duplicated down and across the page.

So we dont get bogged down in what the data is I thought I come up with a calendar report of someones weekly meeting schedule - its a nice straightforward example.

Thats the output from Publisher - not bad, needs a little tidying  but for a first effort and as a mean to convey how to do it, its great.

How did I do it? - well all of our reports need some data so I knocked up some XML.

<MEETINGS>
 <DAY>
  <NAME>Monday</NAME>
  <MEETING>
   <START>9</START>
   <DURATION>1</DURATION>
   <DESCRIPTION>Team Meeting</DESCRIPTION>
  </MEETING>
  <MEETING>
   <START>11</START>
   <DURATION>2</DURATION>
   <DESCRIPTION>Discuss BIP</DESCRIPTION>
 </MEETING>
 <MEETING>
   <START>14</START>
   <DURATION>1</DURATION>
   <DESCRIPTION>Customer Call</DESCRIPTION>
 </MEETING>
</DAY>
...

  Of course I built it simple but there are 3 pieces of information I need about the meeting, the day, the time and the duration - I added the description cos I was getting 'fancy' - a non technical term for 'showing off'

The template could not be simpler in its layout.

So my time slots were fixed, we could have made them dynamic but I want to focus on the shape manipulation. We have a simple for-each loop in the first cell to get the days of the week in rows down the page. Then there is that yellow box - if you check our docs you'll see that to manipulate the shape we embed some commands into the Web tab of the object's properties. In this case I had:

<?for-each@shape:.//MEETING?>
<?shape-offset-x:((number(START)-8) * 65)?>
<?shape-size-x:DURATION?>
<?end for-each?>

The for-each@shape tells our parser that we want to generate shape instances for this loop.

The shape-offset-x - defines how much to offset each shape in the loop. The actual offset value '((number(START)-8) * 65)' is not that tough to work out. Our starting hour of the morning is 8am and the shape is sitting on the border of the 8am cell so we need the shape to be offset from that point - remember we know when the meeting is so our offset is going to be 'start time - 8'. Need to be careful here to use the 24 hour clock a meeting at 1pm using the 12 hour clock yields some interesting results. Finally, the 65 is the point width of the columns in the table row. To get this value, just flip Word to measure in Points rather than Inches or CMs - Tools > Options > General. Then get into the table properties and into the Column tab.

The shape-size-x:DURATION defines how big the rectangle should be - we have the duration of the meeting. So a 2 hour meeting will result in a block twice the size of the original to fill the 2 hour slot.

And thats it to get the blocks repeating across and down the page.

To get 'fancy' and add the description we need to build up a composite shape. You could just use the Add Text command on the shape but you get some funky font stretching when the shape has to be enlarged. So go with the composite - you need the basic rectangle and a text box.

In the rectangle you need -

<?shape-size-x:DURATION?>

- to get the right size

In the text box you need -

<?shape-text:DESCRIPTION?>

- to get the description

Now Group them together by selecting both, right clicking > Grouping > Group. Then add the following to the composite:

<?for-each@shape:.//MEETING?>
<?shape-offset-x:((number(START)-8) * 65)?>
<?end for-each?>

This gets the whole thing to repeat across the page as needed, et voila!

I should point out that when you see the template you'll see that the shape is not exactly at the 8am cell edge - its slightly offset. Its a feature, we want you to play with your creations until they are perfect. Seriously, Im not sure why it needs it, something funky in Word maybe - you just need to nudge it around to get it perfect. You can get the data, template and output here. 

From here, Im moving on to a more complex output with dynamic rows and columns and then onto the fabled org chart which, with a little jiggery pokery I think I can tease out of our shape engine.

Happy Gantting!

Another charting 'tidbit' today  - for those of you in the UK substitute the 't' - its too rude for American ears/eyes so we use a 'd' here ... 'phnarr, phnarr' - sorry America another UK joke, that is, unless you read one of the more boorish comics from the UK in which case, Go Finbarr!
 
I wish I could find a way to help you more to relieve the pain and misery that is the fine tuning of charts in BIP. There is a new chart dialog coming with a property palette for all the finite control you might need but thats a little ways out.
When I get questions, I resort to trawling throu the graph DTD to try and find what I need to get some niggly piece of text to behave as I need - its a slow and laborious process. 
I received a mail from Mayur from Keste, an Oracle partner yesterday asking:

I want a simple Bar chart (Single Bar) with numbers on top. See below image.


I have following code for the image on the template.

chart:

<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT">
<Title text="MBS Past 24hrs Dispatches" visible="true" horizontalAlignment="CENTER"/>
<MarkerText visible="true" markerTextPlace="MTP_TOP"/>
chart data definition ...
</Graph>

I am able to see the chart, with numbers. But numbers are with decimal points. Like 31.00, 20.00, 2.000 etc. I want to see number like 31, 20, 2, 13, 16 etc. I have tried to format using ViewFormat, but it痴 not working.

I have been playing with it on and off and getting annoyed, you may remember I documented the same thing for Pie charts a while back. Applying the same principles I started looking for the right elements to use in place of PieLabel - how about 'ColLabel' ... nope, not there, there is a ColLabels but thats no use. After a lot of frustration I came up with an idea this morning to load the DTD into XMLSpy and then convert it to a schema. XMLSpy has a nice expand and collapse feature for schemas that lets you see dependencies, etc. Within 2 mins I had the code I needed! 

So if you need to control the number format of your bar values then just add:

<MarkerText visible="true">
<Y1ViewFormat>
<ViewFormat decimalDigit="0" decimalSeparatorUsed="true" decimalDigitUsed="true"/>
</Y1ViewFormat>
</MarkerText>

under the <Graph> tag ... boo ya!

As a follow up to yesterday's mis behavin bar charts Mayur had a follow up question. The resulting chart would have looked like this:

There was a followup request to have each bar take on a different color like this:

The simplest method is to add the following attribute to the Graph element e.g.

<Graph colorMode="COLOR_BY_GROUP">

This will change the colors for each bar - there is no further control over these colors without a little effort. But there is a way to define a set of colors to be used - this could be useful if you have a set of corporate colors that you want to use on all your charts. The BIBeans engine supports what they call 'styles' this is an XML definition of the look and feel for the charts from colors to fonts. The engine ships with a series of styles that you can apply to your charts - the latest Template Builder chart dialog allows you to pick one of these.

There are 13 predefined styles but you can create your own. Its a pretty simple XML file - I found it easier to take one of the delivered files and modified it - to get at the files you need to dig into the jar files that make up the chart engine. Its  going to depend on what flavor you're running to know where the jar is - check the classpath in most cases - if you get stuck drop me a comment.

Inside the bipres.jar file you'll find a set of XML files - just open the jar file with WinZip or similar and you'll find them. You can then extract one of them and get busy ...

There is a mass of things you can change in there - I was interested in the colors, just find the following section:

<SeriesItems defaultBorderColor="#0" defaultBorderTransparent="false" borderUsingDefaults="false" 
 defaultColor="" defaultMarkerShape="MS_AUTOMATIC" defaultLineWidth="3" defaultFitlineType="FT_NONE">
<Series id="0" color="#a20346"/>
<Series id="1" color="#88575"/>
<Series id="2" color="#1008a"/>
<Series id="3" color="#cc9900"/>
<Series id="4" color="#d0146b"/>
<Series id="5" color="#694eaf"/>
<Series id="6" color="#dc5c15"/>
<Series id="7" color="#758b27"/>
<Series id="8" color="#a20346"/>
<Series id="9" color="#88575"/>
<Series id="10" color="#1008a"/>
<Series id="11" color="#cc9900"/>
...
</SeriesItems>

Those hex values map to the color palette thats available to the charting engine. This is where things get a little tedious you are going to have to get a HEX color map, maybe here and then the really hard part getting everyone to agree on the color scheme to use. This is the toughest thing in the world - been there done that, had the arguments but did not get the T shirt - we couldnt agree on the color.

Once you have the file updated you need to merge it back into the jar file - WinZip can help here again - just remember when you extract the XML to be modified you get it out using the full path info and save it with a different name - then when you merge it back in use the 'full path info' flag to get it back into the jar under oracledssgraphstyles.

Now to test it, in your XML chart definition you just need to add a new attribute to the Graph element:

stylePath="/oracle/dss/graph/styles/greeeen.xml"

So now you'll have something like:

chart:
<Graph stylePath="/oracle/dss/graph/styles/greeeen.xml" colorMode="COLOR_BY_GROUP">

You can get the template here and you'll get something like this

yes, the color differences are very subtle - but Im just that kind of guy and I like green so dont argue!

Its been a week almost full of chart tips and hints, except for the Tweeting post on Monday, where are you all? To round off the week, more charting, this is a new chart to me - they are called Spark charts, when you see one you kind of see why they are called that.

Mine are rather long sparks but you get the idea. Its really just a series of line charts with all the trimmings stripped out such as borders, grid lines, axes lines and values etc.
I got an inquiry or maybe it was a challenge from Bryan (one of the BIP bloggers - who we have not heard from recently :0) in the sales team. He cc'ed me and told a colleague that sparks might be possible with BIBeans and BIP but the man to know would be Tim Dexter - a challenge if ever I heard one.

Its not that tough to do, its a matter of turning off all the lines and text and using a line chart. Again, the Chart dialog in the Template Builder is going to let you down here - just wait for that property palette version - even I will stop pulling my hair out trying to get these things working. Here's the XML anyways:

<Graph version="3.2.0.22" autoLayout="AL_NEVER" markerTooltipType="MTT_NONE" markerDisplayed="false"
 graphicAntialiasing="true" textAntialiasing="true" graphType="LINE_VERT_ABS"
 frameSizeAutomatic="false">
<ImageSize height="80"/>
<LegendArea visible="false"/>
<O1Axis visible="false"/>
<O1TickLabel visible="false"/>
<PlotArea borderTransparent="true" fillTransparent="true">
<Rect height="24000" width="31842" x="-15889" y="11807"/>
</PlotArea>
<PieFrame>
<Rect height="22400" width="20800" x="-11381" y="12339"/>
</PieFrame>
<SeriesItems>
<Series id="0" color="#66"/>
</SeriesItems>
<Y1Axis visible="false"/>
<Y1MajorTick visible="false"/>
<Y1TickLabel visible="false"/>
<Y2MajorTick visible="false"/>
</Graph>

notice the 'visible' attribute being set to false for the axes related stuff and the plot area border set to transparent - thats it really. The only other element of note is the 'Rect' in the PlotArea - this allows the chart to fill the whole area in the dummy image in the template. Its fiddly but resonably quick to set.

The other cool bit is to get the charts repeating in the table for each range - thats as simple as putting the chart inside a repeating group in a table just like a field.

You can get the template, data and output here. Next week ... more charts, nah Im kidding even I have had enough for a while!

Not so much a how to today - that comes tomorrow; more along the lines of comment. Im charting again but looking at some interesting data. The data in question is the US Mortgage rate - I was chatting to Leslie our documentation guru last week. She mentioned that she had been shopping around for a refi on her house. One vendor told here not to bother right now and to wait until September. Pourquoi? Asks Leslie - 'because the rate always falls in September in an election year!'

Huh? So the mighty Fed that sets interests rates and acts, I thought, independently of government is swayed by 'election years' and lowers the cost of borrowing just before the election and affects the worlds economy to boot. Who benefits? other than the consumer that is. Maybe the fastest candiate off the blocks can claim that they helped sway the Fed in their decision. I couldn't see it.

After a bit more discussion it was thought that maybe it only falls when an incumbent is running for a second term. Maybe George had a quick word with Greenspan in 2004, 'lower the rate there feller, just for old times sake?' Reminds me of our yo-yoing gas (petrol) prices here where Im damn sure the government steps in when OPEC gets a little too greedy.

Being the consumate skeptic I was out googling, looking for historical rate numbers. I alighted upon the USTreasury web site which posts such numbers and they have rates back to 1990 in an XML format to boot; even better, the XML has been generated by an old friend - Oracle Reports.

Time to get busy with a template and check out this claim. Here's the full 17 years worth:

Its a busy chart with more than 4500 data points - I'll address that tomorrow.

Looking in at Nov 2004 and Nov 1996 for messers Bush and Clinton

Bush Incumbent

Clinton Incumbent

I have included some data leading up to the election date but I can not see a change either way? Its not a scientific study but maybe the incumbent does not have the influence afterall ... phew! The only thinkg I can see is a steady rise continuing after Bush got back in, there may even be a jump ... but Im an impartial observer and quite obviously know nothing! 

Tomorrow the crux of why I was looking at this data using our charting engine - how can we smooth it out a bit for users?

 

Sometime in the dim and distant past I wrote about mortgage rates and generating charts for masses of data. It was not actually that long ago last Monday in fact but the intervening 7 days have seemed like months. The hacking cough, aching bones, 'flesh creep' and feeling tired all the time - never felt so bad in my life. I now understand all the fuss about getting a flu shot in the fall, although it appears it would not have done me any good this year. I got Type A which flufacts.com states.

Influenza Type A is the most common and also the scariest of the three influenzas, causing the most serious epidemics in history.

Now, I was not scared but bloody hell it was rough!

Getting back to charts, the mortgage rate chart image I posted looked like this:

In the blown up inset you can see that the line is actually made up of multiple rectangles - thats the SVG output we get from the BIBeans chart engine. Its fine for charts with none too many data points but this chart has around 4500 points which leads to a very 'choppy' chart that does not look good nor prints too well either.

I came up with a way to allow you to smooth the chart. Now, when shared with the rest of the development team I got flamed by a couple of them - maybe flamed is too strong a word - let's say lightly toasted!

My approach was to allow the developer/user to specify a smoothing value for the chart, let's call it 'x'. In the chart definition I then used this value to skip every 'x' values in the data set. So for 4500 data points with a smoothing value of 10 I ought to get 450 points - you can see why I got 'toasted'. A better way would be to calculate an average across those 10 skipped values and use that. Thats a fair point but on a chart with 4500 data points and you want it smoothed so you users can get a feel for the data do you want complete accuracy or a visualization so that they can see the 'rough' view of the data?
If the former, then get the extraction portion of the report to do the calculation for you - thats a lot of heavy lifting in the template to calculate that - you could build an extension function to do it I guess - maybe look into that another time. If you want the latter, read on ...

Once the smoothing is implemented you can get something like this:

I admit, there is some data lost in the smoothing but its a better looking chart, IMHO, from a users point of view. Its going to depend on the smoothing value you assign to the data. If you compare the two you can see some of the smaller peaks and troughs are lost. Reducing the smoothing value brings them back but things get choppy again so its a balancing act. Playing with it a value of 20 is about right in this case.  I think even an average value based chart would still lose some of the finer detail. If you're still with me, how did I do it?

First I created a smoothing parameter for the template

<?param@begin:chartLimit;'20'?>

Its got a default value but this can be overriden at runtime.

Now we have to get our hands dirty and get into the chart code. I used the chart dialog to start out and then got stuck in:

chart:
<Graph graphType="LINE_VERT_ABS" >
 <LocalGridData  
       colCount="{count(.//G_BID_CURVE_DATE[position() mod number($chartLimit)=0])}"
       rowCount="1">
 <RowLabels>
 <Label>
 BC_6MONTH
 </Label>
 </RowLabels>
 <ColLabels>
 <xsl:for-each select=".//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]">
 <Label>
 <xsl:value-of select="BID_CURVE_DATE" />
 </Label>
 </xsl:for-each>
 </ColLabels>
 <DataValues>
 <RowData>
 <xsl:for-each select=".//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]">
 <Cell>
 <xsl:value-of select="LIST_G_BC_30YEAR/G_BC_30YEAR/BC_6MONTH" />
 </Cell>
 </xsl:for-each>
 </RowData>
 </DataValues>
 </LocalGridData>
</Graph>

 

the parts to take note of are, surprisingly the parts in red, duh! They all share the same common calculation:

.//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]

This is essentially only allowing values through where the current record position, when divided by the chartLimit value = 0. So for a chartLimit value of 10 we would get the following points: 10, 20, 30 and so on. The first instance calculates the number of points to be plotted and is assigned to the colCount attribute - the charting engine needs this so dont ignore it. The second is for the chart labels - optional. Lastly the actual data point to be plotted. Simple eh?

At runtime, depending on your flavor the chartLimit parameter can be passed to the template to 'smooth' the chart to the users preference. I have zipped up a standalone report definition here. Those of you on an Apps flavor can use the template and sample XML to test it thru your application. 

 

Some of you may have seen the 'shape' support we have with our RTF templates in the documentation, some of you may have played about a bit with it and thought, 'OK, so what can I do with them from a business perspective?' Some of you may have found uses for them, if you have, please share with it the class!
Since they were introduced as a bit of a side project from Edward (back then 'Mr RTF Template') I have always struggled a little when talking about the shape support, yeah, its cool but what can I use it for. I have written about using them to build a gantt chart and we have seen a few other applications for them - overall they fall into the 'well I cant build what I want in your charting engine so I'll have a stab at shapes!'

This past week Mike, our Product Manager dropped a good one into our respective laps. Do we have the ability to generate this:

I took out the performance measure titles but you get the idea.

There is quite a lot going on here, it was funny to see it, I had just seen something very similar as a requirement from another customer. They had actually abandoned they're visualization because it was too confusing. This one seemed much easier to read although technically a little more complex.

I did not think our current charting engine was up to it, maybe some combination of a couple of charts overlaying each other would get it done but it gets messy very quickly doing that. Shapes was an alternative that might just work, all I would need would be some background table, then a couple of yellow triangles, a black one, a circle and a horizontal line - easy!

Did not have any data to work with so came up with some and made some assumptions:

<PERFORMANCE>
<NAME>c.Makes the coffee really well</NAME>
<VALUE_SELF>3</VALUE_SELF>
<EVAL_START>2</EVAL_START>
<EVAL_END>3</EVAL_END>
<COG>2.7</COG>
</PERFORMANCE>

Looking at the graphic you can hopfully work out how the data maps to an 'icon'. 'COG' is the 'Center of Gravity', an average of the evaluators scores for a given metric. The horizontal line joins the start and end of the evaluators range.

The RTF template for the chart is pretty simple really.

 

The for-each field is looping over the PERFORMANCE loop, the shapes are sitting atop each other with the following code:

Yellow Triangle1	<?shape-offset-x:EVAL_START * 104.7?>
Yellow Triangle2	<?shape-offset-x:EVAL_END * 104.7?>
Black Triangle	<?shape-offset-x:COG * 104.7?>
Circle	<?shape-offset-x:VALUE_SELF * 104.7?>
Line	<?shape-offset-x:EVAL_START * 104.7?> <?shape-size-x:number(EVAL_END) - number(EVAL_START)?>

Straightforward shape stuff, the shape-offset-x moves the shape along the x (horizontal) axis and the shape-size-x (for the line) extends it. Whats the 104.7 for then? Thats the fun part of shapes, in this case its the multiplier to move the object 'x' pixels - I needed to get the shape to sit on the appropriate vertical line and after a litte trial and error I got to 104.7 pts - its not quite there but a little more tweaking will get it spot on.

Shapes are a little funky, they float around on top of the rest of the layout, so our RTF engine has to find them and pin point their position - its not always spot on so you need to do a little 'noodling', a technical term, to get them to the right position. Thank heavens for the template builder to let me 'noodle - test', 'noodle-test', etc.

Here's the final output in PDF

Its close, I missed the last two columns out, thats just simple conditional formatting - I wanted to concentrate on the shape manipulation functionality.
If you get a tough visualization from your users and think how the heck can I do that? Get on and shape up!

OK, I lied, Im squeezing one more in before the migration ...

Got a couple of bugs today asking about how to add and change chart titles. Its not tough, but for now its a code update. Once you have the base chart in your template, double click the image to get the Word dialog up. Got to the Web tab to see the XML for the chart

Heres the code for various titles

<Graph version="3.2.0.22">

 X Axis Title

<O1Title horizontalAlignment="LEFT" text="My X Axis" visible="true">
<GraphFont name="Times New Roman" size="12" bold="false" italic="true"/>
</O1Title>

Chart Title

<Title horizontalAlignment="LEFT" text="My chart" visible="true">
<GraphFont name="Times New Roman" size="12" italic="true"/>
</Title>

Y Axis Title

<Y1Title horizontalAlignment="LEFT" text="My Y axis" visible="true">
<GraphFont name="Times New Roman" size="12" bold="false" italic="true" textRotation="TR_HORIZ_ROTATE_270"/>
</Y1Title>
</Graph>

Things are going to get better I promise, the next release of the template builder for Word 10.1.3.4 will expose these properties thru a dialog.

OK, thats it for a couple of weeks!

Some of you may have found the 'newish' Google Charting engine. I looked at it a while back not long after it came out. Looked cool but a little tough to use with Publisher. They have quite a few chart types and lots of bells and whistles for them. There are also some new and interesting additions, more on those later. Its still not 'easy' to integrate with Publisher but its doable. Im not suggesting you move across to Google charts - we can not support you for one thing but its an alternative that you might want to investigate.
You interact with the chart engine via a URL (there is no other interface) and it returns an image. That's easy enough for us to handle in the template, using our dummy image and url: approach we can construct the URL and replace the dummy image with the Google chart at runtime. The engine will be expecting data points and labels of course, this is a little more tricky as they need to be passed as delimited lists on the URL. Here's a full URL

http://chart.apis.google.com/chart?cht=bhs&chtt=Store+Sales&chs=600x400&chd=t:74,69,75,71,78,71,87,82,84,59,88,79&chxt=x,y&ch
xl=1:|Stores 1-50|Stores 51-100|Stores 101-200|Stores 201-300|Stores 301-400|Stores 401-500|Stores 501-600|Stores 601-700|Stores
701-800|Stores 801-900|Stores 901-1000|Stores 1000+

There's a lot going on in there but notice the delimited numbers (data points) and the delimited strings (labels). Everything else in there are just parameters such as chart type, colors, size and title. I'm not going to dwell on the chart parameter specifics, check out the Google docs. Im going to focus on generating the delimited strings in a template. Assume we have the following XML:

<STORES>
 <STORE>
  <RANGE>Stores 1-50</RANGE>
  <SALE>
   <WEEK>1</WEEK>
   <AMT>10</AMT>
  </SALE>
  <SALE>
   <WEEK>2</WEEK>
   <AMT>12</AMT>
  </SALE>
  <SALE>
   <WEEK>3</WEEK>
   <AMT>15</AMT>
  </SALE>
  <SALE>
   <WEEK>4</WEEK>
   <AMT>11</AMT>
  </SALE>
  <SALE>
   <WEEK>5</WEEK>
   <AMT>10</AMT>
  </SALE>
  <SALE>
   <WEEK>6</WEEK>
   <AMT>16</AMT>
  </SALE>
 </STORE>
...
...
<STORES>

I want to get a chart that lists the store ranges (RANGE) and their total sales (AMT) in a bar chart format. How do I get those delimited strings then? The following will create a variable named 'dList' and then creates the comma delimited list by looping over the STORE level and summing the AMT value. As long as its not the last value it will insert a comma:

<xsl:variable name="dList"> 
 <xsl:for-each select=".//STORE">
 <xsl:value-of select="sum(SALE/AMT)"/>
  <xsl:if test="position() != last()">
   <xsl:text>,</xsl:text>
  </xsl:if>
 </xsl:for-each>
</xsl:variable>

The above code generates: 74,69,75,71,78,71,87,82,84,59,88,79 For the labels we use:

<xsl:variable name="sList"> 
 <xsl:for-each select=".//STORE">
  <xsl:value-of select="RANGE"/>
  <xsl:if test="position() != last()">
   <xsl:text>|</xsl:text>
  </xsl:if>
 </xsl:for-each>
</xsl:variable>

This time we are delimiting with a pipe ('|') to get: Stores 1-50|Stores 51-100|Stores 101-200|Stores 201-300|Stores 301-400|Stores 401-500|Stores 501-600|Stores 601-700|Stores 701-800|Stores 801-900|Stores 901-1000|Stores 1000+ Then its just a case of building the URL:

concat('http://chart.apis.google.com/chart?cht=bhs&chtt=Store+Sales&chs=600x400&chd=t:'
,$dList
,'&chxt=x,y&chxl=1:|'
,$sList)

This will generate the correct URL for the chart, which we can then get into an RTF template by using a dummy image and then in the Web properties of the image using:

url:{concat('http://chart.apis.google.com/chart?cht=bhs&chtt=Store+Sales&chs=600x400&chd=t:',$dList,'&chxt=x,y&chxl=1:|',$sList)}

This will get us:

If you're testing in the Template Builder for MSWord and you are behind a firewall, you'll need to ensure you set the proxy correctly. Tools > Options > Preview > Java Option field:

-Dhttp.proxyHost=www-proxy.us.oracle.com -Dhttp.proxyPort=80

Google has a mass of parameters for their charts that you could quite easily make dynamic as you build the URL. If you're really committed to their engine you could build an extension to our XSLT engine to accept an XML stream much like the built in charts we provide. Here's the RTF and XML data.

Hot chart of the week this week is line charts - not just your single line oh no. Folks want multiple lines! Its not that tough and with the new Template Builder for Word (10.1.3.4) out its even easier - you should not have to dabble in the chart XML. For those of you on earlier versions of Publisher, you can use the latest builder. Just dont build any crosstabs with the crosstab interface. they will not work in your reports unless you have 10.1.3.4 on the server side. As an aside we now have multiple measure support for the crosstabs! If I assume I have the following data:

<SALES>
 <SALE>
  <YEAR>2006</YEAR>
  <SOFTWARE>1200</SOFTWARE>
  <HARDWARE>850</HARDWARE>
  <SERVICES>2000</SERVICES>
 </SALE>
 <SALE>
  <YEAR>2007</YEAR>
  <SOFTWARE>1000</SOFTWARE>
  <HARDWARE>800</HARDWARE>
  <SERVICES>1100</SERVICES>
 </SALE> 
 <SALE>
  <YEAR>2008</YEAR>
  <SOFTWARE>900</SOFTWARE>
  <HARDWARE>1200</HARDWARE>
  <SERVICES>1500</SERVICES>
 </SALE> 
 </SALES>

With the new Chart builder you can add the multiple measures to the line chart ...

... to get the desired result ...

For the hard core souls among you, here's the XML behind the multi line chart

chart:
<Graph graphType="LINE_VERT_ABS"><LegendArea visible="true" />
 <LocalGridData colCount="{count(xdoxslt:group(.//SALE, 'YEAR'))}" rowCount="3">
 <RowLabels>
  <Label>SOFTWARE</Label>
  <Label>HARDWARE</Label>
  <Label>SERVICES</Label>
 </RowLabels>
 <ColLabels>
  <xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <Label>
  <xsl:value-of select="current-group()/YEAR" />
</Label>
</xsl:for-each-group>
</ColLabels>
<DataValues>
<RowData>
<xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Cell>
<xsl:value-of select="sum(current-group()/SOFTWARE)" />
</Cell>
</xsl:for-each-group>
</RowData>
<RowData>
<xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Cell>
<xsl:value-of select="sum(current-group()/HARDWARE)" />
</Cell>
</xsl:for-each-group>
</RowData>
<RowData>
<xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Cell>
<xsl:value-of select="sum(current-group()/SERVICES)" />
</Cell>
</xsl:for-each-group>
</RowData>
</DataValues>
</LocalGridData>
</Graph>

Notice the multiple RowData entries, one for each line.
If you wanted to get even more fancy you can add marker shapes to the data points on your lines. We are not quite there with the chart builder interface yet but this is pretty simple, just add the following to your chart XML.

<Graph  markerDisplayed="true" >
<SeriesItems>
 <Series id="0" markerShape="MS_SQUARE"/>
 <Series id="1" markerShape="MS_CIRCLE"/>
</SeriesItems>
...
</Graph>

marker options are :

• MS_SQUARE
• MS_CIRCLE
• MS_DIAMOND
• MS_PLUS
• MS_TRIANGLE_DOWN
• MS_TRIANGLE_UP

Some of you will find the following attribute for the Graph element 'markerShapeInLegend="true"'. This should do what it 'says on the tin!'
Sadly in earlier versions of te charting library there is a bug preventing this - seems to affect Applications flavors the most. There is a bug for this and we are looking at getting a new chart library patch out.

Bit of a long hiatus or holiday since my last post. I recently moved to a new position and have been playing catch up ever since. Im now caught up (kinda), you won't get 4/5 entries a week but at least 2 or 3 :0)

To get back into the groove, we're back looking at charts. Essentially a colleague was looking for something like this:

Does not look that special but in the accompanying email the OWN_NUMBER column is meant to be a 'house' image. You'll also notice its laying on top of another column. Let me say right now, I dont think the 'house' can be laid on top of the column. Getting a house into the chart is doable thou.

There is a caveat, the charting engine can embed an image into the chart but it will stretch it based on the data. So be wary about what images you are going to use.

To add the images you are going to have to get your hands a little dirty in the chart code. You will need to add a new element to the XML.

<SeriesItems defaultBorderTransparent="true" borderUsingDefaults="true">
<Series id="0" color="#33ccff">
<SFX fillType="FT_TEXTURE" textureURL="file:d:\temp\house1.gif" textureDisplayMode="TDM_STRETCHED"/>
</Series>
<Series id="1" color="#cc33cc">
<SFX fillType="FT_TEXTURE" textureURL="file:d:\temp\house2.gif" textureDisplayMode="TDM_STRETCHED"/>
</Series>
</SeriesItems>

The SeriesItems has a couple of attributes, should be understandable. You then need to provide 'Series' items for the number of columns per data set you want to plot.

The SFX element is the one that pulls the images in, notice the textureURL attribute, it holds the path to the image you want to embed. The textureDisplayMode can take the following values TDM_STRETCHED | TDM_TILED. Ignore the second - the same SFX element can be used to add a chart background and you can stretch or tile it. Tiling is not going to be relevant here.

The final output looks something like this:

Bit stretched but you get the idea, sample files here. This will work with just about all versions of Publisher and BIBeans.

More charting goodness today with a cheesy title linking us to a cheesy song from big time brat packer, Dean Martin. I get shouted down by the rest of my family for belting out 'when the moon hits your eye ...' from the shower|kitchen|while driving|etc.

One of the gifts the boys got over the holidaze was 'Guitar Hero World Tour'. For the uninitiated, its a 'video game' whereby up to four players can play guitar, bass, drums or sing along to songs from nearly all genres. You are all in a band and have to play to audiences to earn money and to unlock more songs. Its all great fun, I get to strut my stuff in the basement while the boys simultaneously beat a poor drum kit to death and rip it up on a plastic guitar. Im pleased, for the most part, that they both play the 'real' instruments too, they just wont let me join their band!

The one drawback of the game, from the gamers perspective at least, is the lack of function to be able to load a song of choice and have its component parts i.e. drum, bass, etc, broken out for you. They have some funky, build your own tune thang but its not the same. My kids loved the idea initially, until they realized that they would need to break out the dickie bows and drum brushes while Dad crooned his way through Dean Martin's repertoire. I did try to cheer them up with the fact that I also loved The Smiths and they were more 'rock.' But then they have heard me singing that non-stop since a friend bought me a 'The Smiths' greatest hits CD for the holidays. According to one son, I'm apparently, an old fart but I still bashed out Dammit by Blink 182 last night with a 200 note streak on 'Hard', take that whippersnapper!

OK, back to charting ... got some data looking like this:

<TEST>
<LIST_G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>No. of Outstanding Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>8</BALANCE>
<IS_ORDER>1</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>No. of Outstanding Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>1</BALANCE>
<IS_ORDER>1</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Loans Outstanding</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>7.1</BALANCE>
<IS_ORDER>2</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Undisbursed Effective Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>4</BALANCE>
<IS_ORDER>3</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Undisbursed Effective Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>4.6</BALANCE>
<IS_ORDER>3</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Total Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>12</BALANCE>
<IS_ORDER>4</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China, People'S Republic Of</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Total Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>12.7</BALANCE>
<IS_ORDER>4</IS_ORDER>
</G_HEADER>
</LIST_G_HEADER>
<CS_TOTAL>10.8</CS_TOTAL>
</TEST>

Requirement is to have pie charts showing 'Undisbursed Effective Loans by Country' and 'No. of Outstanding Loans by Country'. That data is a little tricky with the loan type being stored in that horribly named, DECODE_HEADER_B_UNDISBURSED_EF element but we can do it.

Its back to our old friend XPATH for some help to 'filter' the records that we chart. If you remember, xpath is a way of limiting records using a 'where' clause in SQL speak. You can get very sophisticated but in this case is a simple clause ie filter records where DECODE_HEADER_B_UNDISBURSED_EF = 'Undisbursed Effective Loans' for one chart and DECODE_HEADER_B_UNDISBURSED_EF = 'No. of Outstanding Loans' for the other.

The chart dialog in the template builder can not currently help on adding the 'where' clause so you'll have to dig into the code but we can get you most of the way with the dialog.

Setup the chart like so:

Now we need to add the xpath expression, double click the image thats been embedded in the RTF to get the image properties dialog. Go to the Web tab to see the code. Now look for the three instances of './/G_HEADER' and add the following - [DECODE_HEADER_B_UNDISBURSED_EF='No. of Outstanding Loans'] so you have.

.//G_HEADER[DECODE_HEADER_B_UNDISBURSED_EF='No. of Outstanding Loans']

This will limit the records to those that are of the 'No. of Outstanding Loans' type. Now run the template to see the required chart. Repeat for the other loan type et voila! you will have the required charts. You can use XPATH expressions to help you build pretty much anything you want. Sample files here.

If you're in Colorado and want to catch me and der boyz rockin' out, stop by our basement, we're here all week!

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:

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:

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:

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;

Too many questions on the love of my life, BIP charts. Oh the joy of finding out how to add a trendline or a fixed horizontal line, the list goes on. Its quite easy really but that's because I have an internal chart rendering tool that generates the XML I need to do cool stuff in charts. Some times I have to resort to scouring the chart DTD but the tool lets me test the feature I want. Sadly, the tool is not owned by BIP so we do not have the authority to make it public but I so wish it were. It would help you to help yourselves sooo much.

Two things to cover today ...

X Axis Labels

I've a Bar chart where X-axis labels are coming as horizontally-alligned and one value above, next value in a level below...so on. Is it possible change the alignment to -45 degree as it is available in excel chart? x-axis data label points will be displayed in uniform way then.

45 degrees is not possible at the moment. you can get them to show at 90 or 270 degrees thou

<O1TickLabel textRotation="TR_HORIZ_ROTATE_90" automaticRotation="AR_NO_ROTATE"/>

you can influence other properties too

<O1TickLabel textRotation="TR_HORIZ_ROTATE_90" automaticRotation="AR_NO_ROTATE">

There was a followup kindly answered by Vetsrini

Fixed Axis Scale

Is it possible to define a fixed scale for x-axis? I see by default Bipublisher decides scale based on the value coming from dataset. I want to set scale always from 0 to 500 with label showing at interval of 10.

<X1Axis axisMinAutoScaled="false" axisMinValue="0" axisMaxAutoScaled="false" axisMaxValue="500" majorTickStepAutomatic="false" majorTickStep="10"/>

and then,
Static Chart Line

How do I show a static line on my chart?

Vetsrini and I seemed to be in a race to answer charting questions yesterday :0)

Inside the Graph tag use:

<Y1ReferenceLine>
 <ReferenceLine index="0" visible="true" lineWidth="2" text="My Ref Line" value="550.0" displayedInLegend="true" lineColor="#3366ff"/>
</Y1ReferenceLine>

and finally

Trend Lines

Does BIP have the ability to create trend/regression lines?

Yep, it does, manual job like the others above but possible.
Under the Graph tag

<SeriesItems defaultFitlineType="FT_LINEAR"/>

Valid vaues for fitlinetype are FT_LINEAR|FT_LOGARITHMIC|FT_EXPONENTIAL

if you want multiple lines fitted to data points

<SeriesItems>
<Series id="0" fitlineType="FT_LOGARITHMIC"/>
<Series id="1" fitlineType="FT_EXPONENTIAL"/>
</SeriesItems>

I'll blog em as they come in and we manage to answer them. Happier charting!