Bridge charts are a form of waterfall chart, that have an elevated first category.

Waterfall charts are a great way to show the contribution of different categories’ performance towards an overall goal. They take what could be a boring stacked column chart and add graphical drama by moving each category to the right of the previous one. They are also one of the few ways to neatly demonstrate negatively performing categories alongside positive ones.

The segments can represent anything, from brands, customers, suppliers or product types, to months, years, or age ranges. The waterfall chart is often used in financial comparisons where the analyst is looking to see the contribution by segment to the overall outcome, so currency information is most likely to feature on the value axis.

A standard waterfall chart in Web Intelligence takes its starting position at 0 on the Y axis as the first category begins the chart. Therefore, a standard chart could look a little like a staircase, going up from left to right (assuming consistent positive performance from category to category!).

However, I would like to show you a method for creating a ‘bridge’ chart, where the starting point for the analysis is the known target/budget position for the channel or period, and the end point is the actual total achieved.

I’m using the eFashion universe that ships with SAP BusinessObjects 4.2 SP6 but the theory is the same for any data that has actuals and target split by categories.

Fig 1

I’ve started with a simple query of product lines and their sales revenue for one year. Then I have added a second query from a spreadsheet for the targets per line and merged the two together to allow me to:

Fig 2

Then I have added a second query from a spreadsheet for the targets per line and merged the two datasets to achieve the table above.

Fig 3

Adding a new column and adding some arithmetic gives us a simple display of the variance of revenue v target.

However, using this as the data for our waterfall chart would only give us the zero-point starting position, and subsequent ‘set of stairs’. I want to add in a new member of the category dimension where I can insert some data into the variance column to enable our elevated starting position.

Fig 4

To achieve this, I have created a third query, using free-hand SQL this time. It is a very simple query and could also be created with a spreadsheet as well. All it does is give us a single dimension with one member: ‘Target Total’.

Fig 5

On running the third query I can merge the new dimension into the already merged ‘Lines’ object. This creates a new empty row in our table, as above.

Fig 6

Add a custom sort to the merged dimension so that ‘Target Total’ is at the top of the table.

Fig 7

Then we need to update our simple variance arithmetic with some more complex formula code. At this point I am also turning it into a reusable variable. The formula I am using is this:

Instead of the ‘ForAll’ operator you could use ‘In’ for reports that need finer control within tables or sections.

This variable gives us the equivalent of the total target figure in the first row of the table, leaves the variances by line untouched and means that the sum total of the variance column now adds up to the actual revenue total.

Fig 8

Creating a new table of the merged ‘Lines’ object and our ‘Var for Waterfall’ measure shows us the dataset we need to start our bridge chart with.

Fig 9

Using the ‘turn into’ process I have created a new waterfall chart (I’ve edited the origin of the Y axis to start at £14,600,000). We now have the start of our bridge, but to enable a safe landing on the far bank we need to make a few changes in the chart properties.

Fig 10

In the ‘global’ section of the chart formatting menu, towards the bottom, are these options. We can turn on the ‘Calculate and show the total’ option – this gives us the second supporting tower of our bridge and displays the overall actual figure after all the positive and negative contributions are added. Using the start, total, negative and positive colour selectors enables us to visually separate the target and total columns from the contributing categories that form the centre section of the bridge.

Fig 11

With some extra formatting changes we can see the final structure of our bridge chart. It’s an intuitive chart to interpret, describing a journey from one number to another and has potential uses in every industry and sector.

I think that this exercise proves once again that there is no need to export data to Excel in order to achieve complex visualisations. With Web Intelligence we can use a little ingenuity to create attention grabbing output that, once built, can just be scheduled over and over again directly to your audience.