Displaying Multiple Series on an SSRS Chart using Chart Area

By: Siddharth Mehta | Read Comments (2) | Print Siddharth is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence. Related Tips: 1 | 2 | 3 | 4 | 5 | More

Problem

A report might contain multiple data series on a chart, which can have considerably varying scales but common category groups. In such cases where this a big difference in scales, the data series with the lower scale can become obscured. In this tip we will take a look at how to solve this problem using Chart Areas.

Solution

To simulate the problem let's create a report with multiple data series on one chart.

Step 1:

Create a new SSRS report and add a bar chart to the report.

Step 2:

Add a dataset to the report using the Sales.vSalesPerson from AdventureWorks sample database.

Step 3:

Configure the chart as shown in the below screenshot. Here we have two Values - SalesLastYear and SalesYTD and one Category Group on FirstName.

Step 4:

When you execute / preview the report, you should see a report similar to the below screenshot. If you look at the chart carefully, you will notice that none of the bars for the Sales Last Year series cross the halfway point in the chart.  This is because the values in the Sales YTD series are so much greater they obscure the Sales Last Year series.

Step 5:

To solve this, generally developers would create multiple charts and configure each of them to contain one series, in order to separate the series. But even if you separate these charts, these charts are connected with each other as they have the same category groups. So when you make a change to one chart, you will have to keep making changes to other chart too.

SSRS 2008 introduced a new feature known as Chart Area, which can solve this issue but still keep the charts aligned and associated with each other. To use this, select the series, right-click and select Chart -> Add New Chart Area, which will add a new chart area below the existing chart area.

Step 6:

Select the Sales YTD series from the Chart Data tab, right-click and select "Series Properties". From this tab, on the "Axes and Chart Area" page, select the newly created Chart Area "Area1" as shown below. This will make the Sales YTD series display in this new chart area and the rest of the chart data configurations will remain the same for both the Sales YTD and Sales Last Year series.

Step 7:

Preview the report, and you should find a report similar to the below screenshot. Using Chart Area, you can separate the series, which would effectively have the benefit of flexible scaling for individual series.  Both charts can be configured using a centralized configuration and still have the flexibility to override and configure each chart area independently if needed.

Next Steps

• Add more chart areas to the report and try adding more series to these chart areas. Also add one more category group to the above example and study the effect on the chart areas.
• Try changing the chart types for different chart areas.

Related Tips: 1 | 2 | 3 | 4 | 5 | More | Become a paid author

Last Update: 8/22/2011

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Tuesday, August 30, 2011 - 4:47:11 AM - ThomasStricker

Read The Tip

I understand what you are saying, but if you split the chart into two chart areas, you lose the  comparison effect that you have with one chart area. In other words, the two chart areas do not have the effect of displaying graphically how much greater is the increase in sales this year-to-date from last year. I assume you can add data labels to top of the sales year-to-date bars which show how much is the percentage increase from sales last year, to give the effect of how much greater is the increase?

Monday, February 20, 2012 - 12:47:20 AM - Rafael	Read The Tip
Andy I've used ADS in the past with MySQL. For some rseoan it never clicked with me perhaps I didn't use it long enough Will check it out again with MSSQL

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. Need SQL Server help and not sure where to turn? Reach out to the Edgewood experts for a Health Check starting at $995. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Join the over million SQL Server Professionals who get their issues resolved daily. Demystify TempDB Performance and Manageability