Recently we completed a project to build a KPI dashboard for a large Australian organisation to show certain performance measures for each business unit. The challenge was that the client wanted a very specific kind of chart, basically a waterfall chart which displays the end target/goal as the final column. Using the Dundas charts control this task would not be that difficult as we could have used a chart similar to this and made a few tweaks.
However due to the clients Reporting Services Architecture the costs of Dundas licenses was not feasible so we needed to come up with a way to produce a similar chart using the native Reporting Services chart control.
Here's what we learned:
- The chart type to use is a Stacked Column Chart
- You need to add a data value the represent the data value you want to appear in the waterfall. In our case we added the KPI actuals eg:(Fields!KPIActual.Value)
- Then you need to add the same data as a second data value to the stacked series as a running total and subtract the individual first data value at each point. We added the same KPI actual data as a second data value which would be stacked below the first but this time using a running total less each KPI actual eg:RunningValue(Fields!KPIActual.Value, Sum, "chart1_SeriesGroupKPI_Measures")-(Fields!KPIActual.Value). We also set the background colour fill for this series to white so that it would blend into the background of the chart an could not be seen
The final result was a nice clean waterfall chart that shows progress towards an end target
