none
Report Builder Expression

    Question

  • Given the following Parameter result:  Pierce, Travis (TP021003)

    Does anyone know of an expression within Report Builder 3.0 that would be able to extract the string in between the parentheticals?  So, TP021003 . . .

    Thanks!

    Travis

    Tuesday, December 11, 2012 9:10 PM

Answers

  • Thanks for the help everyone . . . I ended up writing a second sql statement and populated the results of that into the parameter as a work around.  As always, I appreciate the expert advice here.

    Travis

    • Marked as answer by stwp86 Thursday, December 13, 2012 4:23 PM
    Thursday, December 13, 2012 4:22 PM

All replies

  • Hi,

    If I got you correctly you should use MID function which is similar to substring, then you may need to combine MID with INSTR and LEN functions as well to calculate the substring you need to extract. For instance, if your string is "(123456)" then the expression should be 

    =mid("(123456)",InStr("(123456)", "(")+1,LEN("(123456)")-2)
    So, you can change the above sample for your case.

    Cheers


    Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP


    Tuesday, December 11, 2012 10:20 PM
  • Hello,

    You can use this expression in getting your value under parenthesis.

     

    =replace(Split(Parameters!InputStr.Value,"(").getvalue(1),")","")

     


    - Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

    Wednesday, December 12, 2012 7:26 AM
  • =Mid(Parameters!Name.Value,InStr(Parameters!Name.Value,"(")+1,Len(Parameters!Name.Value)-InStr(Parameters!Name.Value,"(")-1)

    Check this!

    Regards,Eshwar.


    --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

    Wednesday, December 12, 2012 9:54 AM
  • This probably effects it, which I apologize for forgetting.  I am getting the inital value from a parameter and would like to pass the TP021003 to another hidden parameter.  The above formulas do not seem to work under that circumstance.   Any thoughts?  Thank you all for the input thus far.
    Wednesday, December 12, 2012 3:31 PM
  • I am not sure if i got the problem correctly: This is what i tried

    Main Query:

    Select *from (select 'Pierce, Travis (TP021003)' as name,1 value
    Union
    Select 'Pierce Travis(TP021002)' as name,2 value
    Union
    Select 'PierceTravis(TP021004)' as name,3 value) a
    Where name=@Name

    Name Param Dataset:

     select 'Pierce, Travis (TP021003)' as name,1 value
     Union
     select 'Pierce Travis(TP021002)' as name,2 value
     Union
     select 'PierceTravis(TP021004)' as name,3 value

    TPNumber Param Dataset:

    Select Substring(@Name,CHARINDEX('(',@Name,1)+1,LEN(@Name)-CHARINDEX('(',@Name,1)-1) TPNumber

    Create two param's Name and TPNumber(Hidden) use the respective datasets for available values and default values of these parameters. Then use Parameter!TPNumber.value where ever required.

    Regards,Eshwar.


    --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

    Thursday, December 13, 2012 4:29 AM
  • Hello,

    You can go ahead with same expression mentioned in the previous post.

    =replace(Split(Parameters!InputStr.Value,"(").getvalue(1),")","")

    As per your requirement, you need to add one more parameter and make it hidden

     

    Under the properties of the hidden parameter > Default values > Specify values > Add > "replace(Split(Parameters!InputStr.Value,"(").getvalue(1),")","")"  as shown below.

     

     

    Please feel free to revert back if any help needed still, thank you...


    - Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

    Thursday, December 13, 2012 6:18 AM
  • Thanks for the help everyone . . . I ended up writing a second sql statement and populated the results of that into the parameter as a work around.  As always, I appreciate the expert advice here.

    Travis

    • Marked as answer by stwp86 Thursday, December 13, 2012 4:23 PM
    Thursday, December 13, 2012 4:22 PM