A SSRS approach to display stacked column totals without additional coding. Lately I have often been asked if and how it is possible to display the totals of stacked columns within a chart in Microsoft Report Builder – without using cryptic MDX formulas or other hard-to-handle code snippets. Of course, it would be just lovely, if this could be achieved by simply performing drag and drop.
Now, imagine the following situation: You are in this situation and ask for an example – a specific case where this might be useful – and you are provided with the following example, the ONE report where it is ABSOLUTELY necessary, that a stacked column is used instead of separate charts per group member:
You ask if it wouldn’t help to know and see at least some of the data behind this chart. „Of course, we can provide this!“ you are told – and what is provided is the following example:
And – with this examples at your hands – you are asked to “simply” add the totals of the columns at the top of the chart. Now, after analysing the data the chart bases on, you will probably have noticed, that – beside the fact that the chart is absolutely unreadable – the data labels in the chart do not match with the total number of elements in each column. In other words: what you see is only an excerpt of all values available in each stacked column. You remember the initial request? Right: your stacked column should display the totals only! So, your first step would be to remove the data labels again. The result? Looks quite familiar, right?
It is the one you were initially provided with. However, since data labels don’t do the trick and it is explicitly requested to solve this task without the use of formulas, MDX and so on, you need another workaround, and here is how it goes: First of all, what you want to do is to create a copy of your chart and place it right above your original one. If applicable, you should remove the title of the lower chart – you only want to see the title once on your page, right? Your result might look something like this:
Now, it’s no good to see the same chart twice – after all, you want to see the totals per column, not for each group in each column. So in your next step you should remove the series group from the upper chart:
Additionally, what you absolutely want to ensure is that the data labels are displayed in the upper chart – this is what you are most interested in! Next, you should change the vertical axis to “displayed“ and modify its maximum value to a value way out of the range of expected values and its interval value to 1. In the following example, I chose max. value of 1.000.000.000 for an expected maximum value of 130.000:
Since the vertical axis wasn’t visible in the original chart, you might want to get rid of it again – which is the same you might want to do with your horizontal axis. Furthermore, you are not interested in displaying a second set of columns, so you might want to set the colour of the columns to „No Colour“ or „white“:
With this done, you are ready to minimize the height of your upper chart. After all, you did everything to hide all elements except the data values in this chart – which are the column totals of your bottom chart – from your upper chart, right? The result might have severe similarities to the following example:
After modifying the format of the data labels to a corresponding number format (and if applicable the title of the chart), you are ready to go: executing the report should display your stacked column chart topped with the required totals:
This workaround is applicable for stacked bar charts as well – except that here you will have to deal with the horizontal axis instead of the vertical axis. I hope this little workaround helps you in bypassing problems faced in the past, which could not be solved by Report Builders standard tool kit.
Kommentare (0)