Pages

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

=left(Fields!Month.Value,3)

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

=Code.GetColor(Fields!Category.Value)

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

19 comments:

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

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

    ReplyDelete
    Replies
    1. Can you give me a few more details or just send the rdl and a picture of how the result looks at jason143@gmail.com?
      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.

      Delete
  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?

    ReplyDelete
    Replies
    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?

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

      Delete
    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 jason143@gmail.com 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.

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

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

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

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

    ReplyDelete
    Replies
    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.

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

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

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

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

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

    ReplyDelete