Friday Feb 28, 2014

Waterfall Charts

Great question came through the ether from Holger on waterfall charts last night.

"I know that Answers supports waterfall charts and BI Publisher does not.
Do you have a different solution approach for waterfall charts with BI Publisher (perhaps stacked bars with white areas)?
Maybe you have already implemented something similar in the past and you can send me an example."

I didnt have one to hand, but I do now. Little known fact, the Publisher chart engine is based on the Oracle Reports chart engine. Therefore, this document came straight to mind. Its awesome for chart tips and tricks. Will you have to get your hands dirty in the chart code? Yep. Will you get the chart you want with a little effort? Yep. Now, I know, I know, in this day and age, you should get waterfalls with no effort but then you'd be bored right?

First things first, for the uninitiated, what is a waterfall chart? From some kind person at Wikipedia, "The waterfall chart is normally used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns. The columns are color-coded for distinguishing between positive and negative values."

We'll get back to that last sentence later, for now lets get the basic chart working.

Checking out the Oracle Report charting doc, search for 'floating' their term for 'waterfall' and it will get you to the section on building a 'floating column chart' or in more modern parlance, a waterfall chart. If you have already got your feet wet in the dark arts world of Publisher chart XML, get on with it and get your waterfall working.

If not, read on.

When I first starting looking at this chart, I decided to ignore the 'negative values' in the definition above. Being a glass half full kind of guy I dont see negatives right :)

Without them its a pretty simple job of rendering a stacked bar chart with 4 series for the colors. One for the starting value, one for the ending value, one for the diffs (steps) and one for the base values. The base values color could be set to white but that obscures any tick lines in the chart. Better to use the transparency option from the Oracle Reports doc.

<Series id="0" borderTransparent="true" transparent="true"/> 

Pretty simple, even the data structure is reasonably easy to get working. But, the negative values was nagging at me and Holger, who I pointed at the Oracle Reports doc had come back and could not get negative values to show correctly. So I took another look. What a pain in the butt!

In the chart above (thats my first BIP waterfall maybe the first ever BIP waterfall.) I have lime green, start and finish bars; red for negative and green for positive values. Look a little closer at the hidden bar values where we transition from red to green, ah man, royal pain in the butt! Not because of anything tough in the chart definition, thats pretty straightforward. I just need the following columns START, BASE, DOWN, UP and FINISH. 

START 200
BASE 0
UP 0
DOWN 0
FINISH 0
START 0
BASE 180
UP 0
DOWN 20
FINISH 0
START 0
BASE 150
UP 0
DOWN 30
FINISH 0

 Bar 1 - Start Value
 Bar 2 - PROD1
 Bar 3 - PROD2

and so on. The start, up, down and finish values are reasonably easy to get. The real trick is calculating that hidden BASE value correctly for that transition from -ve >> + ve and vice versa. Hitting Google, I found the key to that calculation in a great page on building a waterfall chart in Excel from the folks at Contextures.  Excel is great at referencing previous cell values to create complex calculations and I guess I could have fudged this article and used an Excel sheet as my data source. I could even have used an Excel template against my database table to create the data for the chart and fed the resulting Excel output back into the report as the data source for the chart. But, I digress, that would be tres cool thou, gotta look at that.
On that page is the formula to get the hidden base bar values and I adapted that into some sql to get the same result.

Lets assume I have the following data in a table:

PRODUCT_NAME SALES
PROD1 -20
PROD2 -30
PROD3 50
PROD4 60

The sales values are versus the same period last year i.e. a delta value.  I have a starting value of 200 total sales, lets assume this is pulled from another table.
I have spent the majority of my time on generating the data, the actual chart definition is pretty straight forward. Getting that BASE value has been most tricksy!

I need to generate the following for each column:

PRODUCT_NAME

STRT

BASE_VAL

DOWN

UP

END_TOTAL

START
200
0
0
0
0
PROD1
0
180
20
0
0
PROD2
0
150 30 0
0
PROD3
0 150 0 50 0
PROD4
0 200
0 60 0
END
0 0 0 0 260

Ignoring the START and END values for a second. Here's the query for the PRODx columns:

 SELECT 2 SORT_KEY 
, PRODUCT_NAME
, STRT
, SALES
, UP
, DOWN
, 0 END_TOTAL
, 200 + (SUM(LAG_UP - DOWN) OVER (ORDER BY PRODUCT_NAME)) AS BASE_VAL
FROM
(SELECT P.PRODUCT_NAME
,  0 AS STRT
, P.SALES
, CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END AS UP  
, CASE WHEN P.SALES < 0 THEN ABS(P.SALES) ELSE 0 END AS DOWN
, LAG(CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END,1,0) 
      OVER (ORDER BY P.PRODUCT_NAME) AS LAG_UP
FROM PRODUCTS P
)

The inner query is breaking the UP and DOWN values into their own columns based on the SALES value. The LAG function is the cool bit to fetch the UP value in the previous row. That column is the key to getting the BASE values correctly.

The outer query just has a calculation for the BASE_VAL.

200 + (SUM(LAG_UP - DOWN) OVER (ORDER BY PRODUCT_NAME))

The SUM..OVER allows me to iterate over the rows to get the calculation I need ie starting value (200) + the running sum of LAG_UP - DOWN. Remember the LAG_UP value is fetching the value from the previous row.
Is there a neater way to do this? Im most sure there is, I could probably eliminate the inner query with a little effort but for the purposes of this post, its quite handy to be able to break things down.

For the start and end values I used more queries and then just UNIONed the three together. Once note on that union; the sorting. For the chart to work, I need START, PRODx, FINISH, in that order. The easiest way to get that was to add a SORT_KEY value to each query and then sort by it. So my total query for the chart was:

SELECT 1 SORT_KEY
, 'START' PRODUCT_NAME
, 200 STRT
, 0 SALES
, 0 UP
, 0 DOWN
, 0 END_TOTAL
, 0 BASE_VAL
FROM PRODUCTS
UNION
SELECT 2 SORT_KEY 
, PRODUCT_NAME
, STRT
, SALES
, UP
, DOWN
, 0 END_TOTAL
, 200 + (SUM(LAG_UP - DOWN) 
      OVER (ORDER BY PRODUCT_NAME)) AS BASE_VAL
FROM
(SELECT P.PRODUCT_NAME
,  0 AS STRT
, P.SALES
, CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END AS UP  
, CASE WHEN P.SALES < 0 THEN ABS(P.SALES) ELSE 0 END AS DOWN
, LAG(CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END,1,0) 
       OVER (ORDER BY P.PRODUCT_NAME) AS LAG_UP
FROM PRODUCTS P
)
UNION
SELECT 3 SORT_KEY 
, 'END' PRODUCT_NAME
, 0 STRT
, 0 SALES
, 0 UP
, 0 DOWN
, SUM(SALES) + 200 END_TOTAL
, 0 BASE_VAL
FROM PRODUCTS
GROUP BY 1,2,3,4,6
ORDER BY 1 

A lot of effort for a dinky chart but now its done once, doing it again will be easier. Of course no one will want just a single chart in their report, there will be other data, tables, charts, etc. I think if I was doing this in anger I would just break out this query as a separate item in the data model ie a query just for the chart. It will make life much simpler.
Another option that I considered was to build a sub template in XSL to generate the XML tree to support the chart and assign that to a variable. Im sure it can be done with a little effort, I'll save it for another time.

On the last leg, we have the data; now to build the chart. This is actually the easy bit. Sadly I have found an issue in the online template builder that precludes using the chart builder in those templates. However, RTF templates to the rescue!

Insert a chart and in the dialog set up the data like this (click the image to see it full scale.)

Its just a vertical stacked bar with the BASE_VAL color set to white.You can still see the 'hidden' bars and they are over writing the tick lines but if you are happy with it, leave it as is. You can double click the chart and the dialog box can read it no problem. If however, you want those 'hidden' bars truly hidden then click on the Advanced tab of the chart dialog and replace:

<Series id="1" color="#FFFFFF" />

with

<Series id="1" borderTransparent="true" transparent="true" />

and the bars will become completely transparent. You can do the #D and gradient thang if you want and play with colors and themes. You'll then be done with your waterfall masterpiece!

Alot of work? Not really, more than out of the box for sure but hopefully, I have given you enough to decipher the data needs and how to do it at least with an Oracle db. If you need all my files, including table definition, sample XML, BIP DM, Report and templates, you can get them here.

Tuesday Feb 11, 2014

Filtered Charts

A customer question this week regarding filtering a chart. They have a report with a bunch of criteria with monetary values but, rather than show all of the criteria in a pie chart, they just want to show a few. For example:

 This ...
 rather than this

 There are a couple of ways to tackle this:

1. Filter the chart data in the chart definition. Using an XPATH expression you can filter out all of the criteria you do not want to see. Open the chart definition and update the definition. You will need to update the RowCount, RowLabels and DataValues attributes in the chart definition. Adding in the following XPATH expression:

    [DEPARTMENT_NAME='Accounting' or DEPARTMENT_NAME='Marketing' or DEPARTMENT_NAME='Executive']

so the DataValues value becomes:

    <DataValues><xsl:for-each-group select=".//G_1[DEPARTMENT_NAME='Accounting' or 
                                                     DEPARTMENT_NAME='Marketing' or 
                                                        DEPARTMENT_NAME='Executive']" ...

2. Create a variable in the template to hold just the values you want to chart.

    <?variable: filterDepts; /DATA_DS/LIST_G_1/G_1[DEPARTMENT_NAME='Accounting' or 
                                                     DEPARTMENT_NAME='Marketing' or 
                                                       DEPARTMENT_NAME='Executive']?>

Then update the chart definition with the variable for the same three attributes above, the RowCount, RowLabels and DataValues. For example:

    <DataValues><xsl:for-each-group select="$filterDepts" ...

These both work admirably, but they both require some manual updating of the chart definition which can get fiddly and a pain to maintain. I'm also just filtering for three departments, when you get up to 5 or 6 then the XPATH starts to become a pain to maintain. Option 2 alleviates this somewhat because you only need to define the filter once to create the filtered variable.
A better option may be ...

3. Force the effort down into the data layer. Create another query in the report that just pulls the data for the chart.

LIST_G2/G_2 holds the data for the chart. Then all you need do is create a vanilla chart on that particular section of the data.

Yes, there is some overhead to re-fetch the data but this is going to be about the same if not less than the extra processing required in the template with options 1 and 2. This has another advantage, you can parametrize the criteria for the user. You can create a parameter to allow the user to select, in my case, the department(s) they want to chart.


Its simple enough to create the multi-select parameter and modify the query to filter based on the values chosen by the user.

Sample report (including data model and layout template here) just un-archive into your catalog.
RTF Template plus sample data available here.




Tuesday Jul 02, 2013

Working the Chart Percentages

Charting in BIP is such fun, well sometimes it is. Not so much today, at least not for Ron in San Diego. He needed a horizontal bar chart showing values plotted for various test areas with value labels at the end of the bars. Simple enough right? The wrinkle, they were percentage values so he needed to see '56%' not '56'!

Still, it should be simple enough but the percentage formatting has a requirement for your values to be in a decimal format i.e. 0.56 not 56.0. 56.0 gets formatted as 5600%. OK, so either pull the values out as decimals or use the div function to divide the values in the chart by 100 e.g.

<xsl:value-of select="myval div 100)" />

Now I can use the following the chart XML to format the percentages as I need them:

 

<Graph ... >
...
<MarkerText visible="true">
<Y1ViewFormat>
<ViewFormat numberType="NUMTYPE_PERCENT" decimalDigit="0" numberTypeUsed="true" 
        leadingZeroUsed="true" decimalDigitUsed="true"/>
</Y1ViewFormat>
</MarkerText>
...
</Graph>

 

That gets me the values shown the way I want but the auto axis formatting gets me from 0 >> 1.

I now need to go in and add the formatting for the axis too.

 

<Graph ...>
...
<Y1Axis axisMinAutoScaled="false" axisMinValue="0.0" axisMaxAutoScaled="false" 
    axisMaxValue="1.0" majorTickStepAutomatic="true">
<ViewFormat numberType="NUMTYPE_PERCENT" decimalDigit="0" scaleFactor="SCALEFACTOR_NONE" 
    numberTypeUsed="true" leadingZeroUsed="true" decimalDigitUsed="true" scaleFactorUsed="true"/>
</Y1Axis>

 

Now I have a chart that's showing the percentage values and formatting axis scale correctly for me too.



You can of course mess with the attributes above to get more decimal points on your labels, etc.

Happy Charting!


Wednesday Nov 14, 2012

Chart Filtering

Interesting question from a colleague this week. Can you add a filter to a chart to just show a specific set of data?

In an RTF template, you need to do a little finagling in the chart definition. In an online template, a couple of clicks and you're done.

RTF

Build your chart as you would normally to include all the data to start with.

Now flip to the Advanced tab to see the code behind the chart. Its not very pretty but with a little effort you can get it looking a little more friendly. Here's my chart showing employees and their salaries.

<Graph depthAngle="50" depthRadius="8" seriesEffect="SE_AUTO_GRADIENT">
 <LegendArea visible="true"/>
 <Title text="Executive Department Only" visible="true" horizontalAlignment="CENTER"/>
 <LocalGridData colCount="{count(.//G_2)}" rowCount="1">
  <RowLabels>
   <Label>SALARY</Label>
  </RowLabels>
  <ColLabels>
   <xsl:for-each select=".//G_2">
    <Label><xsl:value-of select="EMP_NAME"/></Label>
   </xsl:for-each>
  </ColLabels>
  <DataValues>
   <RowData>
    <xsl:for-each select=".//G_2">
     <Cell><xsl:value-of select="SALARY"/></Cell>
    </xsl:for-each>
   </RowData>
  </DataValues>
 </LocalGridData>
</Graph>

Note the emboldened text. Its currently grabbing all values in the G_2 level of the data. We can use an XPATH expression to filter the data to the set we want to see. In my case I want to only see the employees that are in the Executive department. My  data is structured thus:

 

<DATA_DS>
    <G_1>
        <DEPARTMENT_NAME>Accounting</DEPARTMENT_NAME>
        <G_2>
            <MANAGER>Higgins</MANAGER>
            <EMPLOYEE_ID>206</EMPLOYEE_ID>
            <HIRE_DATE>2002-06-07T00:00:00.000-04:00</HIRE_DATE>
            <SALARY>8300</SALARY>
            <JOB_TITLE>Public Accountant</JOB_TITLE>
            <PARAS>11000</PARAS>
            <EMP_NAME>William Gietz</EMP_NAME>
        </G_2>

So the XPATH expression Im going to use to limit the data to the Executive department would be .//G_2[../DEPARTMENT_NAME='Executive'] Note the ../ moves the parser up the XML tree to be able to test the DEPARTMENT_NAME value. I added this XPATH expression to the three instances that need it ColCount, ColLabels and RowData. Its simple enough to do. Testing your XPATH expression is easier to do using a table of data. Please note, as soon as you make changes to the chart code. Going back to the Builder tab, you'll find that everything is grayed out. I recommend you make all the changes you can via the chart dialog before updating the code.

Online Template

Implementing the filter is much simpler, there is a dialog box to help you out. Add you chart and fill out the various data points you want to show. then hit the Filter item in the ribbon above the chart. That will pop the filter dialog box where you can then add a filter to the chart.


  You can add multiple filters if needed and of course you can use the Manage Filters button to re-open and edit the filters.

Pretty straightforward stuff!

Friday May 18, 2012

Secrets Revealed to Advanced Charting

We get a lot of emails and questions here at Publisher Tower concerning charts and how to do X. I write about some of the solutions here if I think they could be useful to a wider audience but its tough to document everything for everyone's specific features.

The chart dialog in the template builder gets you so far but there are cases where you are going to have to get into the code to make things work the way you want them to. I have documented a bunch which I have pulled together as links below. But if you do venture into the chart code, where do you start?

I have re-documented the location of the chart DTD document recently as it disappeared from OTN and having bugged a few people about it, its still not there, c'est la vie. But those of you with the Template Builder for Word (TB) have your own copy you can refer to. Just dig into your TB install directory and look for the dvt-jclient.jar (11g) or bipres.jar (10g) files open them with a zip utility a dig down through the directories to oracle\dss\graph\.
There you will find the fabled and rare, graph.dtd ... this is the golden fleece of the BIP charting world. In it, you will find secrets beyond your imagination, treasures beyond compare ...  OK, its not that exciting but there is a lot of charting info to be gleaned. There is not much in the way of comments but you can at least look up features and then see what attributes they will need to achieve your needs.

Just remember, Word has almost unlimited undo's, just get stuck in a try stuff out you are not going to break anything!

Some blogged chart solutions via google.

Tuesday Feb 28, 2012

Conditional Charting

Something I have never been asked for with BIP until recently, conditional charts. Not whether they appear or not but being able to highlight a specific bar on a bar chart if it meets some certain criteria. The chart to the right is simple enough showing sales by month. The April bar is being highlighted in red because the value is falling outside of some limit.

So how can you do it? Its not documented in the BIP docs, it's not in the BIBeans (charting engine) docs, so until I finish this post you will need to know and have friends in high places at Oracle. Thank you Klaus and especially Katia for pointing me in the right direction.

I have tested this approach with 10.1.3.4.x and 11g, I have not tested with 5.6.3 thou.
To get the bar to change color you are going to have to dig into the chart XML and there will be a bit of effort to identify the column to change color but its not that onerous to do or manage. Note thou, if you re-open the chart dialog with the 10g Template Builder it will over write you conditional code.

The new object you need to add into your graph XML is an ExceptionalRiser, you can add multiple risers but more on that later. It needs to appear somewhere inside the Graph tag.

chart:
<Graph seriesEffect="SE_NONE" graphType="BAR_HORIZ_CLUST">
<ExceptionalRisers>
<ExceptionalRiser
series="0" group="9" borderColor="#0fffff" fillColor="#ff0000" />
</ExceptionalRisers>
...
</Graph>

The attributes can be described as: 
 - series - the data series that contains the data point to be re-colored. For the majority of charts this will be "0" ie only a single data series
- group - the number of the data point or bar to be re-colorded. Starting from 0
- borderColor - line around outside color
- fillColor  - errr ... the fill color.

If you know the value thats erroneous up front then you can hard code the group attribute as above but thats going to be rare. I guess you could work it all out in the data extract and create an element to hold the exception data row number but you can equally work out the value in the template layer.

I have struggled a bit with this and have had to resort to my nemesis updatable variables. My first use case is where I know I only want to color a single bar on my chart.
Here's my data:

<Graph>
<Sales>
    <Row> <Month>Jan</Month> <Value>450</Value> <Target>420</Target></Row>
    <Row> <Month>Feb</Month> <Value>500</Value> <Target>550</Target></Row>
    <Row> <Month>Mar</Month> <Value>490</Value> <Target>490</Target></Row>
    <Row> <Month>Apr</Month> <Value>400</Value> <Target>520</Target></Row>
    <Row> <Month>May</Month> <Value>680</Value> <Target>650</Target></Row>
</Sales>
</Graph>

Simple stuff as usual but I hope easy to understand. I can create a calculation looking for Values that miss the Target value and build an 'if' statement oround it to highlight values in red.

<?if:(number(Value) div number(Target))<number(0.8)?><?attribute@incontext:color;'red'?><?end if?>

This is looking for Values less than 80% of their Target, of which I know there is only one the %Delta for Apr.


Looking back at the ExceptionalRiser, XML I should be able to pass a value to the group attribute. Here's where I have wasted some time trying to use native variables, for some reason I can not set the value crrectly for the chart to pick the variable value up. So I have resorted to updateable variables instead. It works, it just not appeal to my sense of right and wrong in XSL i.e. no native support for updatable variables. Seeing as Im conditionally formatting the percentage values I could createa variable in that cell. But if the chart appears before the table, its not going to work.

So I have created a field at the beginning of the template to loop through the values seraching for my erroneous vlaue:

<?for-each:Row?><?if:number(number(Value) div number(Target))<0.8?><?xdoxslt:set_variable($_XDOCTX,'val3',position())?><?end if?><?end for-each?>

If it finds the value, it sets the variable val3 with the current record pointer, note, this starts at 1.
In my chart code I have:

<ExceptionalRisers>
<ExceptionalRiser series="0" group="{xdoxslt:get_variable($_XDOCTX,'val3')-1}"  borderColor="#000000" fillColor="#ff0000" />
</ExceptionalRisers>


Notice the curly braces to get the XSLT engine to evaluate my variable value first and remember the group value starts from zero hence the '-1'.
This gets me my chart correctly showing the Apr bar in red.


Next time, multiple conditional bars, heres the RTF template and data so far.

Wednesday Aug 03, 2011

Crispy Charts!

It's been so long ... since I last post Kan has been stepping up and providing content. You may know I love weather and have a not so secret desire to see a tornado up close and personal. Since my last post I have come close, I got trapped in deepest darkest Mississippi and hot and sultry Montgomery, AL due to the most fantastic electrical storms. St Louis nearly kept me for an extra night but in spite of tornado sirens our plane snuck out just in time to avoid the worst.

I have also dropped quite a lot of poundage and last week took part in my first Triathlon, just a 'sprint' you understand. 1/2 mile swim, 11 mile ride and a 5K crawl, sorry I mean run. My dear wife joined me and we did not finish last! I even managed to come 8th in the bicycle ride!

To ease back into the blogging world, a bit of a corner case solution that could be applied to other issues and I wanted to get it down on 'paper' before I forget it. We have a customer that is having issues with the charting engine in 10g. As many of you know, you can customize the charts beyond what the dialog box in MSWord offers. Said customer was hitting an alignment issue in one of their customizations. When generating the chart to RTF all was well but with HTML and more particularly PDF the alignment was off. The first think to remember is that by default BIP will try and generate SVG outputs for the charts if it can. In the case of RTF it can not so you get an embedded image. In HTML and PDF, it can, hence the difference in alignment.

The difference in the chart quality is surprising:

 

The image format above and the svg below

I have had to shrink the images a little but you get the idea. Your svg charts are crisp and clear, even if you zoom in to them. The image formats, not so good.

We needed to log and enhancement/bug against the chart engine but was there a workaround? 

If you have ever used the export feature in the Template Builder to take a look at the XSL, you may have noticed a bunch of parameters the engine drops in. One of those controls the chart format, _XDOCHARTTYPE. The actual line is:

<xsl:param name="_XDOCHARTTYPE">image/svg+xml</xsl:param>

So we need a way to override this parameter for the HTML and PDF outputs. Well the HTML is easy, in the user preferences you can specify that you want chart images in your HTML output, simple. For PDF, you need to do a little more work.

Hopefully you know that you can have a set of config properties either at site level or at report level. There are a bunch you can change via the UI. We need to add one manually.

For Report Level only
1. Navigate to the report folder in the BIP catalog
2. Open the xdo.cfg file. If there is not one present, go back to the UI for the report and click the Config link, then just change any property to the opposite of the site level and Apply. This will create a cfg for you back in the report directory. You an change it back later. Now open it.
3. In the properties section add the following line

<property name="xslt._XDOCHARTTYPE">'image/png'</property>

4. Save the file and re-run the report to PDF output and you will get charts embedded as images rather than SVG and the alignment issue will be gone. There is a price to pay in clarity of the charts but its a work around that works.

If you want this for all reports you can put the same property into the 'master' cfg file in Admin/Configuration. Again, you may have to change a value to get the cfg file created.  

Definitely a corner case but the technique can be applied to any parameter settings in the generated XSLFO file. The xslt. prefix also allows you to set your own paraemter values in the template that might be needed ... I'll save that explantion for another time.

About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today