SSRS line chart not connecting data points

Question

I've looked high and low and can't seem to find an answer to what appears to be quite a straightforward issues (I would think).

I have a line chart where there is data at several points in a series but only only one set of points link up.

Does anyone know why this is? Is it to do with my data? If it is, I am struggling to see any relationships in the data that may explain this behavior.

Here is what I mean:

As you can see, the red diamonds should be connecting - the same could be said about the blue squares and the dark blue triangles. Any thoughts?

Apologies if my colors are wrong - I'm colorblind.

Answer 1

I just had a problem where it was showing markers but not the lines. I ended up solving it by wrapping the expression in a CInt() to convert it to an integer.

Answer 2

Okay, so I worked this out.

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

1. select your series in your chart
2. 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.

1. Select your series
2. In the properties tab paste something the following (replace WhateverTheGroupIsForYourSeries with your series group name): =Code.GetColor(Fields!*WhateverTheGroupIsForYourSeries*.Value)
3. Drill down to the color element of the EmptyPoint Series property
4. 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

Answer 3

Another way to handle this is to do a recursive select (if you have very large data sets don't do this, it kills performance). So you could make your dataset:

SELECT *, ROW_NUMBER() over(partition by Store order by Date desc) as rn
FROM StorePerformance

Now you can have your category group be rn (it should group by rn and sort by rn) but select date for its label. This will still display the date on your x axis, but since you are grouping by rn the data will still be continuous and all of the lines will connect. Also your legend will still match.

I still haven't looked deeply into how much this shifts the data points since we are plotting with row number and displaying date, but if you are mainly concerned with visualizing trends in the lines and aren't as concerned with where they are at a given point in time in relation to one another, it will serve you just fine. Otherwise I would leave the chart the way it is and just read the plot points, which are still valid data.