Remove ;# when using a SharePoint List as a data source in SSRS

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 ;#.

Generally speaking, you’ll run across this when there is a choice column setup to allow multiple values. Gina and Zach helped me put this fix together:

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)

Enjoy!