How to handle empty values in a line chart in SSRS

By:   |   Comments (11)   |   Related: 1 | 2 | 3 | 4 | 5 | > Reporting Services Charts


Problem

When continuous data is displayed on a chart, such as a line chart, data is displayed very smoothly. But when non-continuous data is displayed on such a chart, the chart behavior is different. The continuous data is displayed correctly, but the non-continuous data is ignored on the chart. In such cases, handling of empty points in the dataset is required to make the data continuous and displayed correctly on the chart. In this tip we will look at how to implement a solution for this problem.

Solution

In this tip we will create a line chart to simulate the problem and then we will walk through how to fix this issue.

Step 1
First let's create a dataset that will support the problem. Below is a very simple dataset, that is designed for this purpose. We have created two groups / series for the sales person field (i.e. Series-1 and Series-2) as we intend to create two series on the line chart. We have given a common sales value in the Sales field for each SalesPerson just to illustrate this example. The Sales field will be the Y axis and the Month field will be the X axis. As we intend to create non-continuous data, we have used completely non-matching month numbers for each SalesPerson. So for Series-1 data is present for months 1,3,5,7,9 and for Series-2 data is present for months 2,4,6,8.

create a dataset for use in the ssrs report

Step 2
Create a new SSRS report to access the data from the above table we just created. We need to use this data in a line chart and data fields should be configured as shown in the below screenshot.

create a new ssrs report to access the data

Step 3
Now preview the report and you will find the result as shown in the below screenshot. As you can see nothing is displayed on the chart, because a line chart needs continuous data to create the line between two consecutive points. Here you can see that on the Y axis, ticks are from 1 to 10. For each group, we do not have continuous month values (i.e. we have empty or missing data points).

a line chart needs continuous to create the line between two points

Step 4
To fix this, click on the sales series section in the configuration box and select Properties as shown below.

in the configuration box of ssrs select properties

Step 5
You will find an EmptyPoint properties collection under the data section as shown in the above image. We need to specify the intelligence for Color when drawing lines for empty data points. Enter the Expression for Color in the EmptyPoint properties as shown in the below screenshot. The expression says if the SalesPerson value equals "Series-1" then use color "FCB441" otherwise use color "418CF0". These are the colors I selectecd, but you can use any colors you want for this expression.

enter the expression for color in the emptypoint properties

Step 6
Expand the CustomAttributes property collection and you will find the EmptyPointValue property. This property signifies how empty data points are handled. Average is the default value for this property. Let's say we want missing data points to be "Zero", so we will select "Zero" for this property as shown below. The effect that this property will have is that it will fill the missing data values for the X-axis with a value of zero.

emptypoint value property

Step 7
Preview the report now and you will find the result as shown in the below screenshot. Data is visible now, but if you look at the dataset carefully you will find that the colors are reversed for the series compared to the legend. This is the effect of the color intelligence we have specified in the earlier Expression. If you swap the color values in the Expression this will correct the result.

preview the report and view the result

Next Steps
  • Try handling different values for a different kind of chart.
  • Explore the other options available in the EmptyPoint properties collection to handle empty data points in the dataset.
  • Review these other SSRS tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, September 1, 2016 - 8:47:33 AM - Chirag Kakariya Back To Top (43246)

 

 

 

 

I prepared Multiple Category Group In Line Chart and its out put is like This :-

 

I want to remove This line between to series.

Thanks In Advance

 

 

I prepared Multiple Category Group In Line Chart and its out put is like This :-

 

I want to remove This line between to series.

Thanks In Advance


Friday, April 17, 2015 - 4:38:37 AM - Rohan Back To Top (36961)

Hi,

I want do sequence flow chart in srss with arrow direction , how can I do this , please help.


Thursday, November 6, 2014 - 11:30:10 PM - Naga Back To Top (35212)

Thank you very mush for helping regarding the  continuation of lins in line chart.

Giving same color to EmptyPoint section too.


Thursday, September 11, 2014 - 9:47:10 AM - nag Back To Top (34496)

How to Hide category group if Value is Zero in ssrs  column chart?


Friday, May 30, 2014 - 3:56:10 PM - Mubashar Back To Top (31997)

I have problem while Setting up the empty data point settings. No matter what changes I made in empty points settings, graph is always same. The data has some empty points but line is still continues. I want to suppress the line when there is any empty datapoint. I will appreciate your help. Thanks.


Monday, January 21, 2013 - 8:29:53 PM - Tejas Back To Top (21607)

but What if my dataset points are not continuous and I want my points to start from its origin. i.e.

Series 1 : (0,3),(5,15),(8,8)    Series 2: (0,5),(1,9),(3,10)


Friday, July 13, 2012 - 5:57:05 PM - Junior Fletcher Back To Top (18508)

Actually, instead of entering zero for the emptypoint, you can enter zero so that the graph lines flow into one another continuously instead of bouncing back and forth to zero at each null/empty/zero value


Monday, April 30, 2012 - 8:45:41 AM - Sonal Back To Top (17199)

My question is a sfollows:

there is a field in in database by name WEEK_NUM which contains values 46,47 and 49(week 48 is missing probably dere is no data for  it). In actuate deres in an option which shows a point for missing group in sorting and grouping ie. on x-axis dere are 4 points cming i.e. for week 46 47,49 and an empty point for week 48

i want the same for SSRs graph because due to this both the graphs in ACtuate and SSRS are cming different(SSRS plots only 3 points for 46  47 and 49)

Please help


Thursday, May 5, 2011 - 1:20:05 PM - Leif Peterson Back To Top (13761)

I will thanks


Thursday, May 5, 2011 - 11:08:49 AM - Siddharth Mehta Back To Top (13760)

When you have huge gaps in non-continuous data, using Median instead of Zero would be a better option. Even after including all possible axis values, if you find your axis is non-continuous, try using Median. Else you always have the option of using Expression.


Thursday, May 5, 2011 - 10:49:46 AM - Leif Peterson Back To Top (13759)

That is very interesting.  I didn't know that existed so thank you.  However, if I understand your example this only works if the all x-axis values exist in at least one of the series.  If series-1 consisted of 1 and 3 and series -2 consisted of 6 and 8 wouldn't the x axis then have 1.3.6 and 8?  I have always used a left join to a meta data table (such as month) so that all months would exists at least once in the result set and then backfill the null datapoints with 0.  Thoughts?















get free sql tips
agree to terms