I recently needed to create a SSRS report that allowed my client to group his data by choosing the group field from a drop down list. It’s actually quite simple to accomplish this, although not intuitive straight away, so I thought I would share the steps I took.

First create a report with a valid dataset. Include the fields you will be grouping on. I am using the Adventure Works 2012 Data warehouse. If you would like to follow along my query is:

SELECT top 3000
[TotalProductCost]
,[SalesAmount]
,[MonthNumberOfYear]
,[CalendarQuarter]
,[CalendarYear]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] fs
JOIN [dbo].[DimDate] dd
ON fs.OrderDateKey=dd.DateKey

Next create a tabular design with your detail fields and a few blank columns.

Next Add a group by right clicking on the details group:

Click on the expression builder:

Paste this code into your expression:

=iif (Parameters!GroupBy.Value is Nothing,1,Fields(iif(Parameters!GroupBy.Value is Nothing, “CalendarYear”,Parameters!GroupBy.Value)).Value)

This handles the scenario where we don’t want to choose a grouping.

Now we need to actually create the parameter by right clicking Parameters and selecting Add Parameter:

Allow nulls in your parameter and assign available values:

The names in the Value field need to match the names in your dataset.

Go back to your table and delete the extra group columns that were added when you created your groups:

Now right click the field and paste this expression:

=Fields(iif(Parameters!GroupBy.Value is Nothing, “CalendarYear”, Parameters!GroupBy.Value)).Value

Next you will create two identical parameters with the below names:

Now right click on your first group, select add group, select child group:

The child group settings will be identical to the parent one except you will update the code to read your second parameter:

You can add multiple sub groups this way to really empower your users!

Next I will show you why we deleted the group column and kept the regular tablix column. We want to be able to hide the entire column if the user doesn’t choose it to group on.

A “grouped column” doesn’t have the visibility property available:

But you can right click on your regular column and select Column Visibility and then click on the expression builder:

In your expression paste the below code:

=iif (Parameters!GroupBy.Value is Nothing,True,False)

This will effectively hide your column if you don’t choose it to group by.

Now right click on your blank field and choose expression:

This expression will be:

=iif (Parameters!GroupBySub2.Value is Nothing,1,Fields(iif(Parameters!GroupBySub2.Value is Nothing, “CalendarYear”,Parameters!GroupBySub2.Value)).Value)

Your column heading will be the parameter label (not the parameter value):

And now the fun part. 🙂 You get to preview your report:

No Group Parameters Selected:

One Group parameter selected:

Two Group Parameters selected:

All three Group Parameters selected:

And that’s it! Now I just need to clean up the formatting to make everything pretty.

I hope this is helpful. Please let me know if you find an easier way to accomplish this, or any feedback you might have.

Thanks for reading and have a great day!