How to Create a Waterfall Chart in Crystal Reports
At one time the number one request for Crystal Reports was to have waterfall charts. Although the waterfall chart is not an available Type in the Chart Expert, this blog post has two sample reports that will help you create your own.
The waterfall chart is a Stacked Bar Type and uses a set of formulae to break out the data into the appropriate bars. The main trick to creating a waterfall chart is to find the previous value of your chart’s Show Value.
There are two sample reports as mentioned above.
Below are brief outlines of the two methods used in these sample reports and there are more detailed instructions on the reports visible in the Design tab. Please note that this method has been tested in a limited amount of data. If you see an issue where the bars are returning incorrect data for your record set please let me know as changes may have to be made to the report’s formula set.
If you find that the order of your chart is not correct but all of the data is present on your chart, please pay particular attention to the 1_Chart_OnChangeOf formula on your report. Just like any other chart, you need to set the order of the chart using the appropriate formula output. There are instructions in this formula as well.
Method 1: Rolling up Data from an Existing Report and Passing to a Sub-Report
The waterfall chart via sub-report method is aimed at those who have existing reports in which they don’t want to change the data structure.
This method is also for those that are not familiar with writing their own SQL. Given that, there is still a simple Top N Command object in the sub-report which is only used to return dummy data. This method works with several formulae that are added to the existing report and they roll up several string running totals of data which are then passed to the sub-report. The sub-report contains a chart which parses out the sub-report link.
This method is a bit more work than the second method and the chart is a bit more limited as well. The main limitation is that the chart in this method will not produce the summary bars in the middle of the chart. In the screenshot above there are summary / Final bars for 2012 & 2013 which are only available in the method below.
Method 2: Using a Command Object to Create the Appropriate Data for the Waterfall Chart
This is the recommended method to create the waterfall chart as no sub-report is required and it should be easier to build out than the method above.
The Command based method is for those who are more familiar with writing SQL. This method will only work if you are able to return previous values in your query which is done easily via a Lag() function. If your database does not have a Lag() function then returning previous values with a sub-query is a possibility but is not covered in this blog or in the sample reports. A Row_Number() function is also used in the Command object in order to differentiate between what is the regular data (green and red bars) and what is the summary data (blue bars) in the chart.
Like the sub-report method report, the instructions are available on the report itself in the Design tab. The query used to build the sample is pasted below to show that it is fairly simple and is also available in the Design tab. Please note that the query syntax is written in SAP HANA syntax. Your Command syntax may vary.
Note that there are three parts to the query. The first part returns the non-summary or regular data. This data includes an On Change Of value (PERIOD) as well as a Show Value (VALUE) that will be used in the chart. Note that the data for this sample report is aggregate data for each month.
The second part of the query returns the final value of the data set. This final value is identical to the final value returned in the first part. This is done so that the chart will have a final / total bar at the right side.
The third part of the query returns the yearly summary data which is used for the year final bars in the chart. The WHERE clause ensures that we bring back only the value for the 12th month of the year.
SELECT WFD2.PERIOD, WFD2.VALUE, LAG(WFD2.VALUE) over (order by PERIOD) as VALUEPREV, ROW_NUMBER () over (order by PERIOD) AS ROWNUM FROM CR.WFD2 UNION ALL SELECT '3001-01-01', WFD2.VALUE, 0, 0 FROM CR.WFD2 WHERE WFD2.PERIOD = ( SELECT MAX(WFD2.PERIOD) FROM CR.WFD2 ) UNION ALL SELECT WFD2.PERIOD, WFD2.VALUE, 0, 0 FROM CR.WFD2 WHERE MONTH(WFD2.PERIOD) = 12
I hope that you find this post and the sample reports helpful.