Using trim with Join

Question

Post reply

Using trim with Join

jjafer

Old Hand

Points: 324
More actions
January 31, 2009 at 12:35 pm

#206142

Hi-
I want to trim the white spaces in a string that is used within Join function.
Join (TRIM(Parameters!vnd_name.Value),",") fails with the following error,
[rsCompilerErrorInExpression] The Value expression for the textbox ‘textbox2’ contains an error: [BC30518] Overload resolution failed because no accessible 'Join' can be called with these arguments:
Please help me fix this error.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 1

Isn't there something missing from that JOIN? Like a table or somthing and an equation?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

jjafer (1/31/2009)
Join (TRIM(Parameters!vnd_name.Value),",")

This looks an awful lot like VB.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

jjafer (1/31/2009)
Hi-
I want to trim the white spaces in a string that is used within Join function.
Join (TRIM(Parameters!vnd_name.Value),",") fails with the following error,
[rsCompilerErrorInExpression] The Value expression for the textbox ‘textbox2’ contains an error: [BC30518] Overload resolution failed because no accessible 'Join' can be called with these arguments:
Please help me fix this error.

I'm not sure about the availability of JOIN function, but you are missing "=" sign before the expression....

\Isn't there something missing from that JOIN? Like a table or somthing and an equation?

Hey Jeff, it was related to SSRS...

--Ramesh

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

Ramesh (2/1/2009)
Hey Jeff, it was related to SSRS...

Yep... knew that and I admittedly haven't used SSRS... but, there are certain things like the presence or absense of "=" signs that are common to all of these "languages". The OP's posted phrase just didn't look complete to me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

matzer SSCrazy Eights Points: 9727 More actions · Answer 5

The Join function in SSRS expects an array of objects or strings as its first argument. It concatenates all string values of the elements in the array and separates them by the second argument. The problem here is that the TRIM function returns a string value, not an array. The vnd_name parameter should be defined as multi-valued where all elements should be trimmed in front. Then =Join(Parameter!vnd_name.Value, ",") will work.

benlatham SSC Eights! Points: 933 More actions · Answer 6

Peter Brinkhaus (2/1/2009)
The Join function in SSRS expects an array of objects or strings as its first argument. It concatenates all string values of the elements in the array and separates them by the second argument. The problem here is that the TRIM function returns a string value, not an array. The vnd_name parameter should be defined as multi-valued where all elements should be trimmed in front. Then =Join(Parameter!vnd_name.Value, ",") will work.

What do you mean by "all elements should be trimmed in front"?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

benlatham (2/2/2009)
Peter Brinkhaus (2/1/2009)
The Join function in SSRS expects an array of objects or strings as its first argument. It concatenates all string values of the elements in the array and separates them by the second argument. The problem here is that the TRIM function returns a string value, not an array. The vnd_name parameter should be defined as multi-valued where all elements should be trimmed in front. Then =Join(Parameter!vnd_name.Value, ",") will work.
What do you mean by "all elements should be trimmed in front"?

I guess, he meant that, to trim all the elements in the parameter before loading it into the drop-down...

--Ramesh

benlatham SSC Eights! Points: 933 More actions · Answer 8

Right, I am was interested because I have encountered this issue myself. Sometimes you want spaces in the parameter label so trimming before loading in to the parameter is not always an appropriate solution. For example when you present a SSAS hierarchy in a parameter in a report that is based on a cube you normally have spaces in the parameter labels to indicate the level of the member in the hierarchy.

I wrote the following function to handle this situation

Public Function JoinTrim(myArray as object) As String

Dim ReturnString as string, Item as string, FirstItem as boolean = true

For each Item in myArray

If FirstItem = True

ReturnString = Trim(item)

Else

ReturnString = ReturnString + ", " + Trim(item)

End If

FirstItem = False

Next item

Return ReturnString

End Function

This can then be called in the report like this:

=Code.JoinTrim(Parameters!MyMultiValuedParam.Label)

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 9

benlatham (2/2/2009)
Right, I am was interested because I have encountered this issue myself. Sometimes you want spaces in the parameter label so trimming before loading in to the parameter is not always an appropriate solution. For example when you present a SSAS hierarchy in a parameter in a report that is based on a cube you normally have spaces in the parameter labels to indicate the level of the member in the hierarchy.
I wrote the following function to handle this situation
Public Function JoinTrim(myArray as object) As String
Dim ReturnString as string, Item as string, FirstItem as boolean = true
For each Item in myArray
If FirstItem = True
ReturnString = Trim(item)
Else
ReturnString = ReturnString + ", " + Trim(item)
End If
FirstItem = False
Next item
Return ReturnString
End Function
This can then be called in the report like this:
=Code.JoinTrim(Parameters!MyMultiValuedParam.Label)

Heh... on that note, I'm glad I do all this stuff in T-SQL. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 10

Jeff Moden (2/2/2009)
Heh... on that note, I'm glad I do all this stuff in T-SQL. 😛

[font="Verdana"]Me too! My personal rule is to use stored procedures to do all of the grunt work in organising the data, and then all I have to do in SSRS is things like formatting, presentation, grouping, parameterisation, running totals. But no real logic.[/font]

jjafer Old Hand Points: 324 More actions · Answer 11

jjafer

Old Hand

Points: 324

February 2, 2009 at 8:03 pm

#936884

thanks to all. This helps!

benlatham SSC Eights! Points: 933 More actions · Answer 12

Heh... on that note, I'm glad I do all this stuff in T-SQL. 😛

I agree in general that TSQL should be used to do all the work but in this case what we are trying to achieve is formatting inside a report. i.e. trim then join the values selected by a user for a parameter that contains spaces. I don't see how TSQL can help in this instance.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 13

benlatham (2/3/2009)
Heh... on that note, I'm glad I do all this stuff in T-SQL. 😛
I agree in general that TSQL should be used to do all the work but in this case what we are trying to achieve is formatting inside a report. i.e. trim then join the values selected by a user for a parameter that contains spaces. I don't see how TSQL can help in this instance.

Because I'm mostly a "batch programmer", I've really not used SSRS or any other reporting system to any great extent. But, based on what I've been asked to provide to those that do, my understanding is that many such reporting systems can use a parameterized T-SQL stored procedure as a data source and those who write the reports are frequently amazed at the performance and ease of implementation improvements that are achieved when such a thing is done.

That being said, why are you trying to clean parameters and do the related joins within a reporting system instead of just using a result set from a stored procedure? I know you said it's what you're trying to do, I just don't know the business requirement of why.

And, no... I'm not trying to argue any points or fan any flames here. When it comes to actually writing code in the reporting systems, I'm a bonafide newbie and would like some insight to your dilema... it sounds like a problem that many may encounter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

benlatham SSC Eights! Points: 933 More actions · Answer 14

Sure I appreciate that and I would be interested to hear from others who have faced this situation and their approach to it.

First thing to clarify is that in my situation I am reporting from a cube so the result sets that the report uses are not T-SQL stored procedures but MDX queries.

Business requirements dictate that the parameter values that the user sees are prepended with spaces to indicate the level in the hierarchy.

The question is how do we concatenate the values selected by the user for this parameter in order to display their selections on the report? I suppose you could write a T-SQL stored procedure that accepts a comma delimited string (this is what would be passed by SSRS to the stored proc when you map the query parameter to the multi select report parameter) and return the same string minus the spaces. You could then map this to an additional parameter to display on the report. But this seems more cumbersome than having the logic to achieve this in the report itself.