Wednesday Feb 29, 2012

Conditional Charting II

A follow up post on yesterdays efforts. After pinging a few colleagues Klaus came up with a much much neater approach that appeals to my sense of easy, straightforward and neat, when it comes to code anyway. A by product of the approach, it can handle, none, one or multiple conditonal bars.

So, no variables ... a big plus, some chart xml editing, not quite so good but the benefits far outweigh the costs. Its a case of digging into the chart code and maybe Klaus (who is in charge of the template builders) will one day, provide this conditionality via the chart dialogs ... pleeeease Klaus :0)

Heres the relevant snippet of the chart code:

<Graph seriesEffect="SE_AUTO_GRADIENT">
 <LegendArea visible="false" />
 <ExceptionalRisers>
  <xsl:for-each select=".//Row"  xmlns:xsl="
http://www.w3.org/1999/XSL/Transform">
  <xsl:if test="number(number(.//Value) div number(.//Target))&lt; number($pLim)">
   <ExceptionalRiser series="0" group="{position()-1}" fill border/> 
  </xsl:if>
  </xsl:for-each>
 </ExceptionalRisers>
 <LocalGridData>
...
</Graph> 

We essentially, build the multiple ExceptionalRiser entries in the graph code itself rather than try and build out a concatentated string variable. The string approach was my first thought but 1. XSL does not allow XML strings to be built dynamically and 2. the chart does not break if there are no conditional columns to be rendered.
Dealing with the code a line at a time
  <xsl:for-each select=".//Row"  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
Loop over the data as we did before
  <xsl:if test="number(number(.//Value) div number(.//Target))&lt; number($pLim)">
Look for percentages less than a specified value. I externalized this value into a variable (pLim) to make things easier to test, you can play with it in the template.
   <ExceptionalRiser series="0" group="{position()-1}" fill border/> 
If we find a row of data that meets the criteria we create an ExceptionalRiser entry and use the current record number (position()) minus 1 because out bars are numbered from zero.
Then we just close out the if, the for-each and we're done.
If the if statement does not return true at all i.e. no bars need to be colored; the chart just renders as normal, nice!

Klaus took his example a little further than mine. The one thing that my approach suffers from is that it requires the engine to loop over the data twice for the chart. Once to search for ExceptionalRisers and again to render the chart itself. Klaus took an aproach to create a variable to hold the chart data first and then  run the riser code and chart rendering against that. Check that out in the second template (ConditionalChart2-1.rtf) please note, that its an 11g template and will need the 11g plugin to see it running. Both available and with sample datasets here.

You can now conditionally color your bar charts, horizontal or vertical. I have tried the same with pie charts but no banana so I'm assuming its only going to work for bar charts. I have tested on 10.1.3.4.x and 11g releases. 

Thursday May 13, 2010

Embedding BIP just got easier!

[Read More]

Friday Apr 16, 2010

Excel Template Teaser

[Read More]

Friday Apr 02, 2010

UPDATE FOR BI PUBLISHER ENTERPRISE 10.1.3.4.1 MARCH 2010

[Read More]
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