Solved

How do I use substring from Multi select  parameter in SSRS

Posted on 2009-07-01
17
1,443 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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