Introduction
A customer of mine came up with the question, if we could do some variance reporting on the SAC in the context of line charts. Though there are settings which allow for variance reporting with line charts, they add another bar chart below the actual line chart, which then provides the positive or negative deviation between two key figures.
Settings this standard SAC variance feature up in an already packed dashboard may lead to sizing issues of the already used components and decrease readability of the chart itself. On the other hand integrating the variance into the line chart itself increases the density of information in the reporting case and allows for a more context driven analysis of your variances between KPI A and KPI B or scenario A and scenario B like in an Actual vs. Plan reporting scenario.
Since I wanted to support the self service positioning of the SAC and also support hybrid architecture scenarios, I decided to set this integtrated variance line chart report (sometimes also called “Icicle chart”, even though you will find totally different looking results on google) based entirely on the Story Mode and a SAP BW Live Connection.
Following these thoughts, the upcoming Blog guides you through the process on how to transform a standard variance chart like this:
to this integrated design:
Prerequisites
This Blog content has been created under the following technical conditions:
- Live Connection to a query on a SAP BW system
- SAC tenant on version 2021.20.22
- SAC Story in Canvas Mode
Spoiler: The procedure provides a feasible solution which can be used in productive environments, though at the time I am writing this blog, the story mode lacks some functionality, which would ease the creation and reduce the complexity of the setup. I am pointing out the from my point of view missing features as I am describing the procedure. In the text I will mark missing features with a ” * “ and list them afterwards in the conclusion.
All set, let´s go.
Implementation
Our fictional sample scenario is a workforce comparison between Actual and Plan for a company´s FTE along the the time series of booking periods from January to December. To get a rough overview, I started with a simple line chart. As we can see, we started above plan in the first quarter, but our FTE for some reason declined during the second quarter below plan, followed by a sharp rise in the third until leveling at the year end.
As we can see in this scenario, we have sections in our report where our Actual is above Plan and vice versa we also have sections where our Actual is below our Plan. these numeric differences between Actual and Plan are in fact the core information we want to visualize in our target chart. Visualizing this, allows us to get rid of the plain Plan line, since let alone it does not provide any essential information to us.
So how to reach our goal of an integrated deviation indicator? Taking the SAP SAC standard variance chart approach into account, the deviation is shown as bars for each element on our x-axis while also showing the Actual FTE as our base line, from which the deviation bars are arising. That at the same time is the essential difference of our approach and the standard variance chart, as the standard chart takes the x-axis as a base line. So in order to place our deviation bars directly on top or below the Actual data point, we need some kind of foundation or base for them.
We will solve this problem by utilizing the SAC´s integrated feature of a combined chart consisting of a line chart and a stacked column chart. Doing so, we just have to think about a way to derive the correct size of the foundation and the size of the bars indicating the deviation. Below you see a sketch of how we will approach this issue, depending on being above or below Plan at a certain point in time.
In order to be able to distinguish the two possible deviation scenarios (Actual > Plan and Actual < Plan), we will create two calculations in our story. Since our sample company is eager to grow in regards to workforce, being above Plan is considered positive and being above is considered negative. The definition of those two calculations is fairly simple by just applying a subtraction of our ACT FTE and our PLAN FTE, depending on which number is larger. Please be aware of two restrictions we have to apply:
1. We need to make sure, that our calculation is returning 0 if our condition is not met.
2. Since we use a stacked bar chart, we need to use the ABS() function to negate any negative presigns in our calculation result. Not doing so would result in a negative deviation bar to be shown on the x-axis level and not at our Actual base line.
This results in the following two calculation formulas.
Deviation positive:
ABS( IF( [#FTE (Actual)]>[#FTE (Plan)];[#FTE (Actual)]-[#FTE (Plan)];0))Deviation negative:
ABS(IF([#FTE (Actual)]<[#FTE (Plan)]; [#FTE (Plan)]-[#FTE (Actual)];0))Adding those two calculations to our line chart already shows us the deviations between Actual and Plan quite nicely. For readability reasons, I also defined the colors for the calculations to red (negative deviation) and green (positive deviation). But of course, this is only an intermediate step since the line chart itself just got more chaotic instead of sleeker as we want to be in the end.
Having the deviations correctly in place already from a value perspective, we can now move on with changing the chart type to the combined line / stacked-bar chart type. In that process we will already remove the Plan FTE key figure, since it was only needed to create and validate our deviation calculations.
After changing the chart type, initially all key figures will be shown as parts of the stacked bars. For our chart we want the Actual FTE the line chart component and the deviation calculations the bar components. After changing this, we will get the following result. Not bad at all.
So we do have a deviation bars all set up, but they are stuck at the x-axis base line at the moment. Looking back at our implementation idea, we need to take care of their base now, in order to lift them to our ACT FTE data points.
To do this, we will create another calculation in our story, called “base”. The base needs to take care, that for periods where we are above Plan, the green deviation bar´s upper bound is equal our ACT FTE and for periods in which we are below plan, the red deviation bar´s lower bound is equal our ACT FTE. We are doing this by subtracting the positive deviation from our ACT FTE in order to make make the deviation and base sum up to the Actual data point. On the other hand we can set the base equal to the Actual data point´s value for period we are below plan.
So we can implement the following formula for our base calculation:
IF([#Deviation (positive)] >0 ;[#FTE (Actual)]-[#Deviation (positive)];[#FTE (Actual)])As soon as you add the base calculation to the bar section, and change the order to be it at the bottom, you can see the result as follows. That is looking good already and quite close to where we want to get.
As we are now done from a data perspective, we need to take care of the formatting. Being said easily, this turned out to be tricky facing severe SAC restrictions and pitfalls in story mode.
First we want to “hide” our base bars, since we want the deviation bars to appear floating around the ACT FTE line component. This can easily be done by changing the color of the base calculation key figure to white. Of course this is not ideal, since it forces us to make sure, that the base bar color matches the background of your report or dashboard. For unicoloured backgrounds this may be possible, in case you have gradient backgrounds, this may look strange. It would be great, if SAC would provide the functionality to be able to make chart components transparent.(*) Thinking about this even further, removing certain key figures from the legend would be an exquisite feature in such a scenario, but I can understand, that the use cases for such an specific feature are rather limited.
Changing the color of the base bars leads us to the next formatting challenge. As you can see on the chart above, changing the color to white causes the data point description to automatically change from black to white due to SAC´s integrated readability features. The feature itself is nice and useful, but not being able to change data point font colors per key figure in a chart is not and a huge hassle in our scenario. The fact that we cannot set font colors individually for each key figure forces us to select one color for all data points in our chart.(*) Since we need to get rid of the black text in our base bars to make them entirely invisible and thereby useful at all, we need to pick white as the general font color for the data points. That leads us to the situation, where we are able to see the data point texts on our deviation bars if we picked colors with sufficient contrast, but it makes the data point descriptions for our Actual disappear, which is nothing we want to happen.
Sadly I could not find any setting which did the trick. If you do not need at least some data point texts on the Actual line, then you are good to go with the result you have now. For all other readers, I found a feasible workaround.
In order to add descriptions for data points on the Actual line, we will create a duplicate of our previously created chart. In the duplicate we change the chart type to a simple line chart and remove all key figures except the Actual an change the font color of the data points to black. After that, you can move the line chart above the combined chart, Make sure, the x-axis and it´s elements are aligned and appear as one.
In case you did it properly, looking at the x-axis you wont be able to see its two overlapping charts now. You may hover see, that Actual lines are not matching and have some offset. In order to fix this, we will utilize the uniform chart scaling feature of the SAC and apply it to our Actual FTE key figure. After that the lines from both charts will align and match.
For the final touch I changed the colors for the deviation calculations a bit to exactly match the colouring set by SAP in the standard variation charts. Additionally I changed the data point configuraton for the line chart to only show me texts on the start, end, high and low values to not overload the chart. A positive side effect of the workaround is, that you can hide the base bars of the combined chart by pulling the line chart to the foreground and pushing the combined chart to the background in the formatting layer concept. This prevents users from interacting with the base bars when hovering over the chart with the cursor. Additionally you can add the deviation calculation key figures and the plan to the quick info, so the users can still see the values when hovering ofer the overlapping line chart.
Thats it, you´re done!
Conclusion
Wrapping it all up, the implementation of a line chart with integrated variance is possible with some thoughtful report design and issues to overcome. However for the future it would be nice to see the following functionalities at the SAC:
- Be able to set the color of chart elements to transparent.
- Be able to set the font size and color individually for each key figure individually, at least for data points.
I hope you can benefit from that insight and hope, it enables you to provide a new chart layout to your dashboards using the SAC story mode. Thank you very much for reading.
In case you are interested in further exchange on SAC developments and it´s usage in Reporting, Analytics and Planning use cases, I am always interested in further exchange.
Kind regards
Eric Zeugner
Okumaya devam et...