Tuesday, January 16, 2018

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.

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands