Oracle BI Publisher Blog

Hot chart of the week this week is line charts - not just your single line oh no. Folks want multiple lines! Its not that tough and with the new Template Builder for Word (10.1.3.4) out its even easier - you should not have to dabble in the chart XML. For those of you on earlier versions of Publisher, you can use the latest builder. Just dont build any crosstabs with the crosstab interface. they will not work in your reports unless you have 10.1.3.4 on the server side. As an aside we now have multiple measure support for the crosstabs! If I assume I have the following data:

<SALES>
 <SALE>
  <YEAR>2006</YEAR>
  <SOFTWARE>1200</SOFTWARE>
  <HARDWARE>850</HARDWARE>
  <SERVICES>2000</SERVICES>
 </SALE>
 <SALE>
  <YEAR>2007</YEAR>
  <SOFTWARE>1000</SOFTWARE>
  <HARDWARE>800</HARDWARE>
  <SERVICES>1100</SERVICES>
 </SALE> 
 <SALE>
  <YEAR>2008</YEAR>
  <SOFTWARE>900</SOFTWARE>
  <HARDWARE>1200</HARDWARE>
  <SERVICES>1500</SERVICES>
 </SALE> 
 </SALES>

With the new Chart builder you can add the multiple measures to the line chart ...

... to get the desired result ...

For the hard core souls among you, here's the XML behind the multi line chart

chart:
<Graph graphType="LINE_VERT_ABS"><LegendArea visible="true" />
 <LocalGridData colCount="{count(xdoxslt:group(.//SALE, 'YEAR'))}" rowCount="3">
 <RowLabels>
  <Label>SOFTWARE</Label>
  <Label>HARDWARE</Label>
  <Label>SERVICES</Label>
 </RowLabels>
 <ColLabels>
  <xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <Label>
  <xsl:value-of select="current-group()/YEAR" />
</Label>
</xsl:for-each-group>
</ColLabels>
<DataValues>
<RowData>
<xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Cell>
<xsl:value-of select="sum(current-group()/SOFTWARE)" />
</Cell>
</xsl:for-each-group>
</RowData>
<RowData>
<xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Cell>
<xsl:value-of select="sum(current-group()/HARDWARE)" />
</Cell>
</xsl:for-each-group>
</RowData>
<RowData>
<xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Cell>
<xsl:value-of select="sum(current-group()/SERVICES)" />
</Cell>
</xsl:for-each-group>
</RowData>
</DataValues>
</LocalGridData>
</Graph>

Notice the multiple RowData entries, one for each line.
If you wanted to get even more fancy you can add marker shapes to the data points on your lines. We are not quite there with the chart builder interface yet but this is pretty simple, just add the following to your chart XML.

<Graph  markerDisplayed="true" >
<SeriesItems>
 <Series id="0" markerShape="MS_SQUARE"/>
 <Series id="1" markerShape="MS_CIRCLE"/>
</SeriesItems>
...
</Graph>

marker options are :

• MS_SQUARE
• MS_CIRCLE
• MS_DIAMOND
• MS_PLUS
• MS_TRIANGLE_DOWN
• MS_TRIANGLE_UP

Some of you will find the following attribute for the Graph element 'markerShapeInLegend="true"'. This should do what it 'says on the tin!'
Sadly in earlier versions of te charting library there is a bug preventing this - seems to affect Applications flavors the most. There is a bug for this and we are looking at getting a new chart library patch out.