HowTo: Waterfall Diagrams with SSRS

I realize a new trend for the usage of Waterfall Diagrams at customers. But how would you implement this by using only Reporting Services standard features? Well, a question which reduced my sleep for several nights because – as usual – this was an urgent customer requirement.

Before I begin to explain, what detailed steps are necessary, let me shortly mention where you should know one’s way about Reporting topics:

  •        Expressions
    ..can be used in many ways. You can’t know everything about but some basics should be familiar to you.
  •        Calculated fields
    …can be added to Datasets in order to use some additional columns with formulas or as they are already titled: with calculations.

Let’s assume that you want to report data out of a SSAS cube. But you don’t like to spend hours on writing the right MDX query. Here is a solution to reduce the need of complex MDX with 1 additional action you have to take on your dimension. Just add another attribute indicating the direction of your KPI. Will it increase the start value or decrease? I will explain the reason for this later on.

1. For my example I use a simple T-SQL query to simulate the cubes’ output to SSRS:

select ’dev1’ as description, 120 as val1, 1 as direction
union
select ’dev2’ as description, 20 as val1, -1 as direction
union
select ’dev3’ as description, 15 as val1, -1 as direction
union
select ’dev4’ as description, 10 as val1, -1 as direction
union
select ’dev5’ as description, 30 as val1, 1 as direction
union
select ’dev6’ as description, 20 as val1, 1 as direction

The result looks like this:
Query Results

2. Define a Dataset with the query above (including a Data Source which you need to create first).

3. Now add a Calculated Field with the name “Val2” and the expression:
=Fields!val1.Value*Fields!Direction.Value

4. Drag a Chart from the toolbox and drop it to the Report body and select "Range" as Type

5. Drag the column val1 from the Report Data Area and drop it to the data fields area

6. Edit the Series Properties and replace the Top Value field by the following expression:

=Fields!val1.Value +

IIF(Fields!Direction.Value=1,

       Previous(RunningValue(Fields!val2.Value,Sum,Nothing)),

       RunningValue(Fields!val2.Value,Sum,Nothing)))

replace the Buttom Value field by the following expression:

=IIF(RowNumber("DataSet1")=1 and Fields!Direction.Value=1,

0,

IIF(Fields!Direction.Value=1,

       Previous(RunningValue(Fields!val2.Value,Sum,Nothing)),

       RunningValue(Fields!val2.Value,Sum,Nothing)))

Notice:
The RunningValue is always summerized on postive and negative values of the Calculated Field.

The beam needs to be positive without Plus or Minus sign. This little trick guarantees the right position.

7. Drag the column description from the Report Data Area and drop it to the category fields area

8.  Let’s improve the column colors by setting the Fill Color of your <Expr> Series to the expression:

=IIF(Fields!Direction.Value=1,"LimeGreen","Red")

9. Finish – you’re done except improving Titles, Legends, etc…