Forcing Values into SSRS Matrix Columns and Rows


The concept of the trusty SSRS matrix control is great for dynamically expanding columns and rows based on the objects source data.  It’s behaviour operates in a very similar way to a Pivot table, that is, expanding and contracting based on underlying data.

However, when you want to force data to be shown in rows or columns, the expanding nature of the control can present a problem where the data does not exist. Consider a report where we allow the user to select multiple subcategories and then we display the number of products in each category based on their selection.

The hierarchy for this relationship is product –> product subcategory –> product category, so if we allow the user to select a subcategory, and then restrict our result set based on this, the matrix will not show the all available Categories.

Consider the base (standard query) that uses a parameter for subcategory_id (its a multi-valued parameter).

select
pc.Name as CategoryName
, count(*) as ProductCount
from [Production].[Product] p
join [Production].[ProductSubcategory] psc
on p.ProductSubcategoryID = psc.ProductSubcategoryID
and p.ProductSubcategoryID IN (@subcategory_id)
join [Production].[ProductCategory] pc
on psc.ProductCategoryID = pc.ProductCategoryID

group by pc.Name

 

image If we have a matrix (as in the image to the left), the Category values that are shown are dependent on what values are in the dataset.

If we want to extend the control to show all values, we have to append it to the dataset.  Therefore, our query would need to be changed to the following;

select
pc.Name as CategoryName
, count(*) as ProductCount
from [Production].[Product] p
join [Production].[ProductSubcategory] psc
on p.ProductSubcategoryID = psc.ProductSubcategoryID
and p.ProductSubcategoryID IN (@subcategory_id)
join [Production].[ProductCategory] pc
on psc.ProductCategoryID = pc.ProductCategoryID

group by pc.Name

union all select
Name as CategoryName
, null as ProductCount

from Production.ProductCategory

With such a small example this approach works fine. However, when the dataset includes additional fields, and the matrix groups on those fields, the grouping behaviour may create undesired results because the additional union adds another layer of data to group on.

Consider, for example if we included country, our dataset would look like

[country name (as a value)],[category],[count]   -- for the 'real data'
[country name (as a null) ],[category],[count]   -- for the 'additional data'

If a report included a grouping on country name, the nulls would be in their own group as a value and this is not what we want to display.

To solve this, we can apply a filter on the matrix to remove the blank countries. This does not remove the additional categories (that is values being shown), it just effect what country values are shown. A handy little trick 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s