Waterfall Chart in SSRS

Question

I'm trying to make a waterfall chart in SSRS Report Builder 2012 3.0. It should look something like this:

I have used a Range chart type but I only get a bunch of bars next to each other. So they don't start from the top and go down like in the example. Google is pretty 'empty' about this type of charts in SSRS so I'm out of thoughts. Is there some sort of property that I should consider.

Thanks for helping.

Answer 1

I have done in a similar way.

1. you need to add a column with the difference of the Amount, remember to make 0 the difference for the very first value instead of null.

2. For the Top Value, use the regular Amount Field

3. The magic is in the bottom value. it must have the following formula.

=iif(Sum(Fields!DifferenceAmount.Value) = 0, Sum(Fields!DifferenceAmount.Value) ,Sum(Fields!SalesAmount.Value) - Sum(Fields!DifferenceAmount.Value))

4. To make it more understandable you can change the colours with the followinf formula.

=IIF(Fields!SalesAmount.Value >= Previous(Fields!SalesAmount.Value), "Green", "Red")

and done :)

Answer 2

I am re-posting this from this blog entry by Jan Köhler(j.koehler [at] oraylis [dot] de), so in case it does get removed at some stage, the answer will still be relevant.

For my example we need a dataset, let’s call it dsSales, as our data source. For this purpose we use the following query.

SELECT ProductCategoryName, SalesAmount
FROM
(
    VALUES ('Components' , 577.13)
         , ('Accessories', 103.77)
         , ('Bikes'      , 865.08)
         , ('Clothing'   , 118.84)
         , ('Other Vehicles'   , -292.16)
) Sales(ProductCategoryName, SalesAmount);

The result of the query looks like this.

We use a Range Column Chart as basis, which is linked to the dataset dsSales. The column SalesAmount is used initialy for our data series. As Category Group, we select the column ProductCategoryName. And for better illustration, Chart title, Axis Title and Legend can be hidden. The chart should now look similar to this.

The Waterfall Chart

Let’s take a look at our SalesAmount data series.

A Range Column Chart has two value parameter, a high value, currently assigned with Sum(SalesAmount), and a bottom value, which is currently assigned with nothing. Now how do we get our Waterfall Chart? What we wane do is, to move the current Range slightly upward or downward depending on its predecessors. One possibility is to calculate the running total through expressions. For this we use the function RunningValue. I have outlined the approach for the purpose of a better understanding below.

As a result we’ll get these two expressions for the higher and the lower value.

High Value = RunningValue(Fields!SalesAmount.Value, Sum, Nothing)
Low Value = RunningValue(Fields!SalesAmount.Value, Sum, Nothing)
                - Sum(Fields!SalesAmount.Value)

If we look at the preview, the report should now look similar to this.

Adding a total sum

Now that we have our Waterfall Chart we can make further modifications. What about a total sum? To make it easy we would need to add a separate data series, too. First we modify the query from the dataset dsSales.

WITH Sales
AS
(
    SELECT ProductCategoryName, SalesAmount
    FROM
    (
        VALUES ('Components' , 577.13)
             , ('Accessories', 103.77)
             , ('Bikes'      , 865.08)
             , ('Clothing'   , 118.84)
             , ('Other Vehicles'   , 118.84)
    ) Sales(ProductCategoryName, SalesAmount)
)
SELECT *
FROM
(
        SELECT 1, ProductCategoryName, SalesAmount, NULL
        FROM Sales
    UNION ALL
        SELECT 2, 'Sum total', NULL, SUM(SalesAmount)
        FROM Sales
) Result(CategorySortId, CategoryLabel, SalesAmount, TotalSalesAmount);

The result of the query looks like this.

What has changed?

• TotalSalesAmount contains the desired total in a separate line.
• CategorySortId is a new column that is used for sorting, because the total sum should be the last one on the chart.
• ProductCategoryLabel was renamed into CategoryLabel because it no longer exclusively contains identifiers for the product categories.

Because of the changes we have made on the dataset the chart needs to be updated too. The Category Group ProductCategoryName must be replaced by CategoryLabel.

The sorting is adjusted within the Category Group Properties to ensure the right order. First, it is sorted by CategorySortId, followed by CategoryLabel.

What is missing is the desired total sum. For this purpose, a new data series is added by selecting TotalSalesAmount. Setting the CustomAttribute Property, DrawSideBySide to false prevents that data points with equal x value are drawn side by side as the name might suggest. Also, the width is reduced again through PointWidth to 0.6. The final result looks like this.

Summary

What I have demonstrated to you is how you can create another chart type, which is not offered as a template, through existing standard chart types and some expressions. By combining different chart types, it is also highly customizable and extensible. In addition, Data labels can be added. The background color can be adjusted depending on its value. Unusual or special data points can be highlighted. Whatever is necessary to increase the meaningfulness of the observed information. Still a foretaste to further stimulate your Fantasy.