Build Dynamic Column Groups (and sub groups) in SSRS

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.Image

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

Image

Click on the expression builder:

Image

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)

Image

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:

Image

Allow nulls in your parameter and assign available values:

Image

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

Image

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

ImageImage

Now right click the field and paste this expression:

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

Image

Image

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

Image

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

Image

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

Image

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:

Image

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

Image

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:

Image

This expression will be:

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

Image

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

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

No Group Parameters Selected:

Image

One Group parameter selected:

Image

Two Group Parameters selected:

Image

All three Group Parameters selected:

Image

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!

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