3

I have a number of fields in one column of my table. I have it if field1 is blank display field2 and so on, What i trying to do now is if all fields are blank display N/A.

What i have tried below;

=IFF(ISNothing(Fields!field1.Value) & IIF(IsNothing(Fields!field2.Value),"N/A",VbCRLF & Fields!field2.Value))

What this sometimes displays is field1N/A.

Can anyone point me in the right direction?

Thanks,

UPDATE 1:

I have also tried;

=IFF(ISNothing(Fields!field1.Value),"N/A",Fields!field1.Value & IIF(IsNothing(Fields!field2.Value),"N/A",VbCRLF & Fields!field2.Value))

That also did not work.

Pedram
  • 6,256
  • 10
  • 65
  • 87
AndroidAL
  • 1,111
  • 4
  • 15
  • 35

2 Answers2

2

There are two ways to do this. You can either handle it in SQL Server Query or in the SSRS

Method 1: TSQL

You can use the COALESCE function to find the first non null value. Replace it with N/A if all are NULLs

SELECT COALESCE(Field1, Field2, Field3, 'N/A') AS newFieldValue, ....
FROM myTable.....
WHERE ....

Method 2: SSRS

There is no COALESCE equivalent in SSRS. You can either use iif or switch to emulate the logic.

=SWITCH(NOT(ISNothing(Fields!field1.Value)), Fields!field1.Value, 
       NOT(ISNothing(Fields!field2.Value)), Fields!field2.Value,
       NOT(ISNothing(Fields!field3.Value)), Fields!field3.Value,
       1=1, "N/A")

Also remember if there is space in the field, it won't be handled by ISNULL function. In that case you will also need to use the similar logic to handle the empty spaces.

HTH.

Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32
1

I'd suggest what you need is ISNULL.

Example usage as follows:

ISNULL(su.address1_line2, 'N/A')

This will find null values in that column and replace them with N/A in the report

John O'Grady
  • 235
  • 3
  • 14
  • 1
    Hi, Thanks for the reply. So i could go ISNULL(Field1.value,Field2.value, "N/A") – AndroidAL Aug 10 '15 at 15:56
  • 1
    Hi afaik you would need to wrap the idk isnull function around each variable separately as per example above e.g. ISNULL(field1,'N/A') and then ISNULL (field2, 'N/A'). What this does is replace null values with the specified second argument in this case N/A though it could be an empty string. Note the single quotes syntax around the replacement string. I've found it a useful approach when you then want to apply further transformations to the strings.. E.g. Concatenation. If you concatenateb multiple strings and one of them is null it will general change the resulting string value to null. – John O'Grady Aug 10 '15 at 23:36