Conditional Charting

Something I have never been asked for with BIP until recently, conditional charts. Not whether they appear or not but being able to highlight a specific bar on a bar chart if it meets some certain criteria. The chart to the right is simple enough showing sales by month. The April bar is being highlighted in red because the value is falling outside of some limit.

So how can you do it? Its not documented in the BIP docs, it's not in the BIBeans (charting engine) docs, so until I finish this post you will need to know and have friends in high places at Oracle. Thank you Klaus and especially Katia for pointing me in the right direction.

I have tested this approach with and 11g, I have not tested with 5.6.3 thou.
To get the bar to change color you are going to have to dig into the chart XML and there will be a bit of effort to identify the column to change color but its not that onerous to do or manage. Note thou, if you re-open the chart dialog with the 10g Template Builder it will over write you conditional code.

The new object you need to add into your graph XML is an ExceptionalRiser, you can add multiple risers but more on that later. It needs to appear somewhere inside the Graph tag.

<Graph seriesEffect="SE_NONE" graphType="BAR_HORIZ_CLUST">
series="0" group="9" borderColor="#0fffff" fillColor="#ff0000" />

The attributes can be described as: 
 - series - the data series that contains the data point to be re-colored. For the majority of charts this will be "0" ie only a single data series
- group - the number of the data point or bar to be re-colorded. Starting from 0
- borderColor - line around outside color
- fillColor  - errr ... the fill color.

If you know the value thats erroneous up front then you can hard code the group attribute as above but thats going to be rare. I guess you could work it all out in the data extract and create an element to hold the exception data row number but you can equally work out the value in the template layer.

I have struggled a bit with this and have had to resort to my nemesis updatable variables. My first use case is where I know I only want to color a single bar on my chart.
Here's my data:

    <Row> <Month>Jan</Month> <Value>450</Value> <Target>420</Target></Row>
    <Row> <Month>Feb</Month> <Value>500</Value> <Target>550</Target></Row>
    <Row> <Month>Mar</Month> <Value>490</Value> <Target>490</Target></Row>
    <Row> <Month>Apr</Month> <Value>400</Value> <Target>520</Target></Row>
    <Row> <Month>May</Month> <Value>680</Value> <Target>650</Target></Row>

Simple stuff as usual but I hope easy to understand. I can create a calculation looking for Values that miss the Target value and build an 'if' statement oround it to highlight values in red.

<?if:(number(Value) div number(Target))<number(0.8)?><?attribute@incontext:color;'red'?><?end if?>

This is looking for Values less than 80% of their Target, of which I know there is only one the %Delta for Apr.

Looking back at the ExceptionalRiser, XML I should be able to pass a value to the group attribute. Here's where I have wasted some time trying to use native variables, for some reason I can not set the value crrectly for the chart to pick the variable value up. So I have resorted to updateable variables instead. It works, it just not appeal to my sense of right and wrong in XSL i.e. no native support for updatable variables. Seeing as Im conditionally formatting the percentage values I could createa variable in that cell. But if the chart appears before the table, its not going to work.

So I have created a field at the beginning of the template to loop through the values seraching for my erroneous vlaue:

<?for-each:Row?><?if:number(number(Value) div number(Target))<0.8?><?xdoxslt:set_variable($_XDOCTX,'val3',position())?><?end if?><?end for-each?>

If it finds the value, it sets the variable val3 with the current record pointer, note, this starts at 1.
In my chart code I have:

<ExceptionalRiser series="0" group="{xdoxslt:get_variable($_XDOCTX,'val3')-1}"  borderColor="#000000" fillColor="#ff0000" />

Notice the curly braces to get the XSLT engine to evaluate my variable value first and remember the group value starts from zero hence the '-1'.
This gets me my chart correctly showing the Apr bar in red.

Next time, multiple conditional bars, heres the RTF template and data so far.


Post a Comment:
  • HTML Syntax: NOT allowed

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!


« April 2014