12.03.2014

Improved Waterfall Chart with SSRS

Technical Value

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. imageThe sorting is adjusted within the Category Group Properties to ensure the right order. First, it is sorted by CategorySortId, followed by CategoryLabel. imageWhat 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

Kommentare

Tim Pain
Do, 08.05.2014 - 14:55

Really cleaver idea - many thanks for sharing.

Ives
Fr, 24.03.2017 - 04:11

Thanks a lot for this very helpful topic.
I have an additional question: how did you manage to have labels outside (above and below) the bars? I couldn't get the same result with Range Column Chart where just labels inside the bars seems to be allowed...
Best regards,
Ives

Reginald
Di, 31.03.2015 - 19:15

Thank you for sharing this, great job. How did you manage to put labels outside the bar area?

sean
Di, 12.05.2015 - 18:43

Great solution, much much easier than trying to solve the bottom/top problem in sql. Running total function paired with ranged column and the line chart to really tie the story together.

Thanks for putting this together.

Jasur Ismoilov
Fr, 15.05.2015 - 13:20

Dear Jan, really great and precise explanation. I have a questions, is it also possible to have the same waterfall but each bar is divided into sub-categories? (meaning just like in stacked bar chart) It would be a great great help if you know it.
Thanks

Köhler
Jan
Köhler
Di, 26.05.2015 - 14:44

Hello Jasur,

yes it is possible, but as this is a custom solution there is no way to do this out of the box (stacked bar chart didn't help). One way is to do it like described with the total sum series.

1. By adding another column with the corresponding sub-category data but with the same CategorySortId and CategoryLabel where you want to stack your data.
2. (this is the "complicated" part) You have to control the position of each sub-category in your custom stacked bar chart manually. This means for example by calculating the high value and the low value so that each sub-category is staked on top of its predecessor. This could be done in SQL as well as with expressions within the dataset.

Regards,
Jan

Jasur Ismoilov
Di, 26.05.2015 - 17:45

Hi Jan,

Thanks a lot for your quick response, I have tried it with what you explained me, but it seems a little complicated because I have parameters before showing the graph, and tried using sql, unsuccessful. Is it possible to have a little consultation on gmail (diljasur@gmail.com) or skype: njasur1, whenever you have time. It would be a big help form your side.
Many thanks,
Jasur

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.

Klartext

  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden

Unsere Website benutzt Cookies, um bestmögliche Funktionalitäten bieten zu können. Durch die Nutzung unserer Website, erklären Sie sich mit der Verwendung von Cookies einverstanden. In unserer Datenschutzerklärung finden Sie unter §5 Informationen und Links, wie Sie diesen Maßnahmen jederzeit widersprechen können.