SSRS Combining Rows

[markberry]

Really simple question. Not used SSRS in a while so struggling.

 

I have a Data Set which looks something like this: Employee ID, FirstName, Surname, PhoneType, PhoneNumber

 

Which returns something like:

MBY, Mark, Berry, M, 0771234566

MBY, Mark, Berry, H, 01757123455

 

I create a table to display the information. I create 2 calculated fields, one to display Mobile, one to display Home number (using an expression =IIf(Fields!Code.Value = "M", Fields!Number.Value, "")

 

The problem I have is the table displays 2 rows for each person, one with mobile number, one with home number.

 

 

[table=width: 500]

[tr]

[td]ID[/td]

[td]First[/td]

[td]Surname[/td]

[td]Mobile[/td]

[td]Home[/td]

[/tr]

[tr]

[td]MBY[/td]

[td]Mark[/td]

[td]Berry[/td]

[td]0771234566[/td]

[td][/td]

[/tr]

[tr]

[td]MBY[/td]

[td]Mark[/td]

[td]Berry[/td]

[td][/td]

[td]01757123455[/td]

[/tr]

[/table]

 

 

I know there must be an easy solution to combine the rows, I’m guessing using grouping but I don’t know how! Any ideas?

[djrscally]

Really simple question. Not used SSRS in a while so struggling.

 

I have a Data Set which looks something like this: Employee ID, FirstName, Surname, PhoneType, PhoneNumber

 

Which returns something like:

MBY, Mark, Berry, M, 0771234566

MBY, Mark, Berry, H, 01757123455

 

I create a table to display the information. I create 2 calculated fields, one to display Mobile, one to display Home number (using an expression =IIf(Fields!Code.Value = "M", Fields!Number.Value, "")

 

The problem I have is the table displays 2 rows for each person, one with mobile number, one with home number.

 

 

[TABLE=width: 500]

[TR]

[TD]ID[/TD]

[TD]First[/TD]

[TD]Surname[/TD]

[TD]Mobile[/TD]

[TD]Home[/TD]

[/TR]

[TR]

[TD]MBY[/TD]

[TD]Mark[/TD]

[TD]Berry[/TD]

[TD]0771234566[/TD]

[TD][/TD]

[/TR]

[TR]

[TD]MBY[/TD]

[TD]Mark[/TD]

[TD]Berry[/TD]

[TD][/TD]

[TD]01757123455[/TD]

[/TR]

[/TABLE]

 

 

I know there must be an easy solution to combine the rows, I’m guessing using grouping but I don’t know how! Any ideas?

Use a Matrix instead of a Tablix. Set the Row Group to group on Staff Code, set the Column Group to group on Fields!Code.Value, and set the 'value' cell for that column group to be Fields!Number.Value (no need for your IIf())

 

This will return one row per staff member, and generate a column for each Phone number type (Home, Mobile, Work, Other etc) in the db, with the number below.

Edited September 27, 2017 by djrscally

[markberry]

Thanks Dan!