locked
changing parameter values before passing to stored procedure in SSRS RRS feed

  • Question

  • Hi,

    I have a need to modify the values of a multi-value parameter (fed by end users during run time via a textbox) in a report before it gets passed to a stored procedure. So for example, if the user enters a number like '4444123', then I should be able to pad zeroes at the beginning and make its length equal to 10 ie. '0004444123'. However, if the user enters something like 'AA4545', then, in that case, the value needs to be passed as it is. In short, if the value starts with a number, then pad it with extra zeroes, else make no change

    I have been thinking of using some custom code for it, but can't figure out a way to return back the value to stored procedure. Handling it through SQL is not possible at this moment.

    Thanks,

    Pratik
    Wednesday, May 18, 2016 1:16 AM

Answers

  • Hi pratikg316,

    Here is the solution for your scenario

    Goto your main Dataset properties -> Parameter -> give the below expression in the parameter value

    =iif(((IsNumeric(Parameters!Test.Value)) AND (len(Parameters!Test.Value)<10)),StrDup(ABS(10-Len(Parameters!Test.Value)),"0")& Parameters!Test.Value,Parameters!Test.Value)

    If you still have any questions, please feel free to ask.

    Please Mark This As Answer if it solved your issue or Vote This As Helpful if it helps to solve your issue.

    Thank you!

    Wednesday, May 18, 2016 4:23 AM
  • its a multi-value parameter so user will be able to enter multiple values; its not mapped to any data set. Here is a screenshot with multiple entered values and error

    Update: Solved it by using the function given here - restrict duplicates in the parameters ssrs

    Wednesday, May 18, 2016 7:45 PM

All replies

  • Hi pratikg316,

    Here is the solution for your scenario

    Goto your main Dataset properties -> Parameter -> give the below expression in the parameter value

    =iif(((IsNumeric(Parameters!Test.Value)) AND (len(Parameters!Test.Value)<10)),StrDup(ABS(10-Len(Parameters!Test.Value)),"0")& Parameters!Test.Value,Parameters!Test.Value)

    If you still have any questions, please feel free to ask.

    Please Mark This As Answer if it solved your issue or Vote This As Helpful if it helps to solve your issue.

    Thank you!

    Wednesday, May 18, 2016 4:23 AM
  • it came back with an error saying 'Type mismatch'. I think the problem lies with that it is a multi-value parameter and hence of the type Array and perhaps what we are returning back is a string.
    Wednesday, May 18, 2016 4:01 PM
  • Hi pratikg316,

    it works for single value only in your question you mention that user will enter the value. so user cannot enter muntivalue function wright.

    can you explain in some what elaborate with screen shot which is helpful to give solution

    if your parameter is multi value means are you mapped any data set with that parameter?

    If you still have any questions, please feel free to ask.

    Please Mark This As Answer if it solved your issue or Vote This As Helpful if it helps to solve your issue.

    Thank you!

    Wednesday, May 18, 2016 4:31 PM
  • its a multi-value parameter so user will be able to enter multiple values; its not mapped to any data set. Here is a screenshot with multiple entered values and error

    Update: Solved it by using the function given here - restrict duplicates in the parameters ssrs

    Wednesday, May 18, 2016 7:45 PM