Using trim with Join

Question

Using trim with Join

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

[font="Verdana"]Ah! You see, with some context, it makes sense as to why you need to do it within the report. :D[/font]

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 2

benlatham (2/3/2009)
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.

Thanks for the insight, Ben... and I'm sorry I don't have an answer for the problem. Guess I might have to break down and teach myself about OLAP and MDX.

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

Mess SSC Rookie Points: 47 More actions · Answer 3

Hi benlatham,

Thank you so much for your post. Have been struggling with this and was about to give up. Now it works thanks to you 🙂

Regards,

SM

EdVassie SSC Guru Points: 60472 More actions · Answer 4

Another approach is to create a lookup containing both values and labels.

The label you show in the report drop-down can be formatted with leading spaces or whatever you want. The value field is prepared in the way most suited for further processing, with no leading space, etc.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

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

This approach doesn't solve the specific problem that my function was written to address. When you have a parameter based on an SSAS dimension hierarchy the label usually contains the member caption with spaces prepended to indicate its level and the value is the MDX member name string. If you want to display what the user has selected in the report header for example you wouldn't use the value because it is in an user friendly form and the user friendly labels have spaces in them so you need a way to trim and concatenate the lables.

Ben

darrylw SSC Journeyman Points: 96 More actions · Answer 6

Thanks benlatham..... I was pulling my hair out with this one!

Great solution 🙂

nmclauss 44078 Grasshopper Points: 13 More actions · Answer 7

nmclauss 44078

Grasshopper

Points: 13

January 9, 2013 at 3:51 pm

#1575492

Beautiful, thank you!