SSRS Charts with Data Tables (Excel Style)

Difference from Maximum of Running Totals in PowerPivot
October 18, 2012
SQL Saturday Charlotte Journal
November 6, 2012
Show all

SSRS Charts with Data Tables (Excel Style)

Ok, this post was supposed to come out a lot earlier but I was just too lazy. Complacency is a struggle we all have to fight and I would be lying if I say that I am immune to all these. This post is a direct result of a few forum posts where people said that charts with data tables are not possible in SSRS (unless you add a tablix beneath the chart and spend umpteen hours trying to align both of them!). Working with SSRS is like making love to a woman – you need to know the woman and what makes her click well for a fabulous time. Well, you can still manage without knowing her well enough, just that it won’t be that awesome! Winking smile

1 Charts with Excel style data tables

For people who are familiar with excel charts, adding a data table to a chart is a one-click affair. It is nice to see the data table just beneath the chart and it also serves as a legend. A simple example is shown below:-

2 Excel chart with data table

In SSRS, having a tablix beneath the chart serves as an ok workaround as long as the number of fields in the category is fixed. When the number of fields change change (lets say, when the value of a report parameter changes), the alignment goes for a toss. Here, I will be demonstrating a much more elegant way to add the data tables than trying to align the tablix and chart. 1) Create a simple dataset from the AdventureWorks cube, displaying the Month, Category and Internet Sales Amount. Add a parameter for Calendar year with a default value of CY 2007.

3 Dataset query

2) Drag and drop a matrix to the designer pane and then add the Category field on the row, Month on the column and Internet_Sales_Amount on the data as shown below

4 SSRS Matrix with fields

3) Add two rows outside group above the category row as shown below.

5 Add two rows outside group for matrix

4) Now you can delete the top row (but not the associated group) containing the Category and the Month Header as shown below

6 Delete header rows

5) Once that is done, drag and drop the month field to the textbox above the data value. Here, I just want to display the first three letters of the month (instead of having the month followed by year), so I am using the expression below directly:-


You might also want to do some quick formatting like hiding the textbox borders for the top row.

7 Hiding textbox borders and setting month expression

6) Once this is done, the only part remaining is to make a chart, format it and then place it on the top right textbox. For that, drag and drop a stacked column chart outside of the matrix, and then drag Internet_Sales_amount on the Values, Month on the Category Groups and Category on the Series Groups as shown below.

8 Chart

7) Right click on the vertical axis, select the Vertical Axis Properties and then modify the expression for Maximum from Auto to the expression written below

=1.2 * max(Fields!Internet_Sales_Amount.Value, “DST_Main”)

9 Chart vertical axis max property

8) Delete the axis titles, chart titles and the horizontal axis from the chart

10 Delete Axis titles

9) Hide the labels for the vertical axis.

11 Hide Labels

10) We need to place this chart within the tablix and make it repeat for each of the month. But before that, we need to make sure that there are no white spaces within the chart area, and hence change the following properties for the Chart Area.

12 Custom Inner Plot Position

Also set the BorderStyle property for the Chart as None and disable the MajorTickMarks for the Vertical Axis. This will also help in making the charts look continuous.

11) Now as a last step, we need to ensure that the vertical axis is only displayed for the first chart, Hence, set the LineStyle property for the vertical axis as shown below =iif(Fields!Month.Value=First(Fields!Month.Value, “DST_Main”),”Solid”,”None”)

13 Vertical axis property

12) Drag and drop the chart into the tablix cell

14 Chart in tablix

13) To add a color as a column next to the Category, we can use the Custom Code technique for consistent colors described here. Then ensure that the BackgroundColor property for the column as well as the series color for the chart is the expression below


15 Color of column as well as series

14) Now preview the report and you will get the intended result. Change the parameter value from 2007 to 2008, and you will still see that the alignment is proper (even though the number of bars have changed)

Charts with data tables

You can download the completed rdl file from here. Now you know which post to point to incase someone asks on how to create charts with data tables in SSRS Smile


Update : 10 September /2015

For those looking to replicate this for the line charts, I have written a new post on #SSRS Line Chart with Data Tables (Excel Style) –

SSRS Line Chart with Data Tables (Excel Style)


  1. says:

    I've always considered this kind of chart impossible in SSRS, thanks a lot for proving me wrong Jason 🙂

  2. Anonymous says:

    Nice work!

  3. Anonymous says:

    I tried , but chart not clear . its like blurring. pls help me to solve .

    • SQL_Jason says:

      Can you give me a few more details or just send the rdl and a picture of how the result looks at
      Also, did you try downloading the rdl file I developed and checked if that works? If yes, maybe you could compare the differences between yours and mine.

  4. Jonas says:

    Hej, Nice inlägg! Tack!
    Jag försöker kopiera detta men layouten är inte perfekt. Kolumnen i diagrammet har olika bredd, och kolonnen är inte centrerad över "månad". Några förslag?

  5. hankbon says:

    This is awesome! Do you know of a way to do the same thing with a line chart?

  6. Any way to show the vertical axis for the first bar chart in the series? Thank you for this post, it helped out a lot.

    • Yes,we can show vertical axis for the first bar.I tried and was able to do that by adjusting the Customer Inner Ploat and custom position of chart area property. Mainly you have to do adjustment for left property.

      • Mubasshar says:

        Hi ,

        I am trying to show vertical axes, but its showing for all the columns, instead of this i want to show only in first column, any workaround.

  7. Great article! I thought this was impossible when I delivered the project to a client just couple of weeks before your article. As you mentioned in your profile, you really did an impossible thing. Hats off!

  8. Great Article ! Has helped me a lot in showing the tablix data beneath the graph.

  9. Luis Morales says:

    Actually one of the most useful article I've ever found! Great step-by-step documentation.
    Thank you very very much Jason

  10. Alastair3755 says:

    very useful article but I suspect I have missed something out as, while my colums reflect the data the size of the columns do not reflect the actual totals.

    i have 4 possible values in my series group and the columns are a count of incidents in each category for each day of the month

    i have one column with a total of 39 which is smaller than a column with 6 incidents for the day……..

  11. Banastal says:

    this is nice but I wish there was one with the line chart… 🙁

  12. hgjhj hfghf says:

    Nice article!!
    m trying to create the same kinda graph but my matrix is shows all series of same size.
    Not able to find the cause. Data labels shows correct values but the height of the series are same.

    Thanks in advance!!

  13. Janani Mohan says:

    Hi I am trying the same for a horizontal chart… but chart is not clear … please help

  14. jeng hwas says:

    graph yang menarik
    ini mudah dipahami
    terima kasih infonya

  15. Kris Maly says:

    Can you tell me how to draw a Polynomial Trend Line in SSRS like Polynomial in Excel?

    Appreciate your response

  16. Kris Maly says:

    Nice article.
    Can you guide me how to do the same for line chart?
    When I apply the same to Line chart it shows points but no line between points?

    Any ideays/help?

  17. JJ says:

    This is excellent! Have you also thought about how we can display the Vertical Axis on the left side of the chart?

    • SQL_Jason says:

      Sure, you can just add a column before the first chart, put an empty chart there (just put expression for value = 0), and then adjust the scale of the axis with the scale of the axis in the charts.
      Now since this axis is in it's own column, you can just format this in whatever way you want. This technique is what I used for the recent article on Line chart with data tables (link at the end of the article in Updates) – step 4.

    • Nikis.swap says:

      Hi Jason,
      But how the scale of the axis from the column will know the correct scale axis? The maximum value from here will be applied for all orders for all periods…will be much higher that the one we need…and if add the column in the group than we will get the axis repeating itself…

    • SQL_Jason says:

      We can use the scope arguments in the max() to get the appropriate maximum values. Worst case, you can always pass it from your dataset, though I honestly believe that the argument in max() should take care of it.

  18. Nikis.swap says:

    Hi Jason,
    Thank you for your post.
    I have a question: Can you please tell me if you made also some changes for the gridlines?
    I tried your example with my data but the gridlines are different for some columns…
    how did you manage to have the same gridlines in January and December?

    Thank you!

  19. Suman Aadi says:

    Hi Jason,
    Awesome article.
    I have a similar scenario but slightly different, Instead of same bar chart I would like to display a bar and Line chart.
    It would really appreciate if you could help on this.


  20. Kiddo says:

    Hi Jason,

    Your solution works however having one issue if the graph has a negative value. The value is not presented in the graph and at the same time it causes dis-alignment in grid line.

    Graph without negative value. Looks great!
    Graph with negative value.

    Appreciate to your further help on this.


    • SQLJason says:

      This should be an easy fix. Make sure that you use an expression in step 7 for the Min field also. This will ensure all the charts start at 1.2 * min value.

      • Kiddo says:

        Thanks to your reply Jason. I have done that and the problem now is that the axis does not cross at 0. See results –

        I have tried changing the “Cross at” property but doesn’t worked. Can you further advise?

        Thanks again.

  21. Lucki says:

    Hi Jason,

    Have you done this using a column chart? I tried and my chart does not align itself according to the vertical axis values. The charts in each month aligns according to its own max and min value. So the heights are wrong compared to the vertical axis.

    Any idea on this?

  22. krish says:

    Hi Jason,
    I would like to have the bar as well as line chart in a single matrix in Excel way. would it be possible?If so, Please share the steps.


    • SQLJason says:

      You can, but it is a little more difficult. I have replied on how to do it to Suman Aadi (3-4 comments above yours), hopefully you can take it forward.

  23. MIke says:

    Great Post! I got it to work but when I try to use a running value expression for series data it just sums the values for each month instead of running total for each month.

  24. MIke says:

    Is there a way to display running total on horizontal axis using your approach.

    • SQLJason says:

      A workaround would be to make the running total measure in the dataset. Then you can just use the measure directly

      • twister says:

        First of all tks for your post 🙂
        I have problems 🙁
        My chart isn’t continuos, the size hight the bars is the same for all months 🙁

        • twister says:

          Please desconsider my last comments.

          My problem its other kkk

          1- I have problem in put color with function Code.GetColor in my textbox isn’t showing the colors
          2- I am trying create the chart combinate with bars and line, but the lines isn’t showing in my chart, just bars is show, do you know if its possible create this chart? With Bars and Line?

  25. lenncads says:

    hi jason, how come my column and line chart dont align with the table?
    please enlighten me

  26. Tai Tran says:

    Hi Jason,

    I try to show the first Vertical label but the charts shape and size does not look consistent.

    Please help


    Tai Tran.

  27. seksitreffit says:

    So now that we have this table with several different chart types, we are going to do some conditional hiding on our rows.

Leave a Reply

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