Dynamic Grouping
Question:
How can I give my users the ability to dynamically select fields on which to group within a report?
Answer:
The key to dynamic grouping in a report is this: Practically everything in a report can be based on an expression. From grouping to column headers to column and row visibility... since all of it is expression-based, it can be based on parameters supplied by the user.
Step 1: Build your report with static grouping.
It will be easier to start with a standard report with non-dynamic grouping and modify it from there.
Step 2: Define the grouping parameters.
For each dynamic group, create a parameter for the field name on which to group. Add a valid values list containing the names of the fields on which you want to allow grouping. If you want grouping to be optional, also include null (with a label like "None").
Step 3: Change your group expressions to make them based on the parameters
The trick here is the indexer into the Fields collection. Normally, you have static group expressions like this: =Fields!Year.Value.
But you can refer to items in the Fields collection using an alternate string-based syntax like this: =Fields("Year").Value.
Since the field name is just a string, that means you can use any string subexpression instead. In particular, you can use the parameter:
=Fields(Parameters!FirstFieldName.Value).Value
If you want to allow for optional grouping, this is slightly more complicated:
=iif(Parameters!Group1.Value is Nothing,1,Fields(iif(Parameters!Group1.Value is Nothing, "Year",Parameters!Group1.Value)).Value)
If the parameter value is Nothing, you can just group on a constant (like 1).
Notice the second iif embedded within the Fields collection indexer expression. This is needed because you'll get an error if you try to access the Fields collection with a null indexer [Remember: Visual Basic evaluates all arguments of all functions, so the outer iif wouldn't be enough to stop the Fields collection from erroring if the parameter value is null]. If the parameter value is Nothing, the third argument to the iif will return the value of the Year field, but it won't be used for anything.
Step 4: Change the column headings and report data to be based on the parameters
Before:
Customer Year
=Fields!Customer.Value =Fields!Year.Value
After:
=Parameters!Group1.Value =Parameters!Group2.Value
=Fields(Parameters!Group1.Value).Value =Fields(Parameters!Group2.Value).Value
Note: Again, if you're allowing for optional grouping, you'll need to make things slightly more complex:
=Fields(iif(Parameters!Group1.Value is Nothing, "Year", Parameters!Group1.Value)).Value
Step 5: Hide columns and rows for optional grouping
If you're allowing for optional grouping, you'll need to hide the unneeded columns and rows. You can do this by setting the Hidden property of the corresponding column and row to: =Parameters!Group1.Value is Nothing
A full working sample of dynamic grouping is attached.