SSRS - Adding Secondary Axis Chart

In the data visualisation, a chart can more effectively convey data information than can lengthy lists of data. When multiple series are present on a chart, we must have to determine the best way to compare the series.

As we know that a chart always has two axis types called them as primary and secondary. The secondary axis comes into the picture when comparing two value sets with two distinct data ranges that share a common category.

To understand this beautiful feature, we can take an example where we have a chart that calculates Yearly Total Sales vs Yearly Average Sales. In this case, time period is common to both value sets.

USE [Demo] GO SELECT        Years=Year( [OrderDate])       ,TotalSales=Sum([SalesCash])      ,AverageSales=AVG([SalesCash])    FROM      [dbo].[TBL_SALES_ORDER]    Group by      Year( [OrderDate]) GO Years TotalSales AverageSales 2013 17140.72 1008.28 2014 26618.36 1478.80 2015 38790.44 2155.02 2016 28433.76 1496.51 2017 51194.28 2844.13 2018 10116.16 5058.08

However, when both series are plotted on the same y-axis, we cannot make a useful comparison because the scale of the y-axis is optimized for the largest values in the dataset.

In the above chart, we can see the average yearly sales are too short in comparison of total yearly sales. So, we can understand that when a series in a chart is not measured on the same scale used for the other series in the chart, a secondary axis can make the chart easier to read.

How to implement Secondary axis for Yearly Average Sales

In the report designer, right-click the data series on the graph (in our case, that will be the gold column in our column chart) and select Series Properties as given below:

In the Series Properties window, Select Axes and Chart Area, then Secondary for either vertical or horizontal axes as needed (in our case, we will select Secondary under Vertical axis), and then click OK when done as given below-

After closing the Series properties window for Yearly Average Sales, we can preview of the report as given below:

If we show Yearly Total Sales on the primary axis and Yearly Average Sales on the secondary axis, we can display each series on its own y-axis with its own scale of values. The series still share a common x-axis.