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



No comments:

Post a Comment