SSRS Line chart NULL VALUE - Horizontal Line

Question

Horizontal Line enter image description here

I am hoping someone can help me. I have created an SSRS (2012) report plotting multiple series over time. The chart is shown above. Many of the non-connected points are easily 'joined up' by making the empty point value the same color and size as the line itself. I have demonstrated that with MC113. This is all fine - and I have found plenty of helpful information on the web to help me address this. The problem I am having is with the leading (and sometimes it is the lagging) horizontal line (circled). This is where the data, from that series, does not start or end at the same point as the rest of the data (the example above appears to start in 2009, where as the remainder of the data starts in 2005).

The data set is derived from a query and these points are being returned as NULL values. How can I remove this line - so that in this case - the line would start from 2009? I have tried filtering - but that will just truncate the all the series (to 2009) rather than just filtering MC113.

Does anyone have any suggestions? Perhaps I need to work on how present data from my query?

Currently this looks like:

Query_Results

Any help would be most appreciated!

Trubs Trubs · Accepted Answer · 2015-03-31T00:10:09

OK - here it gets ugly... I think you may need to do this in TSQL to find the lowest date where the value is not null for each series and if the date is before that, set the line color to white

The code below will create column to determine whether or not show the datapoint

    WITH x AS  (
                  Select SampleDate, MC113,MC114,MC46,MC47,MC48
                  From sometable
)
,MinDates AS (
SELECT MC113MinDate = (SELECT Min(SampleDate) FROM x WHERE MC113 IS NOT NULL)
      ,MC114MinDate = (SELECT Min(SampleDate) FROM x WHERE MC114 IS NOT NULL)
      ,MC46MinDate = (SELECT Min(SampleDate) FROM x WHERE MC46 IS NOT NULL)
      ,MC47MinDate = (SELECT Min(SampleDate) FROM x WHERE MC47 IS NOT NULL)
      ,MC48MinDate = (SELECT Min(SampleDate) FROM x WHERE MC48 IS NOT NULL)
      )
SELECT x.SampleDate
        ,MC113,ShowMC113 = CASE WHEN MC113 IS NULL THEN CASE WHEN MC113MinDate >= x.SampleDate THEN 0 ELSE 1 END ELSE 1 END
        ,MC114,ShowMC114 = CASE WHEN MC114 IS NULL THEN CASE WHEN MC114MinDate >= x.SampleDate THEN 0 ELSE 1 END ELSE 1 END
        ,MC46,ShowMC46 = CASE WHEN MC46 IS NULL THEN CASE WHEN MC46MinDate >= x.SampleDate THEN 0 ELSE 1 END ELSE 1 END
        ,MC47,ShowMC47 = CASE WHEN MC47 IS NULL THEN CASE WHEN MC47MinDate >= x.SampleDate THEN 0 ELSE 1 END ELSE 1 END
        ,MC48,ShowMC48 = CASE WHEN MC48 IS NULL THEN CASE WHEN MC48MinDate >= x.SampleDate THEN 0 ELSE 1 END ELSE 1 END
FROM x CROSS JOIN MinDates

Then set your Color and empty point property of series set the color as you've already done, dependent on the values ShowMC113,ShowMC114, etc

Note: there is some trickiness getting the legend to show your chosen color rather than white, but hopefully this will give you somewhere to go...

SSRS Line chart NULL VALUE - Horizontal Line

2 Answers