Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Chart Optimization Tips

Published on
34,508 Points
12 Endorsements
Last Modified:
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2016: MVP Data Platform
Here are some optimization techniques that can be used when creating charts in SQL Server 2008 Reporting Services.  These tips will probably be already known to experienced chart developers, but freshmen charters may spent some time searching how to achieve something before actually finding it, if finding it at all.  I know because I've been there myself when I started out and I also know because I've seen questions on forums related to this.

Okay, enough vague intro-words, let's get concrete now.  In the following example I'll be creating a regular column chart.

1. X-axis: show all labels

By default, a chart in SSRS will automatically position the labels on the X-axis as it best fits.  However, one of these options includes hiding labels when the chart feels there are too many to show.  As you can guess, this is not always what we want.
 Column bar chart without any changes to its default settingsHave a look at the chart above and try to guess what you're seeing.  Difficult, huh?  Let's make some changes to it.

First we start with some basics like giving it a clear title.  This chart shows the annual revenue per state/province, split up by store specialty.  The states or provinces shown are those of the selected country.  I would call it "Annual Revenue per State/Province by Specialty for <SELECTED_COUNTRY>".  As you probably know, the chart's title can be edited by giving it a single-click.  However, this does not give you the option to build an expression so unless you want to type it all from memory, here's another option.  You can right-click on the chart's title.  This gives you a pop-up menu with Title Properties... as one of the options.
 Right-click menu on chart titleSelecting that one will give you the Chart Title Properties where you have the familiar Expression Builder icon next to Title Text textbox.
 Chart Title PropertiesThis is my expression:
="Annual Revenue per State/Province by Specialty for " & Parameters!Country.Value

Open in new window

Also, let's move the legend to the upper middle to make extra horizontal space for all those bars.  This can be done through the Legend Properties.
 Legend PropertiesLastly, now that we've freed up some space for the chart area, we'll modify the X-axis properties so that it shows all labels.

When opening up the axis properties for the X-axis on a column chart you get the Category Axis Properties screen.  Like all other property screens, this is also one with several pages.  You see that one of the pages is called Labels and as you want it to show all labels, that's were you start looking.  Well, stop looking, that's the wrong place.  The option that you need is located in the first page, the Axis Options, and its called Interval.  This is the interval between each label on the axis, and by default it is set to Auto.  As we want all labels, change it to 1.
 Category Axis PropertiesFollowing screenshot shows what the result looks like:
 Column chart showing all labels on category axisBetter, but we're not quite there yet.

2. X-axis: rotate labels in all directions

Right now the labels on the X-axis are difficult to read unless we turn our screen 90 degrees clockwise.  Let's dive again into the Category Axis Properties to put them diagonally.  On the Labels page there's an option to specify the Label Rotation Angle.   To be able to do this you need to activate the Disable auto-fit radio button.  Putting 45 as value will give the following:
 Labels rotated 45 degreesThe labels are rotated 45 degrees.  But not in the direction that I would prefer.  It's nicer when they're positioned from bottom-left to top-right.  So you start increasing the rotation value.  However, once you're past 90 you notice that the labels stay vertically, so 135 degrees does not put them in the direction as you'd hoped.  The answer is quite simple, once you know it.  As we want the opposite of our 45 degrees, and it's not 135, try -45 :-)
 Category Axis Properties with Label rotation angle set to a negative valueIndeed, that gives the following result (I've also removed the X-axis title).
 Column chart with optimized X-axisTo finalize the X-axis optimization, I've added sorting so that the States/Provinces are sorted alphabetically.  This is a recommended design practice to keep your different charts consistent.

To get the labels sorted, you should not look into the Axis properties but in the Category Group Properties:
 Pop-up menu to get to the Category Group PropertiesOn the Sorting page just add the field that's shown on the X-axis.
 Category Group Properties with sorting

3. Y-axis: make numbers readable

So, now that we're done with the X-axis, let's move on to the next letter of the alphabet.  Our Y-axis (also known as Value Axis on a column chart) is not very readable at the moment.  The numbers are too large and there's no formatting.  You don't even see that this is a currency value.

This time we need the Number page of the Value Axis Properties.  We want:

no decimals
a thousands separator
the numbers divided by 1000 (that's the "Show values in" setting)
a dollar sign in front of the value
To get all this, you can set the options as shown in the screenshot:
 Value Axis Properties optimized for large currenciesAnd here's our chart again:
 Chart with optimized Y-axisAdditionally, the axis title has also gotten a clear value.  It indicates how the values should be read.

4. Y-axis: move to right-hand side

To conclude this article I'll show you how to move the Y-axis to the other side of the chart.  This may seem like an odd thing to do, but sometimes the business people want to display a chart that way so you'd better have strong convincing skills or know how to do it.  Or both :-)

In fact, a chart has got two Value axes and two Category axes, a Primary and a Secondary.  The Primary axes are the ones used right now.  To move the visible axis to the other side, it's not through the Value Axis Properties as you might think (I think you're getting the hang of it now, right? ;-).  No, all you need to do is move all data series to the secondary axis.  This can be done through the Series Properties.
 Pop-up menu on data seriesThe Axis and Chart Area page is the one you're after.
 Series Properties with Secondary Value axis activated
That will give us the following end result:
 Column chart with Y-axis on the right-hand sideBTW: you will need to re-do the axis formatting when you switch to the secondary axis so better start with axis activation before formatting it.

Ow, if you found this article to be helpful, don't forget to click the "Yes" button below.

Thank you for reading my article, and happy charting!

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

Great Explanation.

How can we achive same functionality in SSS 2005 ?

My requirment is to display X-Axis lable in Vertical Way.

LVL 37

Author Comment

Out-of-the-box you can't do that in SSRS 2005.

From http://msdn.microsoft.com/en-us/library/aa964128%28SQL.90%29.aspx:

"Currently, built-in Reporting Services charts only allow automatic positioning in order to avoid overlapping the x-axis labels. The label direction (horizontal/vertical) of the axis labels depends on the label string sizes and the available space. X-axis labels are either shown horizontally in one line, horizontally in multiple lines with line breaks, or vertically. Showing x-axis labels at an angle, or explicit manual control over individual x-axis label positions is currently not supported. "
LVL 21

Expert Comment

by:Jason Yousef, MS
Wonderful article, great work and effort, thanks for taking the time to put ALL OF THAT together :)

God bless you for sharing the knowledge...
LVL 37

Author Comment

Thanks for the praise and vote :-)

Expert Comment

Great article and great tips, i have spent lots of hours to make some good graphical reports ...

A high tech blog and SQL Server

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month