I find myself creating more and more SSRS reports with a SharePoint List as the data source. If you’re like me you’ll run into this issue sooner or later; the dreaded ;#.
Function ReplaceChar(ByVal value As String) As String Dim fixedString As String if(String.IsNullOrEmpty(value)) then fixedString = String.Empty else fixedString = value.Replace(";#",", ") fixedString = fixedString.substring(2, fixedString.length-2) fixedString = fixedString.substring(0, fixedString.length-2) end if Return fixedString End Function
You’ll take this code and add it to the Code section of the Report properties. MSDN explains it pretty well for me.
How’s it work? Pretty sweet if you ask me. Starts by declaring “fixedString.” Then we go with the all important if statement. If it’s Null or blank then leave it blank. For everything else, replace ;# with a comma. But we don’t want commas at the start and end of our string so we gotta take care of those. The next two fixedString.substring statements take care of that.
When you’re ready to use the code on a particular column, right-click said column then choose Expression. From there you should see your column setup like this: =Fields!DogNames.Value. Add the following red text and you’re golden: =Code.ReplaceChar(Fields!DogNames.Value)