?
Solved

How do I use substring from Multi select  parameter in SSRS

Posted on 2009-07-01
17
Medium Priority
?
1,474 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 300 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 200 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

752 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