CODEWITHSUNDEEP
sql-server-2008reporting-services
InfoLearner
InfoLearner

Reputation: 15608

SSRS Report, How do I have Group By on the Columns

In MS SQL 2008 R2 DB, I have a table:

Name, Value, Type
A, 1, T1
B, 2, T1
C, 3, T1
D, 4, T1

A, 10, T2
B, 20, T2
C, 13, T2
D, 45, T2

A, 11, T3
B, 22, T3
C, 33, T3
D, 44, T3

What I want to do is to get this:

Name, Type
  T1, T2, T3
A, 1, 10, 11
B, 2, 20, 22
C, 3, 13, 33
D, 4, 45, 44

From the query, I can return this: Name, Value, Type A, 1, T1 B, 2, T1 C, 3, T1 D, 4, T1

A, 10, T2
B, 20, T2
C, 13, T2
D, 45, T2

A, 11, T3
B, 22, T3
C, 33, T3
D, 44, T3

Now I want to take this data and in SSRS, transform it into this form:

Name, Type
      T1, T2, T3
    A, 1, 10, 11
    B, 2, 20, 22
    C, 3, 13, 33
    D, 4, 45, 44

Types can change from one execution to another.

Upvotes: 1

Views: 595

Answers (2)

Jamie F
Jamie F

Reputation: 23819

Create a new report using the Business Intelligence Development Studio "Report Wizard." It will guide you through creating a data set, row groups and column groups.

Once you are familiar with these, then you probably won't want to use the wizard, but for someone getting started with a matrix, it's a good fit.

Upvotes: 0

Ben English
Ben English

Reputation: 3938

The way I see it, you have 2 options here:

You can modify your query using PIVOT to transpose the rows into columns

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Or you can simply create a 'Matrix' report. Where Name is your Row value, type is your column value and value is your details value.

Upvotes: 4

Related Questions