Creating Parameter Driven Custom Waterfall Charts in BI Office
Ian Macdonald , Principal Technologist, Pyramid Analytics
Using standard BI Office Waterfall Chart and parameterization capability, we can create a robust parameterized solution. The previous blog article showed how to create the data series and chart formatting to build a custom waterfall chart in BI Office for one-off scenarios to control data series naming. While it produced the results desired, it was somewhat rigid in terms of the measure being charted and the time series shown. It would be great if we could make the output more flexible by allowing the user to select the measure and time periods to chart.
In order to do this, the inputs into the calculations, time range and the start and end periods need to be dynamic. In BI Office this can be achieved by the use of parameters.
This article will show how to define the parameters required and use them in the data selection, member and measure calculations to provide greater flexibility and usefulness of our waterfall chart.
There are a number of steps that we need to consider to build out our solution.
- Create the parameters to drive the data selected
- Create calculated members and measures, driven by the parameters
- Create the grid of data to be charted
- Format a standard BI Office stacked bar chart to appear as a bridge or waterfall chart
BI Office parameters allow us to “feed” selected measures or members into a calculation at run time, making those calculations dynamic.
In order to make our waterfall chart dynamic, we need to able to select a start and end point for our time series (waterfall charts are almost always a time series analysis) and use those points to drive the range of time periods selected and also the individual values for the Starting and Ending period. We can also use parameters to select the numeric measure that will be charted.
For this example, we will create a waterfall chart based on months; so the first thing to do is create two parameters that will define the start and end points of the range of months.
Create a parameter called Starting Period, which contains all the months that you may want to select. In this example we are using the Month Dates Hierarchy in the standard BI Office Demo Database:
Create a parameter.
Note that we have described February 2008 as the default, as there is no time period before January 2008 and the starting position of our waterfall chart will be the preceding month value.
Now duplicate the parameter, rename it and set a different default month:
Create Ending Period Parameter.
We can also offer the user the option to choose the measure being charted. To do this, create a parameter on the Measures dimension and select the measures to be offered. In this case, we’re choosing Sales, Profit and Expenses:
Select Sales, Profit and Expenses.
Calculating the Measures
We now need to construct the data grid that will be charted by selecting the items to appear on the rows and columns of data to be charted. This involves creating calculated items and sets for the months and calculating the rise and fall between time periods for the measure selected.
We can reuse the measures we created in the first article, Delta, to calculate the Rise and Fall between time periods; Rise will contain the positive change and Fall will contain the negative change (see the previous article for details on how these are constructed using the Calculation Designer). However, we want the Measure that is being used to calculate the Delta to be dynamic, so we need to edit the Delta measure and substitute the Measures Parameter for the previously selected Measure (Sales) for the Current Period and the Previous Period in our expression:
Select the Measure.
Rise and Fall are driven by the value of Delta; so when we place them on the columns of our grid, the parameter used to input into the Delta calculation is automatically brought in. As Base is driven by Rise and Fall, this will also be driven by the parameter:
Setting the time Range and Start and End Periods
On our rows, we want the set of months defined by our starting and ending period parameters as well as the value of the month previous to the starting period (to create the starting value) and the ending period value.
Using the two previously created period parameters, create a set using the Range function using the time period parameters as inputs:
Create Range function.
Build Custom Set for Range.
Of course, this set can be published to other users if required. Select the resulting set from the Time hierarchy to place the range on the Rows. The Waterfall Time Periods will automatically bring in the Starting and ending Period Time parameters to allow the selection:
The last items we need to create are the Start and End periods. Recalling the previous article, the End period is very simple to create as it is the value of the last selected month, i.e. the value of the Parameter, Ending Period. We can modify the existing End calculated member from last time to point to the Ending Period Parameter:
Select Ending Period Parameter.
Start Period will show the value of our measure for the beginning of the first time period selected, i.e. that value of the previous period.
Creating this calculated member is a little more problematic. We cannot use the Calculation Designer and its Functional Selections (Next, Previous, etc.) as we need to both select the Parameter and the Previous option simultaneously, which is impossible.
However, we can apply the Previous Member function to the Starting Period parameter using a right-click on the Starting Period parameter and selecting Custom Elements, Member Sets, Hierarchical and Previous Member:
Select Previous Member.
Unfortunately, as this function is contained under Member Sets, it results in a Set rather than an individual member; and if we were to use the Set to display the Previous month, it would appear with the month name in the Grid. What we want is the Start name to appear. We will have to then create an Aggregated Member from the Set called Start. As the set will only ever contain one member, the Aggregate function will return the correct value:
Choose the Aggregate function.
Finally, as before, set Custom Order for the columns and select Start, Waterfall Time Periods and End to appear on the rows:
Custom Order on columns and Start, Waterfall Time Periods and End on rows.
From there, format the chart as in the previous article to get the Waterfall Chart effect. You can of course, add additional slicers if needed, but the Parameters allow you to select the Measure and starting and ending points of the range of time periods to display:
Finished waterfall chart.
Until next time!
Acknowledgements: Thanks to my colleague Michael Raam for suggesting the initial approach to Waterfall Charts in BI Office.