Solved

How do I use substring from Multi select  parameter in SSRS

Posted on 2009-07-01
17
1,430 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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