Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

SSRS: Ignore Series Grouping in Chart

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.

image

Since each series is grouped by the product model, the chart displays as follows:

image

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:

image

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).

image

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’.

image

The chart now displays as follows:

image

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.

Comments

Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...