sql server 2008 reporting services

I'm trying to generate a matrix report. I have an SSN row and a 3 digit code column. The 3 digit codes are randomly inserted into cells through out the report, one per row. I would like to have them display all in one column. I have tried...

=Iif(IsNothing(Fields!CODE.Value),"The Field Is Null",Fields!CODE.Value)

But this just displays "The Field is Null". I want noting displayed as in...

=Iif(IsNothing(Fields!CODE.Value),"",Fields!CODE.Value)

But have the null cells themselves excluded. An开发者_StackOverflow社区yone know a trick to pull this off?

the result would look like this...

ssn          code
123456789    123
123456789    123
and so on

My matrix structure looks like this...

and the Exp is now set to =Iif(IsNothing(Fields!ID5.Value),"The Field Is Null",Fields!ID5.Value) Note: The ID5 is just a quick nameing convention. ID5 corresponds to the code. ID2 corresponds to the SSN.

---

Basically, you want to compress several columns into one column excluding the null columns?

Probably the easiest way is to do it in the query:

SELECT SSN, IsNull(Code1, '') + IsNull(Code2, '') + IsNull(Code3, '') AS Code
FROM MyTable

Alternatively, do it in the expression in Reporting Services:

=IIF(IsNothing(Fields!Code1.Value), "", Fields!Code1.Value) + IIF(IsNothing(Fields!Code2.Value), "", Fields!Code2.Value)

and so on...