zondag, januari 08, 2012

A #WaterFall Chart in #BusinessObjects

One of my students asked me if it is possible to build a Waterfall chart in BusinessObjects. After some research, I can now tell you : yes, it is possible, and here is how.
First off, here’s our endresult. The chart displayed here is the version in DeskI, but WEBI can do it just as fine. In fact, I’ll be showing the procedure with Rich Client.
In Rich Client, I’m making a query that retrieves the data :
It lists a number of components that comprise our revenue. Next, I’m using the procedure I found here. The person who wrote that, shows how to make a waterfall chart in Excel. And it works just fine for Business Objects too.
Like for Excel, we’re going to calculate: Datum, Padding Plot and CrossOver.

First, datum: =RunningSum([Actual value]) – we won’t be displaying this in the end-chart, but it’s needed to calculate the others.

Second, Crossover : =If((([datum]-[Actual value])*[Actual value])<0 And (Abs([Actual value])-Abs([datum]-[Actual value]))>0) Then( [datum]-[Actual value]) Else(0)

Third, padding : =If([Crossover]<> 0) Then (0) Else(If(([datum]-[Actual value])*[Actual value]>=0) Then([datum]-[Actual value]) Else([datum]))

And last but not least, Plot : =If(([datum]-[Actual value])<>0 And [Crossover]=0) Then( If([datum]<0) Then(-1) Else(If([datum]-[Actual value]<0 )Then (-1) Else(1)))*Abs([Actual value])+[Crossover] Else (If([datum]<0)Then(-1)Else(1))*Abs([Actual value])+[Crossover]
For DeskI, all you need to do is replace the “[” and “]”  symbols by “<” and “>”.
So far, so good.
Next, we convert our table into a stacked chart :
Please note that the actual value and datum are not part of this chart.
Now for the trixy part. Padding needs to be made transparant. In the Properties pane of the chart, change the colour for padding to 240,240,244 – the next two colours need to be set identical. They’re the colour of your actual chart.
Ah, and last but not least, in DeskI, stacked charts don’t show negatives by default. So, in DeskI, you’ll need to change that :
There.. check the checkbox “Show Negative values”.
Looking good Glimlach
Peter De Rop

Geen opmerkingen: