Parameter override

nvkumar
edited February 11, 2022 in Analytics #1

Hi,
While calling the stored procedure getting error
****org.eclipse.birt.report.engine.api.EngineException: Cannot execute the statement. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object. SQL error #1:The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. ; com.microsoft.sqlserver.jdbc.SQLServerException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


In report two parameters are there, one is string other one is integer which is financial year.

Due to above issue my back-end db team updating the stored procedure financial year parameter to string.
User will enter only year like 2017 but I have to pass the value as date to stored procedure by hard coding value of date like 10/1+"Year"(10/1 value is hard coded)and appending the 2017 to parameter and passing to stored procedure.

May I know how we can archive this with example.

Best Answers

  • Can you help answer this question?

    We've noticed this question is over 30 days old and hasn't received a response. We're turning to you, the community, to help answer it.

    This generic response is intended to prompt discussion in this post. The question remains open to your answers, suggestions, and best practices.

    If you posted this question and were able to resolve the issue, please share your solution here with others. If you still need additional help, though, please let us know. Your question and its resolution are important to us, and we want to help.

    David Sciuto

  • If you want the user input to stay the same, you need additional processing before passing it into the data set. There's a few ways to do this, but probably the easiest with minimal changes is to alter the parameter expression in your data set. Instead of linking directly to a parameter (which I assume is what you're doing now), change it to none and add script in the expression like so (ignore the name, as this is just a random report I have open):

    You'll need to make sure it's a string, and structure the date in a way that the database will expect. Try that out and see how it goes.

    Warning No formatter is installed for the format ipb

Answers

  • Can you help answer this question?

    We've noticed this question is over 30 days old and hasn't received a response. We're turning to you, the community, to help answer it.

    This generic response is intended to prompt discussion in this post. The question remains open to your answers, suggestions, and best practices.

    If you posted this question and were able to resolve the issue, please share your solution here with others. If you still need additional help, though, please let us know. Your question and its resolution are important to us, and we want to help.

    David Sciuto

  • If you want the user input to stay the same, you need additional processing before passing it into the data set. There's a few ways to do this, but probably the easiest with minimal changes is to alter the parameter expression in your data set. Instead of linking directly to a parameter (which I assume is what you're doing now), change it to none and add script in the expression like so (ignore the name, as this is just a random report I have open):

    You'll need to make sure it's a string, and structure the date in a way that the database will expect. Try that out and see how it goes.

    Warning No formatter is installed for the format ipb