SSRS Line Chart with Data Tables (Excel Style)

Power BI Tip: Making similar sized KPI Boxes / Charts
August 20, 2015
My Thoughts on Calculated Tables in Power BI
September 24, 2015
Show all

SSRS Line Chart with Data Tables (Excel Style)


It takes a lot of discipline and dedication to run a blog properly and that is one of the main reasons why I admire bloggers and tech gurus like Chris Webb (who has been putting out high quality blogs for ages in a consistent manner!). Sadly, I am not very disciplined when it comes to writing blogs and it takes a significant external force to make me write nowadays. I had written a blog almost 3 years ago on how to create SSRS charts with data tables like in Excel and from then onwards, I have had a lot of readers ask me on how to do the same with line charts (both through comments as well as emails). I knew it was possible but was too lazy to write a blog on it, until I had 2 readers ask me the same question yesterday in the comments. Finally, I decided to check it out and write about it. The solution is not perfect and is more of a workaround but should be ok for most of you I guess.

SSRS Line Chart with Data Table (Excel Style)

For illustrating the solution, I am using a simple dataset which shows the sales by Product and Month.

Sample dataset

To follow this solution, you must be familiar with the technique I mentioned in the previous article. If you have not read that, please the previous article first and then follow the steps below 1) A lot of readers already found out that if the technique described in the previous article was used, then we will only get points and not actual lines. So, the very first step here is to modify the source query such that for every actual point in the line chart, we get 2 more points which gives the start and end for that point. With this, now we will have a line joining 3 points for what would just have been one point before.

;WITH   Src
AS     (SELECT Product,
WHEN MonthNo = 12 THEN NULL ELSE (lead(Sales, 1, NULL) OVER (PARTITION BY Product ORDER BY MonthNo) + Sales) / 2
END AS LeadSales,
WHEN MonthNo = 1 THEN NULL ELSE (lag(Sales, 1, NULL) OVER (PARTITION BY Product ORDER BY MonthNo) + Sales) / 2
END AS LagSales
        FROM   (<Source Query>) AS O)
SELECT Product,
       ‘1’ AS Type,
       CAST (LagSales AS FLOAT) AS Sales
FROM   Src
SELECT Product,
       ‘2’ AS Type,
       CAST (Sales AS FLOAT) AS Sales
FROM   Src
SELECT Product,
       ‘3’ AS Type,
       CAST (LeadSales AS FLOAT) AS Sales
FROM   Src;

Note that LeadSales column is actually the (Sales for next point + Sales for Current Point) / 2 and LagSales column is actually the (Sales for previous point + Sales for Current Point) / 2. This will help us get a smooth line when we join our different lines. Also, we have to ensure that for the first and last points, NULL values are assigned. The bottom part of the query brings all three columns (Sales, LeadSales, LagSales) into a single column called Sales but each one is assigned a different Type.

Changed Dataset

2) Repeat the steps 2 and 3 in previous article to make the matrix and the two rows above it.

Matrix ssrs

Also add the Type column to the row group, delete the columns only and then filter the Type group for 2 only. The reason is that we only want the actual Sales to be shown in the data table, which is 2. Type 1 and 3 are used for the sole purpose of making the line chart.

1 Type filter

3) Now you should be able to follow the rest of the steps in the previous article with the sole exception that you will be using a line chart and not a bar chart.

2 Line Chart

Make sure that you set the CustomInnerPlotPosition and CustomPosition appropriately like in step 10 in the previous article, so that graph appears continuous. I used the below settings for this line chart.

SSRS CustomPosition

4) Instead of step 11 in the previous article, I chose to make a new column to the left for the vertical axis, and just made sure that the vertical axis for the line charts all have the same scale. Adding column for axis

Note that the series expression for this column is just 0, and there are no category or series group. This ensures that we just get a dummy line for the axis. You can start hiding the orders to ensure that the graph looks continuous.

5) I also added an expression such that the markers and tooltips only show if the type is 2.

SSRS adding expression for markers and Tooltip

6) With all these changes and a bit of formatting, we can get the below result

SSRS data table with line chart

This should be good for most people. However, there is one minor drawback which is that the lines do not join that smoothly. I have just zoomed in one part so that you can see the issue. Maybe, this could be solved by fiddling along with the properties some more, but I feel this is not that big of an issue.

Line chart lines are not smooth

Hopefully this will put to rest some of the questions I keep getting on data tables in SSRS, so that I can go back to my lazy self Smile (just kidding)


  1. Gracy says:

    Thanks for the great article. I have followed the steps. When I inserted the chart in the matrix, it shows individual grouping based on category and shows single point for every group. Could you help me how to join indidual points ,remove the matrix grouping and to show like the line chart with markers. Thanks in advance

    • SQL_Jason says:

      It looks like you have not followed the first step correctly. The first step should create 2 extra points for every category, which is what helps in creating the line. Can you revisit that and check?

  2. David Kline says:

    Hi Jason,
    Thank you for the article – most helpful, exactly what I was looking for. Unfortunately, I ran into a problem – the line is not connecting between matrix cells. I do have 3 points in each category, and the line is there, but not connecting. Looks like there is a margin, or a padding that I cannot get rid of. I checked the CustomInnerPlotPosition and CustomPosition, and they are set to 1,1,100,100. Any ideas? Thank you again!

    • SQL_Jason says:

      For other readers, make sure that the side margins for the horizontal axis are disabled. Else there would be space between the margins. Found this while resolving problem for David Kline

  3. SQL_Jason says:

    Hmmm, apart from the plot positions, the only other thing that could affect is the border of the cells, are you sure you set the border style to none? And is there any way you can attach an image so that I can see what's happening? You can email me at jason143 at gmail dot com too

  4. Jose says:

    Hi, Could you give me please the copy of this report. I'm having an Issue with the plots. Thanks.

  5. […] 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) – […]

Leave a Reply

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