An Oracle blog about BI Publisher

  • RTF
    April 8, 2008

Chart Smoothing

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


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:

<Graph graphType="LINE_VERT_ABS" >
colCount="{count(.//G_BID_CURVE_DATE[position() mod number($chartLimit)=0])}"
<xsl:for-each select=".//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]">
<xsl:value-of select="BID_CURVE_DATE" />
<xsl:for-each select=".//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]">
<xsl:value-of select="LIST_G_BC_30YEAR/G_BC_30YEAR/BC_6MONTH" />


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. 


Join the discussion

Comments ( 1 )
  • liam Monday, March 9, 2009
    I am trying to plot a Time series graph
    If my graph exceeds a certain amount of x-axis values it is difficult to read them as they overlap.
    Using your suggestions I can limit the number of labels that appear by limiting the number of values plotted.
    Is it possible to limit the number of labels that appear without limiting the number of values plotted.
    ie plot all values from 1 to 100 and label every 10th value which would mean only 10 labels on the graph.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.