dabello88 (7/29/2014)
thanks for the reply!I google split function and found the following function that gets me on my way but I'm having trouble trimming it so I only get the values I need. I'm also having trouble removing the "&". Note that this string is dynamic so it can be "CHILD=1625=0n=1==3&ADULT=1900=1j=1==1" or "ADULT=1075=0r=12==4" or ADULT=1100=2J=4==1&SENIOR=1000=2K=4==1", etc.
I'm wondering if I need an IIF staments to account for the "&"
=Join(Split(Fields!Tix_Price.Value,"="),", ")
result: CHILD, 900, A1, 3, , 1&SENIOR, 900, A4, 3, , 2
would like it to look like CHILD, SENIOR
Still not sure how to remove the extra values and only keep the two I need 😕
The *right* way to do this is to use REPLACE() function with a REGEX pattern, but here's something a bit more agricultural:
=(
SWITCH(INSTR(Parameters!INPUT.Value,"CHILD") >= 1, ",CHILD") +
SWITCH(INSTR(Parameters!INPUT.Value,"ADULT") >= 1, ",ADULT") +
SWITCH(INSTR(Parameters!INPUT.Value,"SENIOR") >= 1, ",SENIOR")
).Remove(0,1)
It will only work for values of ADULT or CHILD or SENIOR, but you can easily add more lines to it if there are other options. It's also probably not great to have "business logic" like this in the report, but it should work. I am testing on SQL Server 2012, so not sure if the Remove() function will work for you. If not you can do something ugly like use the RIGHT() function, passing in the LEN() of that whole string minus 1. If you have trouble with that I can post syntax.