Accela Contact Name and Full Multiline Address

COALESCE(CASE WHEN isnull(C.B1_FNAME,'') + isnull(C.B1_MNAME,'') + isnull(C.B1_LNAME,'') <> '' THEN CASE WHEN isnull(C.B1_MNAME,'') <> '' THEN C.B1_FNAME + ' ' + C.B1_MNAME + ' ' + C.B1_LNAME ELSE C.B1_FNAME + ' ' + C.B1_LNAME END ELSE NULL END
,CASE WHEN isnull(C.B1_FULL_NAME,'') <> '' THEN C.B1_FULL_NAME ELSE NULL END
,CASE WHEN isnull(C.B1_BUSINESS_NAME,'') <> '' THEN C.B1_BUSINESS_NAME ELSE NULL END) AS 'Tenant Name'

,CASE WHEN isnull(C.B1_ADDRESS1,'') <> '' THEN C.B1_ADDRESS1 + char(13)+char(10) ELSE '' END + 
 CASE WHEN isnull(C.B1_ADDRESS2,'') <> '' THEN C.B1_ADDRESS2 + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull(C.B1_ADDRESS3,'') <> '' THEN C.B1_ADDRESS3 + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull(C.B1_CITY,'') <> '' THEN C.B1_CITY + ', ' ELSE '' END +
 CASE WHEN isnull(C.B1_STATE,'') <> '' THEN C.B1_STATE + ' ' ELSE '' END +
 CASE WHEN isnull(C.B1_ZIP,'') <> '' THEN C.B1_ZIP ELSE '' END
AS 'Full Tenant Address'

OWNER NAME (Crystal Format)
COALESCE(CASE WHEN isnull("B3OWNERS"."B1_OWNER_FNAME",'') + isnull("B3OWNERS"."B1_OWNER_MNAME",'') + isnull("B3OWNERS"."B1_OWNER_LNAME",'') <> '' THEN CASE WHEN isnull("B3OWNERS"."B1_OWNER_MNAME",'') <> '' THEN "B3OWNERS"."B1_OWNER_FNAME" + ' ' + "B3OWNERS"."B1_OWNER_MNAME" + ' ' + "B3OWNERS"."B1_OWNER_LNAME" ELSE "B3OWNERS"."B1_OWNER_FNAME" + ' ' + "B3OWNERS"."B1_OWNER_LNAME" END ELSE NULL END
 ,CASE WHEN isnull("B3OWNERS"."B1_OWNER_FULL_NAME",'') <> '' THEN "B3OWNERS"."B1_OWNER_FULL_NAME" ELSE NULL END

 ,CASE WHEN isnull("B3OWNERS"."B1_OWNER_TITLE",'') <> '' THEN "B3OWNERS"."B1_OWNER_TITLE" ELSE NULL END)

OWNER ADDRESS (Crystal Format)
CASE WHEN isnull("B3OWNERS"."B1_MAIL_ADDRESS2",'') <> '' THEN "B3OWNERS"."B1_MAIL_ADDRESS2" + char(13)+char(10) ELSE '' END + 
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_ADDRESS3",'') <> '' THEN "B3OWNERS"."B1_MAIL_ADDRESS3" + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_ADDRESS1",'') <> '' THEN "B3OWNERS"."B1_MAIL_ADDRESS1" + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_CITY",'') <> '' THEN "B3OWNERS"."B1_MAIL_CITY" + ', ' ELSE '' END +
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_STATE",'') <> '' THEN "B3OWNERS"."B1_MAIL_STATE" + ' ' ELSE '' END +

 CASE WHEN isnull("B3OWNERS"."B1_MAIL_ZIP",'') <> '' THEN "B3OWNERS"."B1_MAIL_ZIP" ELSE '' END