Display Total on top of Stacked Chart

Collation conflict error
June 1, 2010
Calculating visit length by first and last visit time in SSRS
June 7, 2010
Show all

Display Total on top of Stacked Chart


It is a normal phenomenon that human beings take most of the simple things for granted. Take this case, one of my blog readers sent me a mail asking how to display the total on top of a stacked chart in SSRS. The first time I read it, I was almost going to blurt – just enable some property to display subtotals. But the more I thought about it, the more elusive the property became and that is when I realized there is no such property. So for the benefit of all, I thought about posting the workaround that I devised for this. Suppose I have a simple stacked column chart with Month on Categories, Product Category on Series and Sales as the measure. The requirement is to display the total of all Product Categories for a month on top of the stacked column chart. The required output is shown in the below image.

Stacked Column Chart - Total

For this, follow the steps below:-

1) The original query was

SELECT  Month,
FROM    Sales
Modify it as follows SELECT   Month,
FROM     Sales
SELECT   Month,
             ‘Total’ AS Category,
             0.1 * SUM(Sales) AS Sales
FROM     Sales

This is done to include an extra row called Total for Product Categories. The value for Sales for this row would be 10% of the total Sales of that month.

2) Go to the Design tab, right click on the chart and enable the Show Data Labels option. Then select the series label properties by right clicking on the data labels.

Show Data Labels

3) Edit the Label data and enter the following expression


Label Data Expression

The above expression displays blank if the Product Category is not “Total” and displays the sum of the entire Product Categories for that month (including the value for Total) – sum of the Total field. P.S. : “Chart1_CategoryGroup” would have to be replaced with the category group name of your stacked column chart. To find this, click on the Month (category) button in the chart and press F4. You will find the Name of the group in the property panel that opens up as shown below.

Find category group name

4) Now we should be getting the following output when we click on the Preview tab.

Statcked column chart with Total

Go back to the Design tab and click on Product Category (series) in the chart and press F4 to open the property panel. Enter the below expression in the Label property =iif(Fields!Category.Value=”Total”,” “,Fields!Category.Value)

Edit Label expression for series

This is done so that the Total value will not appear in the legend. 5) Right click on the stacked columns and select series properties.

Series properties

6) Go to the Fill tab and enter the following expression for the color


Color expression

This is done so that the Total value will be transparent and hence, would look like it is not present.

7) Preview the report and you should be getting the required output

Final result

This report has been done with SSRS 2008 R2 but the concept should work in SSRS 2008 more or less the same way.


For SSRS 2008 users, it has been noticed that all the bars become black when the above fill expression is used. That is because the Automatic colour can not be used in expressions. So as a workaround, follow the step below instead of the 6th step above

1) In BIDS, press Alt+R to open the Report Tab and select Report Properties. Then go to the code section and paste the following code

Private colorPalette As String() = {“Green”, “Blue”, “Red”, “Orange”, “Aqua”, “Teal”, “Gold”, “RoyalBlue”, “#A59D93”, “#B8341B”, “#352F26”, “#F1E7D6”, “#E16C56”, “#CFBA9B”}
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()
Public Function GetColor(ByVal groupingValue As String) As String
  If mapping.ContainsKey(groupingValue) Then
    Return mapping(groupingValue)
  End If
  Dim c As String = colorPalette(count Mod colorPalette.Length)
  count = count + 1
  mapping.Add(groupingValue, c)
  Return c
End Function

Rep properties

Right now, there are only 14 colours in the palette but more colours can be added if you have more than 14 values in the series.

2) Use the following expression for colour in the fill tab of series properties.

=iif(Fields!Category.Value=”Total”, “Transparent”,Code.GetColor(Fields!Category.Value))

This should do the trick 🙂


  1. Steve Kossaris says:

    Jason Thomas this is a blog that is worth plenty! I would like to thank you for this blog that has instructed me on how to implement a total at the top of each stack in a column stack chart. You are a life saver! I hope that the Development Team at Microsoft can read your blog so that they can see how creative you have to be to be able to place a total at the top of each stack, this would be a common requirement in most companies that wish to create column stack type reports and I hope that Microsoft will make this option easier to implement in future releases of Reporting Services.

    Once again, thank you very much Jason, a blog well done! I look forward to future blogs that you develop.
    Steve Kossaris

  2. Priyanka says:

    Hey your blog helped me a lot. However, I would like to display values of each stack on top of the bar and then total of the bar totally above all the values. Do you have any suggestions?

  3. Jason Thomas says:

    Hi Priyanka,
    Do you have an image which shows how you would like to see the chart?

  4. Priyanka says:

    yes I do..
    But how can I attach the screen here?

  5. Priyanka says:


    I have uploaded on mega upload. Could you please download from there and see?

  6. Jason Thomas says:

    @Priyanka : We can't make it exactly as you said (not possible to get the labels in a coloured textbox) but I have mailed you a possible workaround. Let me know if that would do.

  7. Scott says:

    I would also be interested in how you were able to get this to work.

  8. Jason Thomas says:

    @Scott : Could you be more specific?

  9. Is it also possible to work around with the Fetch XML ,to show the Total Counts on a Field in Stacked Bar chart.

    • SQL_Jason says:

      I am not really sure what you meant by that, but you can get the Total Counts in your dataset query and then display them using the same technique in a Stacked Bar chart

  10. Prof Hu says:

    I think I have a bit simper solution for the same thing. (Figured out this afternoon after finding yours.) It doesn't require you to change the query which makes it more convenient and also you need less properties to be replaced with expressions and the expressions themselves are less complex.

    Steps of my solution:
    1. Add a new series to the chart with the expression: =sum(Fields!Sales.Value,"Chart1_CategoryGroup")
    2. Change the color of the new series to transparent. (Or you may want to change the series chart type to line and set a nice color so you will even have a line connecting the top of your stacked bars.)
    3. Hide the legend of the new series. (You may have to change the legend of the original series to an explicit expression of your category field as well if it is displayed.)
    4. Check "Show data labels" of the new series marked.
    5. Change the position of the labels to top.
    6. Change the "Visible" property of the labels to the expression:
    =iif(Fields!Month.Value=Max(Fields!Month.Value,"YourSales_DataSet"), True, False). Where "YourSales_DataSet" is the name of the actual dataset for the chart. This ensures that only one instance of the labels is shown at the end. Otherwise you would have the same number all around the top as many times as many categories you have. 🙂

    That's it.

  11. asritha says:

    Hi Jason, I have stacked bar chart. I want to display percenatges in x axis. I have calculated to display the percenatges to be shown in data lables. This is the formula I have used.in Chart series Lables => =CInt(Count(Fields!Status.Value)) / CInt(Count(Fields!Section.Value,"Chart3_CategoryGroup")).
    But I have issue with x axis formatting. The bars are not in sync with the x axis values. How do i format the x axis and bars so that the values are in sync.

  12. Linda Shiny says:

    Hi Jason! thanks a ton for posting. It works like a charm. Have a couple of queries though while following Prof. Hu's solution which you mentioned that you also use in advanced lessons
    1) the solution handles the visibility problem of the series label (i.e each group in the series group will have the respective total value displayed. therefore if there are 3 groups, 3 times the total value will be displayed) effectively by displaying only the max of series group for a particular category. therefore only the max value is shown. In my case there are 3 groups in the series group and for a particular category group there is value only for one of the groups. At that instance this max concept is not working. It still displays the total value 3 times. Please let me know what I should do here.
    2) it would be great if I could know how to customise the labels as it displays both the series name as well as the total column name.

    Thanks in advance!

  13. SQL_Jason says:

    You can customize the labels by going to the legend properties and using the custom text (where you can say to only display the field name you want instead of both). As for the first problem, you might have to use some expressions to achieve the same, I can help you further if you send me an rdl to jason143@gmail.com with a simulated example.

  14. Renu says:

    Thank you for your article. The total is not displaying at the top of stacked column. it displays at the center for that particular stacked total graph. Will it possible to include indicator like triangle pointing upwards and show the total above that triangle. Could you please guide me with that?

    • SQLJason says:

      If you followed this article, the total should appear at the top of the stacked bar chart. Can you check and let me know at what step it is failing?

      As for including the indicators, I think the approach should be to include a line chart (which is transparent) above the stacked bar and put a triangle markers and data labels. This will make it look like you have the triangle as well as the labels

Leave a Reply

Your email address will not be published. Required fields are marked *