asked
Waterfall reporting
Does anyone have any links for waterfall reporting? We have to report starting universe (1M records) and then for each exclude rule, tell how many records were dropped for each and then provide a percentage of records dropped for each exclude of the total universe. Looking at SSRS which is what we use now by reading the table and performing count and each exclude. Trying to make more efficient with little user intervention. Attached a sample of what we have but it is a copy and paste. I would like to automate a process that would populate Excel from SQL Server. Thanks! [link text][1] [1]: /storage/temp/4553-sample-waterfall.xls
I believe that the best option for SSRS report would be to start with Range Column Chart. It will take some effort to get it to work correctly. First problem to overcome is to change the range of each bar so that the start of the next always matches the end of the previous. Because your data points are ever decreasing, this means that the bottom of the previous bar must match the top of the next and the sum of all "drops" should be equal to the difference between the bottom of the first and the top of the last bar. You can use the RunningValue function for that. Here is the link to the [Improved Waterfall Chart with SSRS][1] article by Jan Köhler which has a working sample. Another common problem to overcome is the appropriate setting of the chart's start value. In the sample data in question, the start value is ~ 17 million, the end value is ~ 6.5 million, and the drop counts are vastly different depending on the reason. If you leave the chart's start value at 0 then it would be difficult to see the size of some reasons because the 2 biggest ones (marketing optout and bounced emails) are so much bigger than others. Of course, with such big difference between the largest (~ 5 million) and smallest (48), it would be difficult to see the height of the smaller bars (they will all appear as a rather thin line), but setting the start value to some number which is just slightly smaller than the end value will greatly improve the look and feel of the waterfall chart. We use PowerBI where I work and have some reports displaying the waterfall charts. Because it is not possible to programmatically set the start value of the waterfall chart visual, I just use R visual writing R code for rendering the chart. This allows a complete control over how to display the data and labels (including angled labels so that the long ones still fit nicely). I added the waterfall chart to the spreadsheet in question (changing the numbers to "in millions"). Here is the screenshot of the chart from your updated Excel file: ![Chart][2] The [updated spreadsheet][3] is attached. BY examining the chart data section you will see what I had to do to construct the chart. Hope this helps. Oleg [1]:
https://www.oraylis.de/blog/improved-waterfall-chart-with-ssrs [2]: /storage/temp/4554-waterfall.png [3]: /storage/temp/4555-4553-sample-waterfall.xlsx
18 People are following this question.
