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
Hi Paul,
ReplyDeleteAm using the BI charts. I would like you advice, is there a way to set the Legend Labels with dynamic values returned from the query (ROWSET).
Regards
Thanks for sharing, nice post! Post really provice useful information!
ReplyDeleteGiaonhan247 chuyên dịch vụ mua hàng mỹ từ dịch vụ order hàng mỹ hay nhận mua nước hoa pháp từ website nổi tiếng hàng đầu nước Mỹ mua hàng ebay ship về VN uy tín, giá rẻ.