ssrs chart lines not connecting

Question

I have an SSRS Line chart which plots supply points with square feet on the X axis and Price on the Y axis. Right now I don't really care about making it pretty just getting the lines to show up correctly. I am plotting the points and grouping by Subdivision/Builder.

So for example Subdivision A has builders Y and Z. I want to show different colors and lines for Subdivision A builder Y verses Subdivision A Builder Z.

The problem is that the lines are not connecting when a point for another subdivision builder combination breaks up that line.

The grey line and points below are not all connected as the yellow point is between the grey points so the grey line is not connected to all grey points.

How can I make the points of the same color (same Subdivision/Builder) connected via a line?

Answer 1

As I found out the hard way recently, this problem is often caused by null values in the data not being properly handled by SSRS. Without seeing your data, I can't be certain that's the cause, but nulls were the culprit I encountered the same behavior.

The solutions usually involve assigning values to the color of the EmptyPoint property on the Series, sometimes in conjunction with setting the EmptyPointValue to specify null handling. I've found many references to this problem on the web, but I'll only post links to the best two, both of which are on StackExchange:

1. The thread SSRS Line Chart NULL VALUE - Horizontal Line contains a thorough discussion of this issue. The usual workaround given is to hard-code a color expression for each line using an IIf, but sometimes this isn't an option, especially if the field you're grouping on has dynamic, unpredictable values, as my dataset did.

The picture posted there depicts clear examples of the same type of line breaks. The user named trubs posted a code sample which illustrates how to set the EmptyPoint, in case where an Iif will work:

=iif(isNothing(Fields!SelectedValue.Value),'No Color',"LightBlue")

2. The first reply in SSRS Line Chart Not Connecting Data Points details a workaround for cases when the EmptyPoint value & nulls are the root cause and simple hard-coded IIfs won't do the trick. Although I have yet to get my line colors to match the point markers the way I'd like, I can verify that this solution at least gives you your lines back and allows you to assign a variety of colors to them. It's fairly simple and involves merely pasting in some VB code for a couple color properties.

I was asked in the comments section to provide the details of the solutions, but don't want to plagiarize, so I'll simply do a long direct quote of JohnBob's answer:

Firstly, in order to get the lines to join up, you need to set the EmptyPoint colour for the series.

Select your series in your chart In the properties tab (not the dialog) drill down into the EmptyPoint property and set the colour to be Black

This will get them joining up - yay! But part of the line is colour and the other part is black, right? That's a bit silly, especially considering if you leave the colour to Automatic on the EmptyPoint that it will be transparent.

So, then we need to get the series and the EmptyPoint's colours in sync. Using code from here. I added some code to the code of the report.

1). Right click on an empty space on the report and select "Report Properties" 2). In the code tab, paste the following:

Private colorPalette As String() = {"#418CF0", "#FCB441", "#E0400A", "#05642E", "#1A3B69", "#BFBFBF", "#E0400A", "#FCB441", "DarkBlue", "Tomato", "Orange", "CornflowerBlue", "Gold", "Red", "Green", "LightBlue", "Lime", "Maroon", "LightSteelBlue", "Tan", "Silver"}
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

    Public Function GetColor(ByVal groupingValue As String) As String

        If mapping.ContainsKey(groupingValue) Then
                Return mapping(groupingValue)
        End If

        Dim c As String = colorPalette(count Mod colorPalette.Length)
            count = count + 1
            mapping.Add(groupingValue, c)
            Return c
    End Function

Then we need to call this code when setting the colour of the series and of the EmptyPoint.

Select your series
In the properties tab paste something the following (replace WhateverTheGroupIsForYourSeries with your series group name):

=Code.GetColor(Fields!*WhateverTheGroupIsForYourSeries*.Value) Drill down to the color element of the EmptyPoint Series property Paste the same text as from point two [e.g. =Code.GetColor(Fields!*WhateverTheGroupIsForYourSeries*.Value)]

And voila! You're done! I can't believe how unnecessarily difficult this is :D

I hope this helps.