Chart behavior difference in SQL Reporting Services 2008

There are some changes in behavior in Reporting Services Charts with regards to “Empty Points” between SQL 2005 and SQL 2008  (

Let us consider the following Dataset:

create table #TestChart


  Runtime datetime,

  SensorReading int,

  SensorName varchar(20)


insert into #TestChart values('2009-01-30 1:30:00',165,'SENSOR_A')

insert into #TestChart values('2009-01-30 1:31:00',145,'SENSOR_B')

insert into #TestChart values('2009-01-30 5:30:00',135,'SENSOR_A')

insert into #TestChart values('2009-01-30 5:31:00',185,'SENSOR_B')

insert into #TestChart values('2009-01-30 9:30:00',165,'SENSOR_A')

insert into #TestChart values('2009-01-30 9:31:00',115,'SENSOR_B')

insert into #TestChart values('2009-01-30 13:30:00',115,'SENSOR_A')

insert into #TestChart values('2009-01-30 13:31:00',125,'SENSOR_B')

insert into #TestChart values('2009-01-30 17:30:00',155,'SENSOR_A')

insert into #TestChart values('2009-01-30 17:31:00',165,'SENSOR_B')

insert into #TestChart values('2009-01-30 21:30:00',145,'SENSOR_A')

insert into #TestChart values('2009-01-31 1:30:00.130',135,'SENSOR_A')

select * from #TestChart

If we had a Chart in SQL 2005  based on this data it would look like the image below:

SQL 2005 Chart


This was incorrect behavior. To give an analogy, let’s say that you only have data for Mon, Tues, Fri, on one instrument, and you have data for Wed and Thursday on the second instrument.  If you try to plot the days along the X axis,  due to the fact that the chart is going to show 5 days worth of data, each instrument now effectively has 5 days worth of data that could be shown.  The chart builds its own internal data structure to support that view.  If there isn’t data present for a particular point , it is considered as an EmptyPoint.  You simply shouldn’t be drawing a line that goes from Tuesday to Friday for the first instrument because it doesn’t have any data for Wed or Thursday.  For a line chart, a line will be drawn only across 2 or more contiguous points.

For the Dataset above,

Intersection calculated internally:

currentdttm ( X-axis)

1/30/09 1:30 AM

1/30/09 1:31 AM

1/30/09 5:30 AM

1/30/09 5:31 AM

1/30/09 9:30 PM

1/31/09 1:30 AM
















Now the chart cannot plot a line without 2 contiguous values, and the only 2 contiguous values are the ones highlighted above for the same series.

Attached is an RDL that demonstrates my point. With the dataset above the chart would appear as below given that there were only 2 contiguous points it could plot.

SQL 2008 Chart


While it is a different behavior from 2005, the 2005 behavior is the incorrect behavior.  If you look at Excel, Excel will plot the chart the same way as SQL 2008.  SQL 2005 had quite a different behavior as it connected every point for the Series group irrespective if they were contiguous or not. SQL 2008 does expose properties to control what to do on an EmptyPoint which is a property of the Series. If we want to revert to the 2005  behavior, we would have to manipulate the properties of the EmptyPoint

In order to have the behavior that existed in SQL 2005,  you have to change the Color property below with an expression below which basically is assigning the color based on the SeriesGroup.

=IIF(Fields!SensorName.Value="SENSOR_B", "#FCB441","#418CF0")

Also change all the other properties of the EmptyPoint to match those of the series.

By default, empty points are calculated by taking the average of the previous and next data points that contain a value. You can change this so that all empty points are inserted at zero

In the Properties pane, expand the CustomAttributes node.

Set the EmptyPointValue property. To insert empty points at an average of the previous and next data points, select Average. To insert empty points at zero, select Zero.


Resulting Chart will look like SQL 2005 Chart if that is necessary.


-Denzil Ribeiro, Sr. SQL  Dedicated Premier Field Engineer

Comments (6)
  1. Andy Frieders says:

    Thank you very much for this article which helped me to overcome this (to me it seems odd) behavior.  This is the best (of the very few that I found) articles on this subject.  This type of time series chart (multiple series with independent category axis values) is by far the most important to me in my industry (chemical engineering).  I do not wish to insert empty points into each of my series data based on the overall set of category values from all series together.  It is unfortunate that there isn't a more appropriate chart type for this data and I have to coerce the line type to do what I need it to do.  I believe that it is called for.

  2. Melvin says:

    Completely agree, this article is gold! Thanks Denzil!  However I am not sure how to bring through a colour palette when enabling empty point averages.  Auto colour doesnt seem to work, so I end up having to use one colour for all my emptypoints on different curves.  Any ideas?

  3. rwaithaka says:

    Go to Report->Properties->Code and add the following

    Public Shared Function SetColor(ByVal sCondition As String) As String

    SetColor= "Transparent"

    If sRegion = "Condition1" Then

    SetColor= "CornflowerBlue"

    ElseIf sRegion = "Condition2" Then

    SetColor= "IndianRed"

    ElseIf sRegion = "Condition3" Then

    SetColor= "Orange"

    End IF

    End Function

    Then proceed and pick the series and go to its properties as follows

    Empty point -> Color

    Set the following expression:


    Ensure coloring scheme for graphs is uniform also by using the above funciton


  4. This is Fantastic! I've searched the web for a solution and landed here by accident. Thank you for posting this article Denzil Ribeiro.

  5. BIDS IDE says:

    Thanks for the post. Setting the EmptyPoint to Average is making the series to extend backward or forward if all of the series does NOT have the same range of category values(…/ssrs-2008-r2-line-chart-issue)

  6. This article saved me. If you select a continuous chart its supposed to join two points no matter if there are empty data points. I was able to use your logic and generate a smooth line. Thank you for your detailed explanation.

Comments are closed.

Skip to main content