sql server 2008 reporting services

0
votes

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.

1
sql-server-2008reporting-servicesexpression
Can you please add what your data look like when it get to the report and a bit more info on what you want to display. You seem to say you want to display the codes but then say you don't??? - jimconstable
So Fields!CODE.Value can either: have a value; have an empty string value; be NULL? And you want the NULLs and empty strings to display the same? - thomasswilliams
I actually want the null cells to go away hehe! I was just using the Iif command as a starting point. I do want to display the code values but they are dispursed. One per row, in one of around 20 cells. - 2boolORNOT2bool

1 Answers

1
votes

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