Make an SSRS matrix dynamic, like a pivot table
Part two 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
  2. Creating a Dynamic Matrix in SSRS (this blog)

Posted by Andy Brown on 06 August 2012

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.

Creating a Dynamic Matrix in SSRS

The steps to follow to create our matrix are:

  1. Create the dropdown parameters at the top of the report; then
  2. Create the matrix to show whatever data the user requests.

Creating the Dropdown Parameters

Here's the sort of effect we're trying to achieve:

Dropdowns at top of report

The user can choose the row, column and data fields from dropdowns.

For each of these 3 parameters you should follow more or less the same steps.  I'll show how to create the column field dropdown (the other two will be created in the same way).  First create a parameter:

Create a new parameter

Right-click on Parameters and choose to add a new one.


Give your new parameter a sensible name and prompt:

Parameter properties dialog box

We'll call this parameter ColumnField, and specify that it's of type Text.


Now make this into a dropdown parameter by adding available values (one for each possible field value that a user can choose):

Adding available values

Here the columns can be certificates, countries, directors or studios.

When you've done this for all 3 parameters, you should have something like this:

The 3 parameters

The 3 parameters which will be chosen at the top of your report.


You should also set default values for all 3 parameters, so that a sensible matrix appears when you first run the report.

Creating the Matrix

Now that you have your 3 dropdown parameters, it's time to create the matrix.  To do this, add a basic matrix:

Basic matrix with row and column group

Create a basic matrix - we'll add the row and column groupings in a second.


What you now have to do is to specify the row and column groups, and the data field.  To do the row group, for example, first change the group's properties:

Change the properties of the row group

Right-click on the RowGroup and change its properties.


Set the grouping field to be an expression:

The RowGroup GroupExpression

Add a group expression, and set this to be the field chosen by the user in the drop list.


You can set the colum grouping expression to be something similar:

The column grouping expression

The column grouping expression uses the same concept.

For the data expression, right-click on the data text box and choose to set an expression for it:

Setting the data expression

Right-click on the Data textbox and set its expression.


We'll show the average of the data field chosen in the parameter drop list:

Data expression

Show the average of a field whose name is chosen by the user at run-time.

Making the Matrix Look Presentable 

You now need to add labels and a title for your report:

Matrix without labels The same matrix after adding labels
As it is now The final report

For box number 1 (the matrix title), you want it do display something like:

Expression for matrix title

The title will refer to the parameter choices made by the user.

A typical title will thus look something like this:

Typical title for report

The title if you show average budget by certificate and director.


For box numbers 2 and 3, you could use expressions like this:

Box number Expression
2 =Fields(Parameters!ColumnField.Value).value
3 =Fields(Parameters!RowField.Value).value

Notice that we keep repeating the same trick - using the Fields collection to refer to a field whose name is equal to the value of the relevant dropdown parameter.

If you're fired up after reading this article, you could try creating a report with dynamic grouping (ie the user chooses which field they want to group by). 

  1. Dynamic Matrices like Pivot Tables in Reporting Services
  2. Creating a Dynamic Matrix in SSRS (this blog)
This blog has 0 threads Add post