How do I strip special characters in SSRS for Lookup columns with Multiple Selections?

Question

Hello all,
Please let me know if I am posting in the wrong location.

I am building reports against my SharePoint List (actually a library).  1 of the columns I want to display in the report is a Lookup column that allows Multiple lookups.  Problem is that the SharePoint Web Service call GetListItems returns the values with the item id and ";#" combination.  For example:

122;#Apple;#112;#Carrot

but I need the following format:
Apple, Carrot

Following this great article posting - http://www.sharepointu.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx

I was able to drop the first part using the following code

function GetNameFromSP(pFullID as string) as string
  dim strRet as string
  dim iPos as integer

  if pFullID = nothing then return ""
  if pFullID = "" then return ""
  iPos = Instr(pFullID, ";")
  if iPos < 1 then return pFullID

  return Mid(pFullID, iPos +2)
end function

But it does not replace the subsquent id references.

Anyone done this before that can provide me tips?

Thanks!

---

Jeff Kozloff
Project Manager, Solutions Team
Bamboo Solutions
http://www.bamboosolutions.com

Answer 1

>Please let me know if I am posting in the wrong location.

As other forum members will tell you, you don't need to ask :)

Moving to Business Intelligence which is the place for SSRS questions.

---

WSS FAQ sites: http://wssv2faq.mindsharp.com and http://wssv3faq.mindsharp.com
Total list of WSS 3.0 / MOSS 2007 Books (including foreign language) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx

Answer 2

Hi Jeff,

"I was able to drop the first part using the following code" -- "But it does not replace the subsquent id references.
" 

Can you explain a bit more on what you are looking for...

You will get the "ID;#" in the result. You would require to do some string manipulation to get rid of them

 

---

Raghavan

Answer 3

All,
Here is the updated code that resolves my issue (a coworker of mine provided the following code):

Function TrimMultiLookup(ByVal s As String) As String

        Dim combStr As String

        Dim array As String()

        Dim c As Integer

        combStr = String.Empty

 

        array = Split(s, ";#")

        Console.WriteLine(array.Length)

        For c = 1 To array.Length - 1 Step 2

            combStr += array(c) + ", "

        Next

 

        Return Left(combStr, combStr.Length - 2)

 

    End Function

 

---

Jeff Kozloff
Project Manager, Solutions Team
Bamboo Solutions
http://www.bamboosolutions.com