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)

Posted by SQLJason


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

Nice work!


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

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.

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?

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?

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

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.

This document is showing “Column and Line Hybrid Charts” based on SSRS 2005. So, it may be possible that it will create in SSRS 2005 also.

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

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

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

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

Hi Guys,

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

Wandering Walt

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.

Supriya Khamesra

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.

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.

Karthick Surendran

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!

Supriya Khamesra

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

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

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

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

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

Janani Mohan

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

graph yang menarik
ini mudah dipahami
terima kasih infonya

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

Appreciate your response

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?

Guys, the line chart solution is here finally – 🙂

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

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.

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…

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.

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!

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

I added also a pic with my data:

Ok…I'll check again step no. 7 🙂 Thanks

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!

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.


Hmm, I have the line chart solution here – but you will need to use a combination of both of these to make it work for the combo chart. (for eg, since there are 3 points in the line chart solution for every point, you will have to write an expression for the bar to display only for the middle of the 3 points. But the line should display for all 3 points)

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.


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.

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.

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?

You missed step 7. This step will ensure that the heights of all the column charts are normalized.

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.


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.

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.

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

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

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 🙁

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?

I’m having the same issue with the Series color not reflected in the “Category” textbox in the Matrix. I’ve gone back thru your great instructions several times. The macro provided Step 13 works great in the Chart Series Fill properties but the same code in the TextBox Fill Properties only returns the first color in my “colorPalette” var in the custom code.

I was able to figure out the issue in that I inserted the new column to display the Color outside of the Group. I recreate column within the Group and it works fine. Thank you for your article.

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

Hi Jason,

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

Please help


Tai Tran.

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

Hello All,

I have downloaded rdl file, and deployed to report server, in reportserver report was absolutly fine, but when it comes to ReportviewerMVC, we tried to load the report in .NET UI using reproviewerMVC project, it is not showing properly, it is repeating as many times as matrix column, did body tried this using .NET reportviewer?

When i deployed to Reportserver, in report server report is showing properly, when it comes to .NET UI using reportviewerMVC it is not working as expected. Report bar graph is repeating as many times as the columns in Matrix . Did anybody facing same issue like me in .NET environment?

I want to paste the screen shot here. But it is not allowing me?

Any suggestions on this, i m in urgent requirement, I did some POC using this report file, in reporting tool side report is showing data but it is not working in .NET UI.

Great article and very helpful. How can you scale the entire chart to fit within the page. Mine is too small and I can’t seem to fit all on one page without cutting off and continuing onto the next page.

the only issue with this is that it doesn’t stay a consistent size. I can set a chart to 8″ wide and no matter how many category groups there are the chart will be 8″ wide. This is always going to be a different size when the data changes and this makes print layout a nightmare.

Hi, I was going to use this to try to stop the REPLICATING chart problem in SSRS. Has anyone figured out how to stop charts from replicating at the report sever level? It all looks good in SSRS preview, but once the report is deployed to the server, the charts replicate with no rhyme or reason.

I’m trying to follow these instructions but my data is a little different. In SQL Server, I created a pivot table:

CustomMonthDesc OP IP
01 (Jul) 932 529
02 (Aug) 1035 554
03 (Sep) 840 448
04 (Oct) 1140 602
05 (Nov) 972 517
06 (Dec) 1075 555
07 (Jan) 1027 530
08 (Feb) 524 256

In SSRS, I created a stack chart with IP and OP as Values and CustomMonthDesc as Category. That’s as far as I’ve gotten. (Unless you count my creating another pivot table, a transposition of the first, and displaying that below the stack chart.) I can’t modify your instructions to fit my case because I don’t have as many columns as you have. Will your solution work for me? Do I need to adjust the query that I’m using to create the Dataset(s)?

Any help will be greatly appreciated,

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.