Thursday 27 February 2014

BI Publisher Line Charts - Dealing with Lines with no data



This is common problem with BI Publisher reports - when line charts have no data BI Publisher treats the missing values as zero's and drops the line down the bottom of the chart.




This can sometimes be misleading, but generally just looks ugly.

There is something that can be done about this by using the XSL scripting that BI Publisher uses to generate charts.  

There are two alternatives I'm going to look at here. The first is to get the chart to repeat the latest value across the missing periods (effectively drawing a straight line to the edge of the chart), the second is to remove the line completely when there is no data - in the above chart the 2013/14 line would end in Jan.

To start access the chart XSL script by looking at the Advanced tab when editing charts in the Word template builder:



I find it best to copy this code out into a text editor, then reformat it to make it easy to read. 

Locate the <RowData> tag for the required data field. You will have one <RowData> tag for each data item in the chart - in this example there is just one - if you have more than one then just repeat the instructions below.

So the <RowData> tag looks something like this:

<RowData>
<xsl:for-each-group select="//G_1" group-by="./DATE_CODE">
<Cell>
<xsl:value-of select="sum($G1[(./DATE_CODE=current()/DATE_CODE)]/TOTAL_INCOME[.!=''])"/>
</Cell>
</xsl:for-each-group>
</RowData>

make three changes to this code:

1. Add a new line after the <RowData> tag:
<xsl:value-of select="xdoxslt:set_variable($_XDOCTX,'PreviousValue', number(0))"/>

This creates a variable - PreviousValue - into which the previous line value will be kept as the line is drawn.
2. define a variable for the next value, set when the value is picked up - replace 'value-of select' with variable name="NextValue" in the first <xsl tag after the <Cell> tag:
<xsl:variable name="NextValue" select="sum($G1[(./DATE_CODE=current()/DATE_CODE)]/TOTAL_INCOME[.!=''])"/>

3. add a new tag group which will look at the next value and switch in the previous value if it's zero (BIP treats missing/null as zero). add this after the <xsl tag edited above:
<xsl:choose>
<xsl:when test="$NextValue > 0">
<xsl:value-of select="$NextValue"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="xdoxslt:get_variable($_XDOCTX,'PreviousValue')"/>
</xsl:otherwise>
</xsl:choose>
<xsl:choose>
<xsl:when test="$NextValue > 0">
<xsl:value-of select="xdoxslt:set_variable($_XDOCTX,'PreviousValue', $NextValue)"/>
</xsl:when>
</xsl:choose>

The whole <RowData> tag will now look like this:

<RowData>
<xsl:value-of select="xdoxslt:set_variable($_XDOCTX,'PreviousValue', number(0))"/>
<xsl:for-each-group select="//G_1" group-by="./DATE_CODE">
<Cell>
<xsl:variable name="NextValue" select="sum($G1[(./DATE_CODE=current()/DATE_CODE)]/TOTAL_INCOME[.!=''])"/>
<xsl:choose>
<xsl:when test="$NextValue > 0">
<xsl:value-of select="$NextValue"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="xdoxslt:get_variable($_XDOCTX,'PreviousValue')"/>
</xsl:otherwise>
</xsl:choose>
<xsl:choose>
<xsl:when test="$NextValue > 0">
<xsl:value-of select="xdoxslt:set_variable($_XDOCTX,'PreviousValue', $NextValue)"/>
</xsl:when>
</xsl:choose>
</Cell>
</xsl:for-each-group>
</RowData>


Paste the whole code back into the advanced tab in the edit chart dialog box above and click ok. If you made a mistake you will get a message saying so!

The one downside of this is that the preview graph now can't be displayed, so you just see this whilst editing your document instead of the actual graph:



but if you preview the report (in PDF or your preferred output) you'll see the chart:


Note how the 2013/14 line after Jan now stays at the same value to the end of the year.
If you'd prefer the line not to display for the missing months, back in the XSL code remove the <xsl:otherwise> tag:

<xsl:otherwise>
<xsl:value-of select="xdoxslt:get_variable($_XDOCTX,'PreviousValue')"/>
</xsl:otherwise>

so the whole <RowData> tag is now

<RowData>
<xsl:value-of select="xdoxslt:set_variable($_XDOCTX,'PreviousValue', number(0))"/>
<xsl:for-each-group select="//G_1" group-by="./DATE_CODE">
<Cell>
<xsl:variable name="NextValue" select="sum($G1[(./DATE_CODE=current()/DATE_CODE)]/TOTAL_INCOME[.!=''])"/>
<xsl:choose>
<xsl:when test="$NextValue > 0">
<xsl:value-of select="$NextValue"/>
</xsl:when>
</xsl:choose>
<xsl:choose>
<xsl:when test="$NextValue > 0">
<xsl:value-of select="xdoxslt:set_variable($_XDOCTX,'PreviousValue', $NextValue)"/>
</xsl:when>
</xsl:choose>
</Cell>
</xsl:for-each-group>
</RowData>

Save the chart and preview and in my example the 2013/14 line now stops at Jan



BI Publisher 11g - Setting a Background colour on charts



I've recently been asked how to set the background of a chart in BI Publisher to a gradient colour when using the Word Template builder. There is very little documentation on how to do this, and what there is doesn't seem to work properly, so here's how.

Using the Word template builder create the basic chart you want, here's mine:



The background colour isn't in the list of properties when editing the chart, so we need to use the advanced tab of the chart edit screen:



We need to insert a new tag here for "PlotArea" after the Graph tag:
<PlotArea borderColor="#000000" fillColor="#D0DCEC" borderTransparent="true" fillTransparent="false" />




The chart now looks appears as:



There are four parameters on the PlotArea tag:

borderColor: The colour you want to use for the border, in hex, e.g. "#000000" 

fillColor: The colour you want to use for the background, in hex, e.g. "#D0DCEC" 

borderTransparent :  Determines if the border is transparent - the borderColor setting only applies if this is set to "false".

fillTransparent: Determines if the background is transparent - the fillColor setting only applies if this is set to "false"

Now we have a background colour set, we can go a stage further and set a gradient wash:

 

To do this we need to use a sub-tag of PlotArea: SFX. The SFX tag has three parameters:

fillType: this can be set to FT_GRADIENT, FT_COLOR or FT_TEXTURE.

gradientDirection: this can be any of the follow directional options: GD_RIGHT, GD_LEFT, GD_DOWN, GD_UP, GD_DOWN_LEFT, GD_UP_LEFT, GD_DIAGONAL_45, GD_DIAGONAL_135, GD_DOWN_RIGHT, GD_UP_RIGHT, GD_RADIAL, GD_RADIAL_TOP_LEFT, GD_RADIAL_TOP_RIGHT, GD_RADIAL_BOTTOM_LEFT or  GD_RADIAL_BOTTOM_RIGHT

gradientNumStops: This tells the chart how many gradient colour changes to perform. A setting of 2 performs one gradient change from one colour to a second (hence 2). 3 would allow a gradient change from one colour to a second and then on to a third. (there's an example of this below).

so our SFX tag will be:
<SFX fillType="FT_GRADIENT" gradientDirection="GD_RIGHT" gradientNumStops="2">

The SFX tag has a sub-tag of GradientStopStyle, and you need one of these for each stop. As we've specificed 2 stops, we need 2 GradientStopStyle tags:

 <GradientStopStyle stopIndex="0" gradientStopPosition="0.0" gradientStopColor="#D0DCEC"/>
 <GradientStopStyle stopIndex="1" gradientStopPosition="100.0" gradientStopColor="#FFFFFF"/>

These tell the chart the order of the gradient colour changes, the percentage of the chart then should cover and the colour they should end up as.

The first tag sets the initial colour, in this case #D0DCEC. 

The second tag sets the colour to change to and how far across the chart it should go, in this case cover the whole chart (100%) and change the colour to white (#FFFFFF). So the whole PlotArea tag group would now be:

<PlotArea borderColor="#000000" fillColor="#D0DCEC" borderTransparent="true" fillTransparent="false">
<SFX fillType="FT_GRADIENT" gradientDirection="GD_RIGHT" gradientNumStops="2">
<GradientStopStyle stopIndex="0" gradientStopPosition="0.0" gradientStopColor="#D0DCEC"/>
<GradientStopStyle stopIndex="1" gradientStopPosition="100.0" gradientStopColor="#FFFFFF"/>
</SFX>
</PlotArea>

Paste this into the Advanced tab of the edit chart screen to see the chart above.

To enhance this we can add additional stops. The following tag has 3 stops and a down gradient:

<PlotArea   borderColor="#000000" borderTransparent="true" fillTransparent="false">
<SFX fillType="FT_GRADIENT" gradientDirection="GD_RIGHT" gradientNumStops="3">
<GradientStopStyle stopIndex="0" gradientStopPosition="0.0" gradientStopColor="#D0DCEC"/>
<GradientStopStyle stopIndex="1" gradientStopPosition="50.0" gradientStopColor="#FFFFFF"/>
<GradientStopStyle stopIndex="2" gradientStopPosition="100.0" gradientStopColor="#D0DCEC"/>
</SFX>
</PlotArea>

and produces this:



This sets the background of the chart area, but we can also set the background of the whole chart. The background tag sets this:
 
<Background fillColor="#D0DCEC"  fillTransparent="false"/>

Just like the PlotArea tag, filltransparent must be set to "false" for the fillColor to be active:



We can also use the same SFX sub-tag to set a gradient:

<Background fillColor="#D0DCEC"  fillTransparent="false">
<SFX fillType="FT_GRADIENT" gradientDirection="GD_RIGHT" gradientNumStops="2">
<GradientStopStyle stopIndex="0" gradientStopPosition="0.0" gradientStopColor="#D0DCEC"/>
<GradientStopStyle stopIndex="1" gradientStopPosition="100.0" gradientStopColor="#FFFFFF"/>
</SFX>
</Background>

produces:


 

and finally we can go one step further by having different settings for the PlotArea and the Background:

<Background fillColor="#D0DCEC"  fillTransparent="false">
<SFX fillType="FT_GRADIENT" gradientDirection="GD_RIGHT" gradientNumStops="2">
<GradientStopStyle stopIndex="0" gradientStopPosition="0.0" gradientStopColor="#D0DCEC"/>
<GradientStopStyle stopIndex="1" gradientStopPosition="100.0" gradientStopColor="#FFFFFF"/>
</SFX>
</Background>
<PlotArea borderTransparent="true" fillTransparent="false">
<SFX fillType="FT_GRADIENT" gradientDirection="GD_LEFT" gradientNumStops="2">
<GradientStopStyle stopIndex="0" gradientStopPosition="0.0" gradientStopColor="#D0DCEC"/>
<GradientStopStyle stopIndex="1" gradientStopPosition="100.0" gradientStopColor="#FFFFFF"/>
</SFX>
</PlotArea>

Produces: