You’re offline. This is a read only version of the page.
Hi all,
I need to add an address box to a delivery note report in CRM online, using fetxh XML and visual studio (BIDS). Basically the issue I have is empty address fields and formatting the box to show the address in one block ignoring the empty lines (such as address line 1 and 2 is filled but 3 is blank, but on other addresses its filled in also).
*This post is locked for comments
I have the same question (0)
Hi Paul,
If you use the SQL that it would be easier in select query you can manipulate using STUFF and Coalesce.
To construct the address field, you need o construct each individual fields because you cannot use address1_composite field, it gives blank value on its value.
But, if you are using Fetch XML,
I think there are three options :
You create a table with total row for your individual address field, for example you have:
ROW 1 address1_line1
ROW 2 address1_line2
ROW 3 address1_line3
ROW 4 address1_city
ROW 5 address1_stateorprovince
ROW 6 address1_postalcode
ROW 7 address1_county
ROW 8 address1_COUNTRY
Then you can set the visibility using ISNOTHING or using = ""
Example:
stackoverflow.com/.../hiding-rows-that-contain-no-values
Or
=IIF(IsNothing(Fields!address1_line1.Value),True,False)
=IIF(IsNothing(Fields!address1_line2.Value),True,False)
Set them for each row value visibility.
I think it is the best way.
Second way, you can use long IIF to have concept:
If address 2 no value then concatenate address1_line1, address1_line3 and so on until country, (using concatenation &)
www.earniepost.info/.../expression-concatenation.aspx
and I use comma separator (I give example only until address1_city, because too long)
=IIF(IsNothing(Fields!address1_line2.Value), Fields!address1_line1 & "," & Fields!address1_line3 & "," & Fields!address1_city,
Fields!address1_line1 & "," & Fields!address1_line2 & "," & Fields!address1_line3 & "," & Fields!address1_city
and you also need to give condition if address 3 no value, or if state no value or county no value (long if you need)
You can keep them in the split fields as well.
For example you have 3 fields:
1. If address1_line1 no value
2. If address1_line2 no value
3. If address1_line3 no value
Then concatenate them together.
Third, you can also use Switch I think.
Example:
social.msdn.microsoft.com/.../is-there-a-coalesce-function-in-reporting-service-2005
And another example, I haven't tried:
Using Join
social.msdn.microsoft.com/.../ssrs-concatenate-field-in-one-string-delimited-by-comma-without-sql
I recommend the first way actually.
I hope this can help you.
Thank you.
Share
Report
All responses (
Answers (
44,524
