CODEWITHSUNDEEP
sql-server-2008reporting-servicesexpression
2boolORNOT2bool
2boolORNOT2bool

Reputation: 567

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. Anyone 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... enter image description here

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.

Upvotes: 0

Views: 289

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

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...

Upvotes: 1

Related Questions