Creating an SSRS report with dynamic columns

A couple years ago, I created a report in SQL Server Reporting Services that allowed the columns in the report to be configured dynamically to display different datapoints. The report allows the user to configure a parameter for each column to determine what datapoint is shown in that column. This approach is too unwieldy for a report accessed by the user directly, but can be handy if the report is distributed by a subscription. In this way, multiple users can get the same report configured to their needs. If you have a large number of users, this approach is much faster & more maintainable than creating separate reports for each one.

Disclaimer, this was done in Visual Studio 2005 against a SQL Server 2005 DB so things may have changed a bit.

First, I defined the SQL query. Notice that there are "Aggregation Type" columns returned which reference some of the actual data columns. This tells the report how to aggregate that particular column. So, for example if it makes sense for the report's aggregation row to display a total for Column1. We set "Sum" as it's aggregation type.

Also notice that the where clause allows filtering. @IDs & @Teams are multivalued report parameters which allow the data to be limited by the subscription to a specific list of IDs & or Teams (nothing special about these names, just an example).

SELECT
 Column1,
 Column2,
 Column3,
        Column4,
        Column5,

 --Aggregation Types
 --Options include Sum, Avg, Min, Max
 'Sum' AS Column1AggregationType,
 'Avg' AS Column2AggregationType,
 'Max' AS Column4AggregationType
FROM
 Table1
WHERE
 ID IN (@IDs)
 AND Team IN (@Teams)

Next, I defined a function to dynamically retrieve a field's value given the field name. This function will be called by the cell expression (see below).

Public Function GetFieldValue(ByVal fieldCollection as Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields, ByVal fieldName as String) as Object
Try
   Dim x as Field
  x = fieldCollection(fieldName)
Catch ex As Exception
   Return ""
End Try
Return fieldCollection(fieldName).Value
End Function

Last, here are the cell contents for the cells in the header, aggregation, & details rows of the table in the report.

Header row cell contents:
Sets the column header label text if the user has selected a datapoint for that column.

= IIF(Parameters!Column2.Value <> "None", Parameters!Column2.Label, "")

Aggregation row cell contents:
This grabs the "AggregationType" columns from the SQL Query and performs the specified aggregation. Notice that the options (Sum, Avg, etc.) are the same as those mentioned in the comment in the SQL query.

= IIF(
 Code.GetFieldValue(Fields, Parameters!Column2.Value + "AggregationType") = "Sum",
 Round(Sum(val(Fields(Parameters!Column2.Value).Value)), 2),
 IIF(
  Code.GetFieldValue(Fields, Parameters!Column2.Value + "AggregationType") = "Avg",
  Round(Avg(val(Fields(Parameters!Column2.Value).Value)), 2),
  IIF(
   Code.GetFieldValue(Fields, Parameters!Column2.Value + "AggregationType") = "Max",
   Round(Max(val(Fields(Parameters!Column2.Value).Value)), 2),
   IIF(
    Code.GetFieldValue(Fields, Parameters!Column2.Value + "AggregationType") = "Min",
    Round(Min(val(Fields(Parameters!Column2.Value).Value)), 2),
    ""
   )
  )
 )
)

Details row cell contents:
Uses the GetFieldValue function shown above to get the value of the field chosen & assigned to the parameter for that column. There is a parameter for each configurable column, and each parameter has a full list of the available datapoints for that column (each datapoint must be in the SQL Query).

= Code.GetFieldValue(Fields, Parameters!Column2.Value)

Combine all of those things and you have an SSRS report with dynamic columns. Here's a couple other things that I got working that may be handy.

Sorting from a parameter:
I created a parameter named Sort Direction with two available values (Ascending & Descending)

I then configured the table's sorting tab as seen in the screenshot below.

Dynamic Node Expansion:
I created a Boolean parameter called ExpandNodes. Then under table properties --> Groups tab --> Details Grouping button --> Visibility tab I set the "initial visibility to this expression

= NOT(Parameters!ExpandNodes.Value)

Nothing complicated, but can be very powerful when the report must target both management & front line people. Managers usually only care about the stuff in the aggregate row & front liners care about the details.

Custom Grouping:
I created a parameter for valid groupings (for example: the "Teams" from the SQL example above) with valid groupings for the data points. I then put the following in the table's grouping expression.

= Code.GetFieldValue(Fields, Parameters!GroupBy.Value + "Code")