CODEWITHSUNDEEP
reporting-servicesssrs-2012ssrs-expression
AndroidAL
AndroidAL

Reputation: 1119

Add N/A if all fields are blank SSRS Expression

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.

Upvotes: 3

Views: 2601

Answers (2)

Anup Agrawal
Anup Agrawal

Reputation: 6669

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.

Upvotes: 2

John O'Grady
John O'Grady

Reputation: 245

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

Upvotes: 1

Related Questions