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!

7 thoughts on “Remove ;# when using a SharePoint List as a data source in SSRS”

  1. Thanks a lot for this! The same issue was happening to me and found this blog and followed these instructions and it worked! Awesome solution.

    Like

  2. Have you figured out how to create a parameter to filter based off of items in a multi-choice column? I have a column with office names in it. If a task applies to that office, then the checkbox is selected. It can apply to more than one office. I need a way to create a parameter on the report so the offices can filter by their office code to see only the tasks that apply to them. Any ideas?

    Like

  3. Thank you so much!! I spent hours looking for this as the field in SharePoint was a choice field. You are truly a savior!!

    Like

Leave a Reply to Joe Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s