Paul Cannon's BI Blog: August 2012

Following my previous blog on using advanced tags in BI Publisher charts, here’s another example, this time with horizontal bar charts.

Start with a standard horizontal bar chart, this time showing % of responses from a customer survey. The biggest issue here is that the x-axis scaling goes over 100, so we want to restrict it to just 100.

Now in the Advanced tab we make the following changes to the XML:

Move the legend to the bottom by adding automaticPlacement="AP_NEVER" and position="LAP_BOTTOM" to the LegendArea tag.

Now add a title to the chart by adding the Title tag with a GraphFont subtag after the LegendArea tag:

</Title>

Specify our own colours on the bars by adding a section of <SeriesItems> tag and sub-tags, adding this after the Title tag.

</SeriesItems>

Set the X-Axis scaling. Note that BIP calls the X-Axis the Y-Axis in horizontal charts and also the documentation for these tags is incorrect in this case as it claims the tag is “y1axis” when actually it must be upper case Y and A – “Y1Axis”. We add this tag after the <SeriesItems> tag above.

Finally specify a title for the X-Axis – again note the Y-Axis tag name – Y1Title:

</Y1Title>

The resulting bar chart then looks like this:

The entire XML from the advanced tag is:

</Title>

</SeriesItems>

</Y1Title>

<LocalGridData colCount="{count(xdoxslt:group(.//G_33, 'SURVEYQUESTION'))}"

rowCount="{count(xdoxslt:group(.//G_33, 'YEAR'))}">

<xsl:for-each-group select=".//G_33" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

</xsl:for-each-group></RowLabels><ColLabels>

<xsl:for-each-group select=".//G_33" group-by="SURVEYQUESTION" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

</xsl:for-each-group></ColLabels><DataValues><xsl:for-each-group select="//G_33"

group-by="./YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:variable name="G1" select="current-group()" />

<xsl:value-of select="sum($G1[(./SURVEYQUESTION=current()/SURVEYQUESTION)]/COLUMN3_16[.!='']) div

count($G1[(./SURVEYQUESTION=current()/SURVEYQUESTION)]/COLUMN3_16[.!=''])" /></Cell>

</xsl:for-each-group></RowData></xsl:for-each-group>

</DataValues>

</LocalGridData>

</Graph>

When creating Charts in BI Publisher the options to improve their appearance is bit limited, there are a whole bunch of options in the builder tab of the chart dialog box, but this does cover everything you might want - in a recent development we wanted the line width to be thiner and to specify the y-asix scaling but there are no options for this.

However BI Publisher charts are generated by the same ADF Data Visualisation Tools that are used by OBIEE and other Fusion products and this does contain a whole host of options not available in the builder tab. Fortunatly the chart builder dialog box has a second tab called "Advanced" which contains the XML code to generate the chart. We can use this tab to alter the XML to add in extra options.

Below is an example of using the Advanced tab to alter a chart, but I only cover a handul of options. for the full list of whats available look in the OBIEE documentation - look in the "Oracle ADF Faces Data Visualization Tools Tag Reference" (under the "Reference and APIs" section)

So we start with creating a standard chart:

I've set the 3d option to false in the Properties list, but otherwise left the chart using the default settings. This produces the following chart:

Now if we edit the chart and look in the Advanced tab, we see the XML BI Publisher generated.

The good news is that we can edit the XML in this tab and directly alter the graph. Note that once you do alter the XML you can no longer use the Builder tab to alter those properties it does include - the entire builder tab is greyed out.

So lets start making changes.

Firstly I want markers displayed on the lines. the first tag is <Graph> and in here we can add an extra parameter: markerDisplayed="true"

so this tag becomes:

<Graph seriesEffect="SE_AUTO_GRADIENT" graphType="LINE_VERT_ABS" markerDisplayed="true">

Next I want to move the location of the legend. The second tag <LegendArea> currently only states that the legend is visible. So lets add two parameters here. Firstly we want to turn off the automatic placement of the legend, automaticPlacement="AP_NEVER" does this, then position the legend underneath the graph with position="LAP_BOTTOM".

so the LegendArea tag becomes:

<LegendArea visible="true" automaticPlacement="AP_NEVER" position="LAP_BOTTOM" />

Next I want to add a title to the chart. there is no title specified at present, so we'll add a new tag after the LegendArea tag. The tag is called "Title" and we'll use three parameters to make it visible, specify the text we want to display and position it in the centre of the screen. We are also going to use a sub-tag called GraphFont to specify the font details. GraphFont is a generic sub-tag which can be used with a number of tags.

<Title text="Total Sales Volume - Actual vs Target" visible="true" horizontalAlignment="CENTER">
<GraphFont bold="false" italic="false" underline="false" fontColor="#0000C0" />
</Title>

Next we'll deal with the Y-Axis scaling. The <Y1Axis> tag lets us specify both the lower and upper ranges of the scale and, importantly, turn off the auto-scaling. This can be added under the <Title> tag.

<Y1Axis axisMinAutoScaled="false" axisMinValue="20000" axisMaxAutoScaled="false" axisMaxValue="35000" />

Now for the width and colour of the lines. The colour of the lines can be set in the builder tab, but the width cannot. We handle this with a section of sub-tags. Firstly the <SeriesItems> lets us specify the line width and then allows us to specify details, in this case just colour, for each data item (aka Series). Note that the series are numbered starting at 0. These tags can be added after the <Y1Axis> tag.

<SeriesItems defaultLineWidth="1">
<Series id="0" color="#0000C0" />
<Series id="1" color="#FF00FF" />
</SeriesItems>

The final change I want to make to this graph is change the labels on the data items. Oddly this uses a different set of tags (you'd have thought it would be included in the SeriesItems tag above). This time the tags already exist, further down the XML you should be able to see the <RowLabels> tag and the <Label> tag for each data item. Simply change the text against each label, bearing in mind that the order of the labels matches the order of the data items.

<RowLabels>
<Label>Target</Label>
<Label>Actual Sales</Label>
</RowLabels>

Now when you save and view the graph you see the improvements:

So, in summary I've taken the default XML build by BI Publisher of:

<Graph seriesEffect="SE_AUTO_GRADIENT" graphType= "LINE_VERT_ABS">
<LegendArea visible="true"/>
<LocalGridData colCount="{count(xdoxslt:group(.//G_1, 'COLUMN0'))}" rowCount="2">
<RowLabels>
<Label>COLUMN1</Label>
<Label>COLUMN2</Label>
</RowLabels>
<ColLabels>
<xsl:for-each-group select=".//G_1" group-by="COLUMN0">
<Label><xsl:value-of select="current-group()/COLUMN0"/></Label>
</xsl:for-each-group>
</ColLabels>
<DataValues><RowData>
<xsl:for-each-group select=".//G_1" group-by="COLUMN0">
<Cell><xsl:value-of select="sum(current-group()/COLUMN1[.!=''])"/></Cell>
</xsl:for-each-group></RowData>
<RowData><xsl:for-each-group select=".//G_1" group-by="COLUMN0">
<Cell><xsl:value-of select="sum(current-group()/COLUMN2[.!=''])"/></Cell>
</xsl:for-each-group></RowData>
</DataValues>
</LocalGridData>
</Graph>

and enhanced it to look like:

<Graph seriesEffect="SE_AUTO_GRADIENT" graphType="LINE_VERT_ABS" markerDisplayed="true">
<LegendArea visible="true" automaticPlacement="AP_NEVER" position="LAP_BOTTOM" />
<Title text="Total Sales Volume - Actual vs Target" visible="true" horizontalAlignment="CENTER">
<GraphFont bold="false" italic="false" underline="false" fontColor="#0000C0" />
</Title>
<Y1Axis axisMinAutoScaled="false" axisMinValue="20000" axisMaxAutoScaled="false" axisMaxValue="35000" />
<SeriesItems defaultLineWidth="1">
<Series id="0" color="#0000C0" />
<Series id="1" color="#FF00FF" />
</SeriesItems>
<LocalGridData colCount="{count(xdoxslt:group(.//G_1, 'COLUMN0'))}" rowCount="2">
<RowLabels>
<Label>Target</Label>
<Label>Actual Sales</Label>
</RowLabels>
<ColLabels>
<xsl:for-each-group select=".//G_1" group-by="COLUMN0">
<Label><xsl:value-of select="current-group()/COLUMN0"/></Label>
</xsl:for-each-group>
</ColLabels>
<DataValues><RowData>
<xsl:for-each-group select=".//G_1" group-by="COLUMN0">
<Cell><xsl:value-of select="sum(current-group()/COLUMN1[.!=''])"/></Cell>
</xsl:for-each-group></RowData>
<RowData><xsl:for-each-group select=".//G_1" group-by="COLUMN0">
<Cell><xsl:value-of select="sum(current-group()/COLUMN2[.!=''])"/></Cell>
</xsl:for-each-group></RowData>
</DataValues>
</LocalGridData>
</Graph>

Paul Cannon's BI Blog

Thursday, 2 August 2012

BI Publisher Charts Advanced Tags – Horizontal Bar Example

BI Publisher Charts Advanced Tags

About Me