How do I use substring from Multi select parameter in SSRS

In a SSRS report I am displaying values from multi select parameter.

right now I am using =Join( Parameters![paremetername].Value, ", " )

Out put I get now " usa-Newyork, England-London, usa-Chicago, Canada-Toronta"

The Out put I want "Newyork, London, Chicago, Toronto"

Basically what I want to display is the text after "-"

Thanks



KuldeepReddyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rob_farleyCommented:
You just need to do the substring bit before the Join. One second and I'll get you an example.

Rob
0
rob_farleyCommented:
Oh, I'm not thinking... yeah, you can't easily apply the InStr to each value in the array. Hold on...
0
rob_farleyCommented:
Put this in the Code section of Report Properties:

Public Function SplitDash(ByVal Vals As Array) As String
  Dim sOutput as string
  Dim i as integer

  sOutput = ""
  For i = 0 to Vals.Length -1
    sOutput = sOutput & ", " & right(Vals(i),Len(Vals(i))-InStr(Vals(i),"-"))
  Next

  return right(sOutput, len(sOutput)-2)
End Function

And then have your expression as:

=Code.SplitDash(Parameters!param.Value)

Hope this works... I feel like it's clumsy, but it's probably not too bad. Works on 2005 too.

Rob
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

KuldeepReddyAuthor Commented:
Where Can I find the Properties of the report.
0
rob_farleyCommented:
In the Reports menu when you're on the Layout tab.
0
Chris LuttrellSenior Database ArchitectCommented:
Rob, cool code solution!

KuldeepReddy, the image below should help you find where to put Rob's code.
SSRS2008CustomCodeScreen.png
0
KuldeepReddyAuthor Commented:
It worked, thanks a lot
0
Chris LuttrellSenior Database ArchitectCommented:
KuldeepReddy,
Not that I don't love to get points, but you've got to give Rob the credit (or at least most of it) for the code, I just pointed you to where to paste it into.
0
rob_farleyCommented:
Ah - but I didn't put a picture there. I was only on EE Mobile and couldn't put pictures in.

It's not about the points, although it would be nice to get a bit closer to Master in SQL 2008.
0
KuldeepReddyAuthor Commented:
Yeah I have mistakenly Accepted that as Solution. By the time I have realised that I  have accepted that as a Solution.
I have tried to reassign the Points but I dont know how to Re Assign the Points in EE. Thanks Rob
0
Chris LuttrellSenior Database ArchitectCommented:
KuldeepReddy,
You can request attention at the top of page right under your question and ask to reopen to change the points.
0
rob_farleyCommented:
Thanks everyone - you really didn't have to do this.
0
KuldeepReddyAuthor Commented:
Thanks mark
0
KuldeepReddyAuthor Commented:
I am Very Thankful to Both Rob and CGLuttrell
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.