In this post I will show you how to create a simple report with dual Y-axis column chart. It is a very common requirement in reports and to be honest – quite difficult to find out how. I’ve been struggling for a while to get to the desired result. First let me share what kind of data I prepared for my demo and what will be the end result.
1. The data – it as a simple dataset with three columns – one for month and two for measures.
select 'Jan' as Month,10200 as Measure1, 11528 as Measure2 union all select 'Feb' as Month,6247 as Measure1, 3098 as Measure2 union all select 'March' as Month,5982 as Measure1,6814 as Measure2 union all select 'Apr' as Month,5200 as Measure1, 2528 as Measure2 union all select 'May' as Month,5000 as Measure1, 1000 as Measure2 union all select 'Jun' as Month,4000 as Measure1, 413 as Measure2
2. The end result:
So how to do it? Of course First you create a new report and enter the dataset.
1) Add a simple column, add Month filed to the Category box and Measure1 and Measure2 – to Data box
2) Right click on the second data series (in my case – the yellow columns). Right-click on the selected series and go to Series Properties –> Axis and Chart area.Change the value axis to Secondary and hit OK. The result should be in showing a second axis on the right of the chart just like below
3) After adding the secondary axis, again select the second series (yellow ones), right-click on it and go to Change Chart Type. Then select some of the line chart templates and hit OK button. I am using the first and the simplest one.
And that is how you can create a dual Y-Axis column chart which can help you in better visualization of certain data. Of course till getting the report done for end-user running, you can add some labels, include Markers and Data Labels for better reading. But it is up to you and your organization’s requirements how functional and “colorful” a report to be.