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.
For illustrating the solution, I am using a simple dataset which shows the sales by Product and Month.
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.
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)
‘1’ AS Type,
CAST (LagSales AS FLOAT) AS Sales
‘2’ AS Type,
CAST (Sales AS FLOAT) AS Sales
‘3’ AS Type,
CAST (LeadSales AS FLOAT) AS Sales
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.
2) Repeat the steps 2 and 3 in previous article to make the matrix and the two rows above it.
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.
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.
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.
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.
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.
6) With all these changes and a bit of formatting, we can get the below result
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.
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 (just kidding)