Formatting Address Data separated by delimiter in the single field

Introduction:

Whenever the a single field in NAV contains Name, Address and other values seperated by delimiter such as ‘,’. But this field cannot formated in SSRS to look appropriate as follows.

For Example in NAV: John Peter, Long Island, New York,US.
Formatting in SSRS:
John Peter,
Long Island,
New York,
US.

Pre-requisites:

Microsoft Dynamics NAV.

Solution:

1. Getting a combined value field in this we are concating mutliple values seperated by ‘|’. In this case I’m using the comments on Sales Invoice Lines. This can be a single field or combination of mulitple fields
   
   Code to combine all comments in single variable
   
2. Writing a Query in SSRS report query to seperate the values by delimiter
   
   Field and its expression:
   
3. Output:
   
   In this output screen the comments are seperated properly in SSRS report.

Conclusion:

This is one of the easiest way of combining and seperating and formatting the report by the use of  delimeter.