In SSRS, when you add a Series Group to a chart the data for each series is aggregated based on the series grouping specified. In some situations, you may have a series that you want aggregated differently than the series grouping.
For example, you may want to display a series on the chart for each individual product model (series grouping) and also display a series for the product category – so you can compare the individual product performance to the product category as a whole.
My sample dataset consists of the following fields: Month, Product Category, Model, Units Sold, Failed Quantity, and ModelRank (integer value that indicates the model’s rank based on Units Sold). I want to compare the failure rate (Sum of Failed Quantity / Sum of Units Sold) of each individual model (‘Bicycle Model A’, ‘Bicycle Model B’, ‘Bicycle Model C’, etc.) to the failure rate of the product category overall (‘Bicycles’).
I created a chart with two series named FailureRateModel and FailureRateProductCategory. The category grouping is by month and the series grouping is by product model. At this point, both series are grouped by product model.
Since each series is grouped by the product model, the chart displays as follows:
I want to override the grouping by product model in the ‘FailureRateProductCategory’ series so this series displays the failure rate of the product category (Bicycles) as a whole.
To accomplish this, I first need to modify the expression in the Value field of the ‘FailureRateProductCategory’ series so it aggregates to the category group of the chart (the category group is named Chart_CategoryGroup by default).
Note: Chart_CategoryGroup is the name of the category group and not the field the category is grouped by.
In this example, the Value expression is:
Next, I will modify the visibility of the series so it only appears once on the chart (instead of being displayed for each product model). In this case, I am going to set the visibility expression so the series is only displayed when ModelRank = 1 (it is sort of a hack, but achieves the desired result).
Finally, I will update the expression of the series’ legend ‘Custom legend text’ field so it displays the product category name. In this example the name of the product category is ‘Bicycles’.
The chart now displays as follows:
The series related to the product category ‘Bicycles’ is displayed in black. We can now easily compare the failure rate of each individual bicycle model to that of the Bicycle product category overall.