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  (https://msdn.microsoft.com/en-us/library/dd207051.aspx)

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:

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

• Anonymous
  May 27, 2010
  The comment has been removed

• Anonymous
  November 29, 2010
  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?

• Anonymous
  March 07, 2012
  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: =Code.SetColor(Fields!Fieldname.Value) Ensure coloring scheme for graphs is uniform also by using the above funciton =Code.SetColor(Fields!Fieldname.Value)

• Anonymous
  May 16, 2012
  This is Fantastic! I've searched the web for a solution and landed here by accident. Thank you for posting this article Denzil Ribeiro.

• Anonymous
  June 19, 2013
  The comment has been removed

• Anonymous
  August 02, 2016
  The comment has been removed