Waterfall charts in Business Objects Webi

I spent a few hours fighting with Business Objects yesterday, trying to make a waterfall chart.

Waterfall charts are beloved of marketing types and people who want to look at the impact of several different factors on sales. They look like this:

For my hypothetical wild animal shop, I can see that there's been a big contribution from sales of aardvarks, whereas bears and cattle are bringing results down.

You could just use a bar chart, but that will tend to be misleading, as bars for negative quantities will display the same area as bars for positive quantities, when we want to distinguish one from the other.

Creating waterfall charts in Excel is fairly easy to do; you use a stacked bar chart, and with some ingenious meddling with formatting, make one bar in the stack invisible, so that the quantities you want to display appear to be magically floating in the air.

Unfortunately, the formulas for doing this in Excel aren't available to you in Business Objects, and the formatting for graphs in Business Objects isn't exactly wonderful either. However, I want to take advantage of all the money and time that my company has put into Business Objects, and instead of stuffing numbers into a spreadsheet and emailing it every week, I'll have Business Objects send the report to people automatically, and I can spend more time drinking coffee and staring out of the window.

Building a waterfall chart turns out to be a bit painful; you can (fairly easily) build something that is all one colour, but I wanted to use some conditional formatting - things that declined year on year should be in red, whereas things that were improving should be in green.

Lots of stuff in gloomy navy blue.  What's up?  What's down?

After an unproductive day, I discovered this handy post, and remembered once again that Google Is Your Friend, and honest toil is not preferable to borrowing the work of another.

This is a lovely, elegant solution, but it doesn't do quite all we ask of it. For my waterfall charts, I won't always have positive values - sometimes the bar will drop below the x-axis. The final bar, which David is interested in having the same colour as the first bar, should be either red or green for me. Finally, it works for a list where it makes sense to talk about a maximum, because his data is categorised by date; I want to have a waterfall that will work with an arbitrary ordering of categories.

Because we're going to have some bars that cross the x-axis, we can't do it with just one measure for positive values and one for negative, as David has. But we only have to double these up. The measures you need to create (to make a chart on a measurement called 'Value' to follow David's convention) are:

Position

=If(IsNull(Previous(RunningSum(([28D RN DIFF - MKTG]))));"First";"Middle")

Axis Cross

=If(Sign(RunningSum([Value]))=Sign(Previous(RunningSum([Value])));0;1)

White Space

=If([Axis Cross] = 1;
    0;
    If([Value] > 0;
    If(RunningSum([Value])>0;
    Previous(RunningSum([Value]));
    RunningSum([Value]));
    If(RunningSum([Value])<0;
    Previous(RunningSum([Value]));
    RunningSum([Value]))))

Value First (up)

=If([Position]="Middle";0;If [Value]> 0 Then [Value] Else 0)

Value First (down)

=If([Position]="Middle";0;If [Value]< 0 Then [Value] Else 0)

Value (up) above axis

=If([Position]="Middle" And [Value] >0 And RunningSum([Value]) > 0;If([Axis Cross] = 1; RunningSum([Value]);[Value]);0)

Value (down) above axis

=If([Position]="Middle" And [Value]<0 And Previous(RunningSum([Value])) > 0 ;If([Axis Cross] = 1; Previous(RunningSum([Value]));-[Value]); 0)

Value (up) below axis

=If([Position]="Middle" And [Value]>0 And Previous(RunningSum([Value])) < 0 ;If([Axis Cross] = 1; Previous(RunningSum([Value]));-[Value]); 0)

Value (down) below axis

=If([Position]="Middle" And [Value] <0 And RunningSum([Value]) < 0;If([Axis Cross] = 1; RunningSum([Value]);[Value]);0)

There's two ways to handle displaying the last row - for me, it's no different to anything else.  If you want David's behaviour, for Position you'll want to use

=If(IsNull(Previous(RunningSum(([28D RN DIFF - MKTG]))));"First";if(RunningCount([Value])=Count([Value]) ForAll [dimension];"Last";"Middle"))

- which means that we don't rely on a maximum like David's solution. Using the Axis Cross object allows me to have formulae that are much easier to read, and perhaps more importantly, there's now a closer resemblance between the formula for moving towards and away from the axes, which should make this easier to understand. (I have removed my previous spaghetti-like code because it doesn't do anything apart from display the confused thought processes of a madman.)

Arrange your columns in the order above, and then follow all the instructions David has already put in place. (Having them in this order means that you need to choose your palette as white/green/red/green/red/green/red - alternating them like this makes it easier to see you've put them the right way round).  Then you should end up with some charts like this:

Ah! Marvel at the beauty of a nicely coloured graph! Or wonder what the categories might be along the x- and y- axes, that I'm so assiduously attempting to conceal...