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




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

One thought on “CRM 2011 – Reports – removing lines if blank for address

  1. champ January 5, 2012 / 9:33 am


    Could you please tell me that is there a way to eliminate blank lines (null address fields) from email and letter activities in CRM2011?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s