Insight

Insight from Agora Consultants

Create a Waterfall Chart Template using Reporting Service 2005

Waterfall chart is a special type of floating-column chart, it is normally used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns. A typical waterfall chart shows how an initial value is increased and decreased by a series of intermediate values, leading to a final value, waterfall chart can be used for analytical purposes, especially for understanding or explaining the gradual transition in the quantitative value of an entity which is subjected to increment or decrement.

 

 

The chart above demonstrates the how the Profit factors contributed to it.

Area 1: Revenue categories (stacked up based on the previous number)

Area 2: Revenue Total

Area 3: Profit (Revenue Total – Cost Total)

Area 4: Cost Total

Area 5: Cost categories (stacked down)

Although waterfall chart is not a standard type in Reporting Service 2005, but through some play around we still can make a waterfall chart base on the stacked column chart.

So how do we make a waterfall chart? My idea for above sample is if we have Data Source for Revenue detail and Cost detail, then we can calculate Revenue Total, Cost Total and finally Profit. So I want to build a template which will only need to specify Revenue and Cost data and get the chart shows up above.

The following are the steps to build it:

1.       Create a Chart using chart type:   Column -> Stacked Column.

2.       Create 2 parameters ‘LeftDS’ and ‘RightDS’ which will be used to store SQL.

For example:

Parameter:  LeftDS

Value:          Normal 0 false false false EN-CA ZH-CN X-NONE Select 90 as Value, 'CA' as Category

 

Parameter:  RightDS

Value:           Normal 0 false false false EN-CA ZH-CN X-NONE Select 40 as Value, 'Marketing' as Category

Note: make sure the 2 SQL have this 2 Columns

 

3.       Create 2 Datasets:

Dataset:  DSLeft               Query:               ="SELECT     Sum(Value) as Value From ( " & Parameters!LeftDS.Value & " ) t"

Dataset:  DSRight             Query:                ="SELECT     Sum(Value) as Value From ( " & Parameters!RightDS.Value & " ) t"

 

Note: after these 2 Dataset created, you have to manually create column ‘value’ in each dataset because the Dataset SQL is an Expression so it won’t automatically generate columns for you.

 

4.       Create another 2 parameters to store calculated Total from the above Datasets.

Parameter:  LeftTotal, make its default Value comes from a Dataset ‘DSLeft’.

Parameter:  RightTotal, make its default Value comes from a Dataset ‘DSRight’.

     

5.       Now we are ready create a Dataset to build the chart.

Again we use an expression here for the Dataset:

="SELECT       t1.Value, t1.Category, 1 as StackType From ( " & Parameters!LeftDS.Value & " ) t1 " &

"UNION " &

"SELECT " & Parameters!LeftTotal.Value &" as Value, 'Revenue Total', 2 as StackType " &

"UNION " &

"SELECT " & Parameters!LeftTotal.Value - Parameters!RightTotal.Value &" as Value, 'Profit', 3 as StackType " &

"UNION " &

"SELECT " & Parameters!RightTotal.Value &" as Value, 'Cost Total', 4 as StackType " &

"UNION " &

"SELECT        t2.Value, t2.Category, 5 as StackType From ( " & Parameters!RightDS.Value & " ) t2"

 

6.       Drag and drop 3 ‘Vale’ from dataset to the Data area, ‘Category’ to Category fields (sorted by orderindex).

 

7.       Create an empty expression as Series; we need this group to calculate Running Value.

Name: SeriesGroup                   Expression:   ‘’

 

8.       Modify first ‘Value’ column in Data Area make its value as formula below :

=Switch(Fields!StackType.Value =1 ,RunningValue(Fields!value.Value, Sum,"SeriesGroup") - Fields!value.Value,

Fields!StackType.Value = 5, Parameters!RightTotal.Value - RunningValue(IIF(Fields!StackType.Value= 5,Fields!value.Value,0),Sum,"SeriesGroup"))

The idea here is that we use the Running Value to repesentive gradual transition for Left and Right Areas, and make its Fill color as background color(make it invisible).

9.       Modify third ‘Value’ column, make its value=”=20” and Fill with background color, this stack we only used to show Number above the second stack.

 

10.    Set second stack colors.

Modify the second ‘Value’->Property->Apprences->Serial Style->Fill

=Switch(Fields!StackType.Value= 1,"Blue",Fields!StackType.Value= 2,"Navy",Fields!StackType.Value= 3,"Green",Fields!StackType.Value= 4,"Red",Fields!StackType.Value= 5,"Pink")

 

now it time to run the report and check it out and this is the template we can reused.

So how can we use this template.

It’s simple, just specify you SQL query into the 2 parameters: LeftDS, RightDS.

Make sure they should have same structure with ‘Select Value, Category

 

 


 
Comments are closed