Make an SSRS matrix dynamic, like a pivot table
Part one of a two-part series of blogs

Create a matrix whose row, column and data fields are all selectable from dropdown lists.

  1. Dynamic Matrices like Pivot Tables in Reporting Services (this blog)
  2. Creating a Dynamic Matrix in SSRS

Posted by Andy Brown on 06 August 2012 | no comments

Dynamic Matrices like Pivot Tables in Reporting Services

This blog shows you how you can create dynamic grouping for matrices:

Dynamic matrix

Matrix showing (in this case) the average film budget by director and certificate.

The Central Principle - Dynamic Fields

It's relatively simple to get this example working (as explained in the rest of this blog), once you've realised a simple principle: that you can refer to a field by name in two ways.

The first (and most common) way is the method used by SSRS expressions:

Referring to a field name

This refers to the value of the field called FilmName in the collection of Fields.

 

However, you can also refer to a field using the following syntax:

=Fields("FilmName").Value

The advantage of this approach is that the thing in brackets can then be any expression.  For example:

=Fields(Parameters!RowField.Value).value

We'll use this throughout our matrix to make it truly dynamic!  Let's begin by creating the parameters for our report.