Draw a vertical line when two line charts meet - SSRS

reporting-services, ssrs-2008

Solution

The green line you want to place on the chart is called a StripLine. Getting them to display correctly can be pretty tricky. The first thing to setup is your data structure.

The dataset results can't look like this:

Time    Series    Value
12:00   A         20
12:00   B         30

They must look like this:

Time   SeriesAValue    SeriesBValue
12:00  20              30

The reason for this is that the StripLines will not display if you try to use Category Groups or Series Groups.

Next, you have to calculate the intercept point. In my example, I added another dataset to return the time of the intercept.

declare @test table(Time datetime, Value1 int, Value2 int)
INSERT INTO @test values('12:00', 32, 20), ('12:01', 30, 22), ('12:02', 24, 25), ('12:03', 28, 30), ('12:04', 29, 20)

SELECT MIN(Time)
FROM @test
WHERE Value1 <= Value2

To insert the StripLine, select the Horizontal Axis on your Chart. Click on the StripLines properties under the Appearance section. Click the Add button. In the Interval section you only want to change the IntervalOffset expression, leave everything else as Auto. The expression will look like this:

=DateDiff(DateInterval.Minute, Min(Fields!Time.Value, "DataSet1"), First(Fields!ID.Value, "DataSet2")) + 1

The interval must compute to an integer and the +1 is because it starts at 0. The result will look like this:

Since you have one of the lines being a set value, you may be able to simplify this even further.

Problem

Original source