And then there's the Waterfall Chart in PBCS

Ever found yourself looking to create a Waterfall chart in PBCS Dashboards only to be left disappointed?  Sure, you can have this done when Management Reporting eventually gets rolled into PBCS to replace Financial Reporting.  What if you need to have the chart now?  Well, you've come to the right blog!  I've managed to create one for one of my projects.  So, hang on tight and I'll walk you through how I got PBCS to produce the Waterfall Chart.

The above chart is attempting to visualise the evolution of the selected account (the Operating Costs in this case) in Dec from what it was in Jan across all the entities.  The extreme left and right bars (in blue) are the anchors showing the starting and ending position of the Operating Costs for Total Australia while the bars in between (the red and green bars) represent the movements (up or down) for each child entity of Australia; up represented in green and red otherwise.

You might be wondering what's the big fuss about this?  It looks just like the normal column chart that we can already produce in PBCS.  You're right.  It is a column chart but with a twist!

The twist...

You see, unlike the column chart we are used to, we need to have the movement bars be plotted relative to the position of its previous bar.  For instance, the bar for Victoria (second one from left) needs to begin from where the previous bar ends (in this case at $226M for Australia in Dec FY18).  The third bar (South Australia) will need to begin where Victoria has left off and so on and so forth.  How can we do that with the a bar chart?  A normal column chart will begin plotting from $0 of the Y-Axis.  So how can we trick the bar chart to do what we want?  The secret lies in manipulating the empty bars below the red and the green bars.  Notice that I have set the background to a light grey?  The reason is not because of aesthetics but rather we have bars coloured in the same colour as the background to give you the illusion that the movement bars are plotted relative to their immediate left bar.  There are hidden bars plotted below the red and green bars and they all start from 0.  Gotcha!  The screenshot below shows you the hidden bars (which I have set to black here to reveal them).

You might also be wondering why didn't I set the background and the hidden bars to transparent?  Well, I did, but unfortunately, they turn to black the next time I accessed the dashboard.  A little bug for Oracle to solve.  Now let's dive into the details of this solution, shall we?

Nuts and Bolts

This example requires users to make the following selections to perform the comparison:

• Comparison Scenario 1 & 2
• Comparison Version 1 & 2
• Comparison Year 1 & 2
• Comparison Period 1 & 2
• Parent Entity
• Account

The chart will then make comparison of the selected account across the children of the selected entity between Comparison 1 and Comparison 2 data sets.  You could vary this to compare across other dimensions that you may have (e.g. Cost Centres, Department, etc).

Remember I mentioned about manipulating the blank bars earlier?  Well, we have other elements that we need to handle too and here's the list of what's required:

• Anchor - the starting and ending values for the chart.
• Delta - the difference between Comparison 1 and 2.
• Increase - contains the absolute positive delta value.
• Decrease - contains the absolute negative delta value.
• Blank - the value of this element is dependent on the value in the previous bar (we will discuss the logic in more detail later in this blog).

These elements need to be created in a separate dimension and they are in the Measures dimension in this example.

A business rule is set to launch each time the dashboard is loaded to ensure the latest numbers are shown.  Let's examine the rule.

The Heart of the Solution...

In the nutshell, the rule covers the following processes:

• Step 1 - Populate all relevant measures and accounts for Comparison 1 Scenario, Version, Year, Period.
• Step 2 - Populate all relevant measures and accounts for Comparison 2 Scenario, Version, Year, Period.
• Step 3 - Calculate the Delta, Increase, Decrease, and Blanks for the Waterfall Chart.

Step 1

Step 1 - Comparison 1

• Step 1a - Clear Previously Populated Values
• Step 1b - Populate Anchor Measure (Parent Entity Only)
• Step 1c - Transfer the Income Statement Values to the Waterfall Accounts (Child Entity)

We start off with the clearing of data from the waterfall related measures to ensure that all previously populated numbers are cleared.  This is followed by populating the anchor measure with the selected parent entity value for each of the Income Statement accounts for comparison 1 data set (Scenario, Version, Year, and Period).  We will finally transfer the Income Statement account values to their respective Waterfall accounts.  Such accounts are required should you have other Waterfall Charts to prevent on wterfall chart from overriding data of another.  This example demonstrates the waterfall chart for entities, so I've created a set of waterfall accounts for entity, for instance:

• NPAT
• Revenue
• Operating Costs
• Non-Operating Costs
• etc..

You'd create another set of waterfall accounts for another chart, e.g. another set for Cost Centre.

Step 2

Step 2 - Comparison 2

• Step 2a - Clear Previously Populated Values
• Step 2b - Populate Anchor Measure (Parent Entity Only)
• Step 2c - Transfer the Income Statement Values to the Waterfall Accounts (Child Entity)

They are identical to the processes described in step 1.  The difference is in step 2, we are dealing with the Comparison 2 data set.

Step 3

Step 3 - Calculate the Measures for the Waterfall Chart

• Step 3a - Calculate the Delta
• Step 3b - Determine the Increase/Decrease
• Step 3c - Create Blocks
• Step 3d - Determine the Blanks

This step deals with the logic to produce the movement bars.  We begin with calculating the difference between Comparison data set 1 and set 2 for all the children of the selected entity.  The delta is then evaluated to assign its abosolute value (without signs) to either the increase or the decrease measures.  Once done, we proceed to create the necesary blocks before we determine the values for the blank bars.  Here, we need to first determine the anchor value if it is a positive or a negative.  There is a difference in the formula to calculate the blank for a positive anchor and a negative one.  Let's review the logic for each one:

Positive Anchor

Assign the starting anchor value to the blank measure if it is the first child of the selected entity.  So, in this example, Victoria (which is the first child in the Australia entity hierarchy) will have the anchor value minus the decrease of the first child entity assigned to its blank measure.  

All subsequent entities will have the following formula to calculate the blank measure:

Negative Anchor

The logic for the first child of the selected entity with a negative anchor is shown below.  Differences are highlighted:

The blank calculations for the remainder of the child entities, again with differences highlighted:

Forms and Dashboard

To produce the chart in a Dashboard, we'd need 2 forms to be created, one each for the grid and the other to plot the Waterfall Chart.  The form to show the grid has the IChildren of the selected entity across the columns with 3 measures in the rows, namely:

• Anchor
• Increase
• Decrease

The form for the chart on the other hand requires the same settings for the columns but the rows contains the following measures:

• Anchor
• Blank
• Increase
• Decrease

For the dashboard, create one and include the above 2 forms in the dashboad with the latter form (for chart) being set with a Chart Type of Column.  Remember to change the colour for the data series:

• Series 1 (Anchor) - Blue
• Series 2 (Blank) - Light Grey
• Series 3 (Increase) - Green
• Series 4 (Decrease) - Red

And please remember to set the background colour for the chart to be Light Grey as well so that the Blank bar appears hidden.

There you go.  The complete code line to produce the Waterfall Chart.

Before we go, some parting remarks...

Did you realise that this example covers only positive starting and ending numbers (anchors)?  This works perfectly when both numbers are positive or both are negative.  Additional logic and measures are required to address other data conditions such as:

• Starting anchor is positive and ending anchor is negative or vice versa.
• The movements moves from positive to negative or vice versa.

To make this post short and precise, I've decided to start off with the easy stuff and then cover the complex one in another post.  So, stay tuned for my next post where I'll discuss the logic to cover the above conditions.  Till then, stay safe and stay home! #Covid-19

Hierarchy-Driven SmartLists: And then there were two....

Apa khabar (that's "how are you" in Malay in case you're wondering)?  Hope you've enjoyed reading my maiden blogpost (Reverse Mapping using Hierarchy-Driven SmartLists).  Keeping to the theme, thought I'd share another use of a Hierarchy-Driver Smartlist; this time in an allocation process.

More often than not, you'll find yourself building some sort of an allocation logic for your planning application and as you know, there's more than one way to skin a cat.  My latest go-to method is using @XWRITE in conjunction with a Hierarchy-Driven Smartlist.  Not only does it help in addressing the ever-annoying block creation issue (we had our fair share of rants on this matter don't we, Pete?), using a SmartList provides the ability to avoid any kind of hard-coding in the business rules.

Interested?  Read on...

Background

A client of mine has this 4-tiered allocation process that they want implemented in their PBCS application.  Our solution uses a mapping table established to define the relationships between the following sources and targets:

• Entity
• Profit Centre
• Function Code
• Account
• Allocation %

The UV_Assessment_Cycle (in the POV) allows the client to maintain multiple cycles (or tiers) of allocation while the rows provides the ability to have multiple lines of allocation per cycle.  For instance, L001 defines a 100% allocation of cost

• From
• Entity: 4650
• Profit Centre: RAM RNY
• Function Code: 590 - COMPUTER SYSTEMS
• Account: OH2
• To
• Entity: 4550
• Profit Centre: MGR RAM
• Function Code: 590 - COMPUTER SYSTEMS
• Account: Overheads Pooling

Having this mapping table frees us from having to hard-code our allocation business rules.

The SmartLists
Define a hierarchy-driven smartlist for each of the following:

• Sender/Receiver Entity

• Sender/Receiver Profit Centre

• Sender/Receiver Function Code

• Sender Account

• Receiver Account

Accounts / Measures
Create the following accounts or measures and attach the above smartlists:

• Sender_Entity - SL_Ent
• Sender_Account - Cycle_Sender_Acc
• Sender_Profit_Centre - SL_PC
• Sender_Function_Code - SL_FC
• Receiver_Entity - SL_Ent
• Receiver_Account - Cycle_Receiver_Acc
• Receiver_Profit_Centre - SL_PC
• Receiver_Function_Code - SL_FC

Business Rule

The business rule will cycle through all the Assessment Cycles and Assessment Cycle Lines to the post a negative entry to the sender Entity/Profit Centre/Function Code/Account combinations and posts a positive entry to the receiver combinations.  A sample of the results is illustrated in the table below:

This is the full allocation code:

Say what?  Okay, okay.  Let's examine the section where we used the Hierarchy-Driven Smartists to first create the required block and then post the allocated numbers.  We'll start with the posting to the source combinations:

Notice that the rule uses the XWRITE function to post the value from the Sender Entity, Profit Centre, Function Code, and SenderAccount combination (section 1) to the Sender Entity, Profit Centre, Function Code, and Receiver Account combination (section 2).  What's cool about this is that the XWRITE will first create the block for the Sender Entity, Profit Centre, Function Code, and Receiver Account combination if it does not exist before the posting happens.

Also note that we have used the method of concatenating "HSP_ID_" with the value from the Hierarchy-Driven SmartList to resolve to the intended member. 

Here's the code snippet for the receiving entry and with similar method used when pushing the Sender Entry:

The last part of the rule applies the allocation ratio to both the sender and receiver entries and flips the sign for the sender entry:

Recap
This solution allows users to define their allocation mapping without having to rely on a hard-coded business rule (all thanks to the Hierarchy-Driven Smartlists).  The rule will cycle through 2 dimensions (i.e. the Assessment Cycle and the Assessment Cycle lines) when allocating costs between sender and receiver Entity, Profit Centre, Function Code, and Account combinations.  An example of the allocation result is illustrated in the table below:

Hope you have enjoyed this blog and stay tuned for my next blogpost.  Till then, terima kasih!