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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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.