0

I need to return first letter and last name that are passed back in a multi-value parameter.

Example: John Doe, Jane Doe, Jimmy Smith

I need jdoe, jdoe, jsmith so I can apply a filter in my reporting table.

Currently...

Update: I got the short version of the usernames with the below, but I have a space between the J and DOE. Additionally, it's a string and I need to join them to apply it as a filter to my Tablix.

=LCASE(LEFT(Parameters!Users.Value(0),1))+LCASE(MID(Parameters!Users.Value(0),InStrRev(CStr(Parameters!Users.Value(0))," ")))

This is all due to the fact I can't pass back multi-value parameter into my query. As seen below...

SSRS: @Usernames =Parameters!Users.Value

*Join and Split functions don't resolve my issue

SQL: WHERE Username IN (@Usernames)

Any insight on either part would be helpful! Thanks!

aduguid
  • 3,099
  • 6
  • 18
  • 37
ereed18
  • 1
  • 1
  • Why don't you populate your parameter from source with the required format jdoe, jsmith, etc.? – alejandro zuleta Jan 26 '17 at 19:30
  • Currently I have two different data sets. One uses the full name (example: John Doe), while the other uses the short name (jdoe). Unfortunately, I can't alter how the usernames are displayed, but was hoping to still work my data set around it. – ereed18 Jan 26 '17 at 22:26
  • If you want to get rid of the white space you can use replace `REPLACE( LCASE(LEFT(Parameters!Users.Value(0),1))+LCASE(MID(Parameters!Users.Value(0),InStrRev(CStr(Parameters!Users.Value(0))," "))), " ","")`. Note you are only getting the first selected value in your parameter. It could be helpful if you explain better what you need. – alejandro zuleta Jan 26 '17 at 22:32
  • I need to apply a filter of usernames to my dataset. I'm unable to insert multiple usernames into SQL (per above). SPLIT and JOIN functions in SSRS doesn't work to allow me to place the string into query. Therefore, my only workaround is to pull all users and apply the username filter to the tablix and display only selected users. Therefore, two different datasets, two different tables, two different types of usernames. Yeah, I need to remove (0). At first, I was trying to just get the username set-up correctly. Now I need to get all usernames in a string form. – ereed18 Jan 26 '17 at 22:33
  • If you remove the (0) it will reference an array which your expression won't process, surely resulting in an error. I think you will need a custom code to get what you require. – alejandro zuleta Jan 26 '17 at 22:42
  • Yeah, I was afraid of that. Still confused why passing multi-value parameters into a query is one of the most lacking items in SQL. – ereed18 Jan 26 '17 at 22:43
  • One approach could be add a UDF in your SQL server to process "jdoe,jsmith" value to multiple rows. check [this](http://stackoverflow.com/a/5493616/2647648). – alejandro zuleta Jan 26 '17 at 22:47

0 Answers0