Adding a Secondary Axis to a SSRS Chart

Home > Blogs > SQL Server > Adding a Secondary Axis to a SSRS Chart

Adding a Secondary Axis to a SSRS Chart

Like This Blog 7Peter Avila
Added by July 15, 2013

What happens when you use the same scale to compare values that are actually in different scales?

Have a look at this chart that was generated using Reporting Services and notice that you can barely see the gold columns for the Average Sales values.

001-Adding-a-Secondary-Axis-to-a-SQL-Reporting-Services-Chart

The data (see the table next to the chart) tells us that a scale ranging from 0 to 625,000 is needed to plot the Total Sales, while a scale ranging from 0 to only 10,000 would do for the Average Sales. Though Average Sales and Total Sales are on different scales, the chart plots them on one axis, leading to the tiny columns for Average Sales.

Learning Point: 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.

Let’s put in a secondary axis for the Average Sales.

  1. 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…
    002-series-properties-Adding-Secondary-Axis-to-a-SQL-Reporting-Services-Chart
  2. 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.
    003-axes-chart-area-Adding-a-Secondary-Axis-to-a-SQL-Reporting-Services-Chart
  3. Now when we preview the report, it’s a lot easier to see the Average Sales.
    004-new-secondary-axis-SQL-Reporting-Services-Chart
    Even though this gives us a clearer picture of the variations in the Average Sales, it’s a bit odd to see the gold columns side-by-side with the blue columns; it still looks a lot like they are on the same scale.Learning Point: When comparing series on different scales, using a different graphic representation for each series makes the chart clearer.
    Let’s change the gold column to a line.
  4. 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 Change Chart Type…
    005-change-chart-type-Adding-a-Secondary-Axis-to-a-SQL-Reporting-Services-Chart
  5. Select a new chart type for the series and then click OK when done; in our case, we’ll select the first line chart type.
    006-select-chart-Adding-a-Secondary-Axis-to-a-SQL-Reporting-Services-Chart
  6. The chart looks much better now. It still shows us what the Average Sales does through the different Quarters together with what the Total Sales does, but with the different chart types, they don’t look so tied together anymore. It’s easier to see that they are on different scales.
    007-new-chart-Adding-a-Secondary-Axis-to-a-SQL-Reporting-Services-Chart
    …and we can even make the gold line a bit thicker.
  7. In the report designer, right-click the line and select Series Properties…
    008-series-properties-Adding-a-Secondary-Axis-to-a-SQL-Reporting-Services-Chart
  8. Select Border, then change the line width as desired, and then click OK when done; in our case, we’ll change the line width to 2.5.
    009-series-properties-boarder-Adding-a-Secondary-Axis-to-a-SQL-Reporting-Services-Chart
  9. Much better!
    010-new-chart-Adding-a-Secondary-Axis-to-a-SQL-Reporting-Services-Chart
You may also like:  Microsoft White Paper - SQL Server 2016 and Windows Server 2016 Better Together

 

 

Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Agile Methodology in Project Management

0 42 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

0 118 1

In this video Dan Wahlin demonstrate the similarities and differences between C# and JavaScript and the future of JavaScript (ES6).

Detailed Forensic Investigation of Malware Infections – April 21, 2015

2 124 1

In this IT Security training video, Security expert Mike Danseglio (CISSP / CEH) will perform several malware investigations including rootkits, botnets, viruses, and browser toolbars.

Write a Comment

See what people are saying...

  1. Vinod

    Is it possible to add more than 1 secondary axis to rdlc report?

  2. Sunanda

    I have a requirement to do the very same thing in SQL Server Mobile Reports. Can we do that?

  3. Srinivas

    SSRS :
    I have a chart report with two fields Assets and Liabilities (2 bars). on the same chart I want show another bar with the difference value between two fields.

  4. Ramiro MIllan

    Hi, I am trying to do something like this but, when I change one of my series, all of them change to line, I am using ssrs 2016 , already, can be this the problem?

  5. Alida Meira

    Hi, awesome article, thank you! Now if the primary axis shows values from 0 to 8000 and the secondary axis from -2- to 80, how can I get the primary and secondary axis 0 to be aligned?

  6. Dinesh Balaji

    Hi,
    Thanks for the beautiful article. it helps me lot.

    Reg,
    Dinesh Balaji.N

  7. santosh pandey

    Really great work Peter Avila. I have exactly the same requirment.Thanks alot…
    You are too intelligent, you grow with your this sharp mind.
    Thanks again

Share your thoughts...

Please fill out the comment form below to post a reply.