Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
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.
PM - Gramps: no, that's incorrect. The minute you put two different values into the Values panel, you've got yourself more than one series. Series groups allows you to group those series. In order to do that, you have to first create the series (in the Values panel as the author of the article describes)
Ok here's the problem with this tip: the terminology. In your example, you have no series groups, so you are not displaying different series in different chart areas, as you propose. You have two different measures aka values. You can separate the different data measures into the different chart areas. You cannot separate different series into different chart areas.
Thursday, April 5, 2018 - 9:42:55 PM - Rodolfo Reyes
-Hi , I have treid to do the same thing but for me first chart giving me the correct data but second and third chart giving me same type data...they are not changing their shape. Basically i have three dataset. (1) BuildingWise (2) Regional (3) National Wise data.. and Category group is common..MonthyearName like : NOV 2015..
I want to create line chart which show week start date at horizantal bar and data will be two or three datetime record for each date , how can i show this data .
Wednesday, October 1, 2014 - 11:50:53 AM - Ivo Milanov
You can try Nevron Chart for SSRS - https://www.nevron.com/products-reporting-services-chart-overview.aspx. It has very flexible category axis labeling options, that avoid overlapping, has support for wrapping and other. I work for Nevron and we also provide paid support for SSRS so you can fix many problems with your reports using our extensions and services.
Best regards, Ivo
Monday, February 10, 2014 - 11:16:39 PM - Sukhjeet
I am using SSRS 2012. I have a series of charts that have multiple category groups. When I preview the chart, everything looks great unless the category group label is too long. In that case it just gets cut off. I've tried allowing "wrapped text", changed interval values,font size for Y AXIS but that seems to only affect the inside (child) category group. Is there a way to allow text wrapping on the outside (parent) group label?
I am expecting AREA VIII BUT it shows AR on y axis.
can we add 10-12 bar chart in a report so that all remains in same page. i am nt getting the option for the same to make them on the same page. it is going automitically on 2nd page.
I have a hard time just with seeing all of last year compared to YTD. YTD for both (parallel periods) or Daily rates are so much easier to compare.
Or have YTD on one graph, and then last year vs. YTD projected out to year end to see if they are on track.
So for me, good question / problem, but the example could have been better.
Similar to what Mr. Stricker noted, we should be trying to give a visual 'at a glance' view of the data that clearly shows who is doing well, and who might need some help meeting goals.
Tuesday, October 16, 2012 - 5:23:10 AM - Saikat Malakar
Please help me how to create dynamic series in chart. I have parameter. depending on the parameter the series will be change. How to write the expression.
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
Tuesday, August 30, 2011 - 4:47:11 AM - ThomasStricker
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?
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.
