Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Add Calculated Series to a Graph


By:   |   Read Comments (2)   |   Related Tips: > Reporting Services Charts

Attend these FREE MSSQLTips webcasts >> click to register


Problem

I created a line chart in Reporting Services (SSRS). In order to analyze the data better, Iíd like to add some sort of trendline to the graph, such as a weighted average for example. In Excel it is possible to add this functionality with just a few clicks. Can we achieve the same in SSRS without having to do many extra calculations?

Solution

Fortunately it is pretty straight forward in SSRS to add an extra calculated series to a graph. A calculated series is a series where its data is derived from another series in the graph through a specific formula. A very simple example is the mean. Here the data is derived by aggregating the data of the base series and dividing it by the total number of data points. The result is a straight horizontal line indicating the value of the mean:

Example of a calculated series displaying the mean value

The advantage of a calculated series is that you donít have to go through the hassle of calculating it yourself in the source query. Calculating the mean is easy enough, but there are more complex formulas. The following calculated series can be added in SSRS:

  • Mean
  • Median
  • Bollinger bands
  • Moving average
  • Exponential moving average
  • Triangular moving average
  • Weighted moving average
  • MACD
  • Detrended price oscillator
  • Envelopes
  • Performance
  • Rate of change
  • Relative strength index
  • Standard deviation
  • TRIX

Those are a lot of options, but unfortunately one of the most crucial trendlines is missing: the linear regression! This means that if you want to add a linear trendline to your graph, you still have to do the calculations yourself. This will be covered in another tip.

The official Microsoft documentation is pretty poor on this subject. The MSDN page for calculated series just lists the available options without explaining anything. There is a bit more information in an MSDN page about adding a moving average in Report Builder, but as you can guess it deals only with the moving average. Fortunately there is a lot more information on the different formulas that can be found here from this 3rd party tool. A lot of the same formulas are supported and luckily they provided an explanation.

SQL Server Reporting Services Line Chart Example

To demonstrate the functionality of the calculated series, we will first create a simple line chart in SSRS. The test data is retrieved from the AdventureWorksDW2012 database with the following query:

SELECT
  [OrderQuantity] = SUM([OrderQuantity])
 ,[OrderDate]  = CONVERT(DATE,[OrderDate])
 ,OrderMonth   = LEFT(d.[EnglishMonthName],3) + '-' + CONVERT(CHAR(4),YEAR([OrderDate]))
FROM [dbo].[FactResellerSales] f
JOIN [dbo].[DimProduct]   p ON f.ProductKey = p.ProductKey
JOIN [dbo].[DimDate]   d ON f.OrderDateKey = d.DateKey 
WHERE OrderDate >= '2007-01-01' AND EnglishProductName = 'Mountain-500 Silver, 42'
GROUP BY p.[ProductKey], [OrderDate],[EnglishMonthName]
ORDER BY [OrderDate];

With this data set we will analyze the trends of the order quantity of the product ĎMountain-500 Silver,42í. The OrderMonth column is a user friendly representation of the year and month, and the OrderDate column is kept to ensure a correct sorting in SSRS.

Add a data source pointing to the AdventureWorksDW2012 database and add a data set with the query listed above (Learn more - Reporting Services Tutorial). Then add a line chart using the following configuration:

The starting point of our example

The OrderMonth category group is sorted by OrderDate to prevent the months from being sorted alphabetically.

Adding a calculated series to a SSRS report

To add a new calculated series, right-click the series you want to base the calculation upon and select Add Calculated SeriesÖ

Adding a calculated series

A dialog pops up, where you can select the type of formula. Some formulas have parameters and others have none at all, such as the mean and the median. The moving average takes two parameters: the number of periods to average on and if the series should start from the first point or not.

Choosing the formula and configuring the parameters

In the other panes of the dialog you can configure typical settings of a series, such as visibility, axes, borders and so on. After clicking OK, the new calculated series is added to the graph.

The new calculated series

Itís a good idea to change the name of the new series in the properties pane. When clicking the down arrow and selecting Calculated Series PropertiesÖ you can go back to the same dialog as before. There you can change the parameters of the series or choose a different formula altogether.

Going to the properties...

Itís possible to add multiple calculated series to one chart. In the following example, the moving average, the mean and the median have been added to our example.

Adding multiple calculates series

Make sure to not clutter the graph by adding too many series. It is not possible to add a calculated series based upon another calculated series. Furthermore, not all chart types in SSRS support calculated series; you can add a calculated series to a bar chart for example, but not to a pie chart. If you do try though, you are greeted with the following message:

Calculated series incompatible with graph type

If you click Yes, a new chart area is added to the chart that will contain the newly created calculated series.

I do not accept responsability for the ugliness of this chart

The pie chart defies all rules of best practices regarding data visualization, but itís just there to show off the concept.

Conclusion

The calculated series feature in SSRS allows us to easily add trendlines to an existing graph. There are many different options, such as moving averages, mean, median and more advanced formulas such as the Bollinger bands, but somehow they forgot to add the linear regression trendline, which is really unfortunate as its use is very common. The official documentation about the different formulas is also severely lacking.

Next Steps
  • You can download the .rdl report here, if you want to see the calculated series in action.
  • You can find more SSRS tips here.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, January 09, 2015 - 9:05:15 AM - Koen Verbeeck Back To Top

Hi Hans,

thanks for reading the tip.

Unfortunately the forecast is not possible in SSRS. You would have to calculate it yourself, as I did here:
http://www.mssqltips.com/sqlservertip/3432/add-a-linear-trendline-to-a-graph-in-sql-server-reporting-services/

Power View in Office 365 does have automatic forecasting ability. 


Friday, January 09, 2015 - 5:47:34 AM - Hans Back To Top

Hi Koen,

Thanks for the article, this will come in handy! It's a feature I was not aware of.

Is it possible to extend the calculated series to get some kind of forecasting? I would like the line of the caluated series to be extended beyond the (time-)range of original report data.

Thanks,
Hans


Learn more about SQL Server tools