By: Siddharth Mehta | Last Updated: 2011-08-22 | Comments (12) | Reporting Services Charts
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.
Last Updated: 2011-08-22
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.
View all my tips
