BLOG

Improved Waterfall Chart with SSRS

12.03.2014 Jan Köhler

Some time ago, I have received the task to develop a Waterfall Chart with Microsoft’s Reporting Services. The requirements were very specific and required much more than a simple SSRS standard chart. At the end the result made ​​everyone happy, which is why I want to introduce you my approach.

Establish the basis

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

  1. SELECT ProductCategoryName, SalesAmount
  2. FROM
  3. (
  4.     VALUES (‚Components‘ , 577.13)
  5.          , (‚Accessories‘, 103.77)
  6.          , (‚Bikes‘      , 865.08)
  7.          , (‚Clothing‘   , 118.84)
  8.          , (‚Other Vehicles‘   , 292.16)
  9. ) Sales(ProductCategoryName, SalesAmount);

The result of the query looks like this:

image

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:

image

The Waterfall Chart

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

image

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.

image

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

  1. High Value = RunningValue(Fields!SalesAmount.Value, Sum, Nothing)
  2. Low Value = RunningValue(Fields!SalesAmount.Value, Sum, Nothing)
  3.                 Sum(Fields!SalesAmount.Value)

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

image

Improving the Waterfall Chart

Now we will add some reference lines to improve the presentation of our Waterfall Chart. These lines are used to illustrate the dependency to the previous product category and allow the viewer a better visual understanding. As preparation, the horizontal gridlines are hidden. The width of the SalesAmount data series can be further reduced by changing the CustomAttribute Property, PointWidth to 0.6. Now we come to the said reference lines. For this purpose we add another data series, choosing again SalesAmount as our source column. SSRS creates automatically another Range Column Chart, naming it SalesAmount1. Via the context menu, we can change the chart type into a Stepped Line Chart . By simply dragging and dropping the new Stepped Line Chart upwards until it comes first we can ensure that it is drawn behind the SalesAmount Ranged Column Chart.

image

Also for this data series, the Y value must be changed. All we need to do is, to use the same expression as for the high value of our Range Column Chart.

  1. Y Value = RunningValue(Fields!SalesAmount.Value, Sum, Nothing)

At last, we’ll adjust some properties for both charts for presentation purpose. Color and BorderColor is set to Silver. For the Range Column Chart, BorderStyle is set to Solid, for our Stepped Line Chart to Dashed. The Border around the Range Column Chart is needed because otherwise, we will see an unwanted artifact like a small step where the line disappears behind the range. The result looks like this:

image

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.

  1. WITH Sales
  2. AS
  3. (
  4.     SELECT ProductCategoryName, SalesAmount
  5.     FROM
  6.     (
  7.         VALUES (‚Components‘ , 577.13)
  8.              , (‚Accessories‘, 103.77)
  9.              , (‚Bikes‘      , 865.08)
  10.              , (‚Clothing‘   , 118.84)
  11.              , (‚Other Vehicles‘   , 118.84)
  12.     ) Sales(ProductCategoryName, SalesAmount)
  13. )
  14. SELECT *
  15. FROM
  16. (
  17.         SELECT 1, ProductCategoryName, SalesAmount, NULL
  18.         FROM Sales
  19.     UNION ALL
  20.         SELECT 2, ‚Sum total‘, NULL, SUM(SalesAmount)
  21.         FROM Sales
  22. ) Result(CategorySortId, CategoryLabel, SalesAmount, TotalSalesAmount);

The result of the query looks like this:

image

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.

image

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

image

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:

image

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.

image

Your email address will not be published. Required fields are marked *

Join #teamoraylispeople

Gestalte mit uns
die Welt der Daten