Monday, October 29, 2012

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 wont 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. I've always considered this kind of chart impossible in SSRS, thanks a lot for proving me wrong Jason :)

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

    1. 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.

  3. 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?

    1. Hi, Nice post! Thanks!
      I try to copy this but the layout is not perfect. The column in the chart has different width, and the column is not centred over the "month". Any suggestions?

    2. This is great. Can it be done with SSRS 2005? thanks.

    3. Jonas,
      Cut the chart from the tablix and paste it outside, and then try previewing the report. If the chart looks ok, cut and paste it back to the tablix cell and you should be fine. Else forward it to my mail at so I can have a quick look.

      And I dont have SSRS 2005 with me to check this, but I would think that it is difficult if not impossible to follow the same approach in SSRS 2005, as we dont have many of these options.

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

    1. Hmm, great question. Can't think of a solution right now, but will keep thinking :) Thanks for asking!

    2. I am looking for a solution for a line chart as well. Any ideas?

    3. nice article. Have you been able to find the solution for line char?

    4. This comment has been removed by the author.

    5. Hi Guys,

      Any solution in line chart for connecting lines across the months? Thanks a lot

  5. 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.

    1. 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.

  6. 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!

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

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

  9. 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........

  10. this is nice but I wish there was one with the line chart... :(

  11. 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!!

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

  13. graph yang menarik
    ini mudah dipahami
    terima kasih infonya

  14. This comment has been removed by the author.

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

    Appreciate your response

  16. 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. Guys, the line chart solution is here finally - :)

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

    1. 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.

    2. 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...

    3. 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.

  19. This comment has been removed by the author.

  20. 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!

    1. Step number 7 is key for that, Make sure the same scale is used in the axis.

    2. I added also a pic with my data:

    3. Ok...I'll check again step no. 7 :) Thanks

    4. Thank you very much for your quick reply Jason. Indeed the issue was on number 7:) I needed also a SUM in that MAX.

      Thank you!