Solved

How do I use substring from Multi select  parameter in SSRS

Posted on 2009-07-01
17
1,437 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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 26

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 26

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 26

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 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