Dynamic Column Names In SSRS 2005

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…

Continue reading →

Dynamic Querying Using Block Quotes

Dynamic SQL, a photo of a single train track splitting into many tracks.
Dynamic SQL, a photo of a single train track splitting into many tracks.

In ETL, we often have to load many targets from a common set of base tables. Inevitably the targets are different enough that we have to create multiple queries or views to populate the many outputs of data. Which is fine, except now you’ve got yourself a maintenance nightmare, one that is avoidable. I’d like to share a trick with you to take a single query that can be recursively modified to dynamically change its structure to get different outputs. I call it, dynamic querying using block quotes.

The business case:

The sales department would like to take a single report that already exists and split it into two reports. The additional report will require different fitlers, aggregates, columns, and even joins!

Continue reading →