After a lot of swearing I decided to make this post, dynamic column names in SSRS 2005, to help the internet and give back to the huddled masses who still have to support this old reporting services instance from 2005. In this version, as all of my dead-end googling revealed, there is no way to dynamically or ordinal-positionally set the column names in your reports — which is insane! I mean, what year were Microsoft in, in 2005?

Anway, like I said, after a lot of swearing, I came up with a creative way to achieve dynamic column names in SSRS 2005 and this post will share how you can implement the same solution.

Don’t even get me started on dynamic data sources…

 The Problem With SSRS 2005:

The underlying problem is that SSRS requires a hard-coded column name to retrieve from a hard-coded data source. Don’t even get me started on dynamic data sources. But, while that’s not a huge problem in itself, the real problem is it’s inability to use an ordinal position. For example:  SELECT foo FROM bar ORDER BY 1;  Here we’re ordering by the ordinal position of the first column, foo, in this query. It would make complete sense for SSRS to allow us to simply define the columns ordinal position when selecting which field we want in our data’s source. But, it doesn’t…

The Solution for Dynamic Column Names in SSRS 2005:

The trick is to define a static column name in your data set but union a new “row” for your dynamic column names. I did this all in a procedure which will give you the dynamic nature you’re looking for. Now, in SSRS, simply remove the column headers and pre-sort your data so that your new column names are at the top of the data set and make them bold or whatever formatting you desire.

Here’s some sample code:

Then, in SSRS, you can make the row of data for your dynamic fields bold by doing the following:

The Caveat’s To This Solution:

This solution assumes that the number of dynamic columns are the same, requires all fields to be varchar, and the dataset to be pre-sorted.