X

An Oracle blog about BI Publisher

  • RTF
    August 25, 2008

Line Charts

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 ...

LineChart1.gif


... to get the desired result ...

LineChart2.gif


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.

Join the discussion

Comments ( 2 )
  • guest Wednesday, December 21, 2011

    Your sample is of great help and easy to be understood. :)

    Please I just want to ask if is it possible to pass values from parameter or values from variable in the RTF to the graph an put it inside the <Cell></Cell> tag?

    sort of <Cell> (get variable or parameter) </Cell>

    Your help will greatly be appreciated?


  • Tim Wednesday, December 21, 2011

    As long as the parameter/variable is declared globally (use the @start command) Your chart code can then access it.

    I believe there is now a sample report that does this in 11g

    Tim


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.