Transposing a dataset in SSRS

Problem: We wish to tranpose a dataset in SSRS so that the columns headings become row headings, we dont want to do this in T-SQL

Solution: Move the detail group from the default row group to a column group

The linked document below includes detailed images on how to achieve this, this post contains the same information minus images.

Transpose Dataset using SSRS

The above report is our starting point, we want our end point to be a report where the first row is a row of business entity ids the second is a row of first names etc
1 Add New Table

2 Delete Row Group (and related rows)

3. Add Column Group

4. Delete non detail columns

5. Add rows to the table

6. Bind each cell to a value in the dataset

7. Add a header column

Advertisements

6 Responses to “Transposing a dataset in SSRS”

  1. Mike Says:

    This is the solution that I have been looking for for days! Every article, other than this, says that the solution is a pivot and that wouldn’t work for me because all of my columns needed to be transposed (I didn’t have a non-pivoted column). Thank you for posting this informative and detailed explanation of the process.

  2. Rizwaan Says:

    Agree with Mike, this is exactly what I had been looking for too and everyone else tried to suggest a matrix based solution. Hats off to you and respect!!

  3. Shirley Says:

    This is great! How would you show an ID and name if it wasn’t in the table. For example, if ID 3 – Roberto Tamburello, wasn’t in the table but you need to show ID 3 with the name blank, how would you do that? I have an instance where I have grades with enorllments and I need to show grades 6-12, even if the data does not exist in the table.

    Thanks.

  4. Matt Says:

    Awesome trick! This saved me alot of time and effort! Thanks for sharing this!

  5. SteveT Says:

    Ditto to the posts above! I had explored the Matrix in SSRS and pivoting the data in SQL. The SQL pivot approach worked but was not great from a maintenance standpoint. Thanks again!!!

  6. Taran Says:

    Awesome Man you rock….Trust me i have been looking this from last 2-3 days and was tired and dont know all of suddenly your page turned up and thought lets give a lst try…You rock …Appreciat all your help

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


%d bloggers like this: