Jul 31

Showing Error Magnitude Using a Banded Column Chart in SSRS

Forecasting is never completely accurate and there are always differences between the predicted value and the actual value.  Users often want to see the errors displayed so that they can judge for themselves how accurate the forecast process is.  This article will show how to create an error chart  in SSRS with a visual indicator to judge the size of the errors.

The data set that we’ll be using for this example will contain twelve months of error values as well as the standard error for the data set.  The standard error would be provided by the statistical software that generates the forecasts.  Here is the sample data set:

Raw Data Set

Next we’ll create a Dataset in SSRS that selects this data from SQL Server.

Data Set

We’ll add a Chart to a blank SSRS report and then select a Column Chart for the type.

Select Chart

Now we’ll add the fields from the ErrorData dataset to the chart and see what we get with the default settings.  Month goes in the “Category Groups” area and PredictionError goes in the “Values” area.

Initial Chart

Initial Chart Preview

This column chart shows the months along the time axis and the error values along the y axis.  The chart gives the analyst some information, but it doesn’t give a sense of whether the errors for this time series are large or small.  We see the magnitude of the errors compared to each other, but not to the standard error.  In order to show that we’ll add colored bands to the column chart.

The first step to do this is to open the Vertical Axis Properties dialogue box.  We’ll change some of the default settings to match what we want to do.

Vertical Axis Properties

Notice that we’ve checked the “Use interlacing color” option as well as selected an appropriate color.  This is the color that will appear in horizontal bands across the column chart.  We’ve also changed the “Interval” to be equal to the StandardError column from the dataset.  This means that each colored band will be one standard error tall.  The “Interval type” has also been changed to Number.

The final change that we need to make is to the “Minimum” and “Maximum” fields.  If we leave these to adjust automatically, then the bands won’t be centered around zero.  This will make it hard to compare a column to the colored band.  So instead we’ll change these to be three standard errors above and below zero.

Minimum Expression

Maximum Expression

Using a range of three standard error amounts will account for 99.5% of the error values.  If you want to have even greater certainty that all of the error values lie in this range, then you can increase the values to 4 standard errors.

Here is what the final chart looks like once we have made all of these changes:

Final Chart

Now it is much easier to estimate how serious the errors are.  We can see that there are a few errors that are greater than one standard error, but none that are greater than two standard errors.  We can feel confident that the forecast model is reasonably accurate since errors are within the expected range.