CRM 2011 - Reports - removing lines if blank for address

I had a tricky problem of adding an address to a report.  The address had the usual

address 1

address 2

city

county

etc

The problem was a lot of the addresses didn’t have a value for address 2 and it was leaving a nasty look blank row on my report.  So I asked one of the reports gurus at work and he found this lovely if statement for my expression.  It checks to see if the value is blank, if the field has a value and then puts in a carriage return and then goes to the next line.

=

IIf(IsNothing(First(Fields!Address1_Name.Value, ”DataSet5″) ),”",(First(Fields!Address1_Name.Value, ”DataSet5″) ))&

IIf(IsNothing(First(Fields!Address1_Name.Value, ”DataSet5″) ),”",ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_Line1.Value, ”DataSet5″) ),”",(First(Fields!Address1_Line1.Value, ”DataSet5″) ))&

IIf(IsNothing(First(Fields!Address1_Line1.Value, ”DataSet5″) ),”",ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_Line2.Value, ”DataSet5″) ),”",(First(Fields!Address1_Line2.Value, ”DataSet5″) ))&

IIf(IsNothing(First(Fields!Address1_Line2.Value, ”DataSet5″) ),”",ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_Line3.Value, ”DataSet5″) ),”",(First(Fields!Address1_Line3.Value, ”DataSet5″) ))&

IIf(IsNothing(First(Fields!Address1_Line3.Value, ”DataSet5″) ),”",ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_City.Value, ”DataSet5″) ),”",(First(Fields!Address1_City.Value, ”DataSet5″) ))&

IIf(IsNothing(First(Fields!Address1_City.Value, ”DataSet5″) ),”",ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_StateOrProvince.Value, ”DataSet5″) ),”",(First(Fields!Address1_StateOrProvince.Value, ”DataSet5″) ))&

IIf(IsNothing(First(Fields!Address1_StateOrProvince.Value, ”DataSet5″) ),”",ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_PostalCode.Value, ”DataSet5″) ),”",(First(Fields!Address1_PostalCode.Value, ”DataSet5″) ))&

IIf(IsNothing(First(Fields!Address1_PostalCode.Value, ”DataSet5″) ),”",ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_Country.Value, ”DataSet5″) ),”",(First(Fields!Address1_Country.Value, ”DataSet5″) ))&

IIf(IsNothing(First(Fields!Address1_Country.Value, ”DataSet5″) ),”",ChrW(13)&ChrW(10) )

About these ads

Posted in: CRM 2011, CRM 4, reports