Solved

How do I use substring from Multi select  parameter in SSRS

Posted on 2009-07-01
17
1,462 Views
Last Modified: 2013-11-27
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



0
Comment
Question by:KuldeepReddy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
17 Comments
 
LVL 15

Expert Comment

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

Rob
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24761312
Oh, I'm not thinking... yeah, you can't easily apply the InStr to each value in the array. Hold on...
0
 
LVL 15

Accepted Solution

by:
rob_farley earned 75 total points
ID: 24761350
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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:KuldeepReddy
ID: 24763025
Where Can I find the Properties of the report.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24764208
In the Reports menu when you're on the Layout tab.
0
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 50 total points
ID: 24766630
Rob, cool code solution!

KuldeepReddy, the image below should help you find where to put Rob's code.
SSRS2008CustomCodeScreen.png
0
 

Author Comment

by:KuldeepReddy
ID: 24766888
It worked, thanks a lot
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24766943
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24768984
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
 

Author Comment

by:KuldeepReddy
ID: 24769076
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
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24769134
KuldeepReddy,
You can request attention at the top of page right under your question and ask to reopen to change the points.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24769160
Thanks everyone - you really didn't have to do this.
0
 

Author Comment

by:KuldeepReddy
ID: 24769216
Thanks mark
0
 

Author Closing Comment

by:KuldeepReddy
ID: 31599045
I am Very Thankful to Both Rob and CGLuttrell
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question