Report error - Invalid Number

Question

I am passing multiple value to a oracle sql query from the report. SQL looks like

columna in ( :inparam)

In the report I have multi value parameter and expression defined 

=JOIN(Parameters!inparam.Value,",")

When I execute the report I am getting an error ora-01722 invalid number. Any idea on how to fix this?

Answer 1

Hi SGeek,

I'm not an Oracle guy so please pardon my ignorance here, but this issue could be due to data mismatch in parameters, e.g. if you trying to assign a string value to a numeric parameter.

In order to narrow down your analysis, could you please hard code the values and check the SQL which is getting fired against your Oracle database. If you see the problem, you might need to write dynamic SQL so that it passes the right value to your Oracle engine after getting parameters from the SSRS's JOIN expression.

HTH

Regards,
Santosh

---

http://microsoftbizintel.wordpress.com/

Answer 2

Hi SGeek,

I agree with Santosh that the issue should occur because the mismatch between the data types of the parameter values passed to the query and the target column. If you have specified the data type of the parameter to Integer, please try to modify the WHERE clause of the query as follows:

WHERE columna in (" +  Join(Parameters!inparam.Value,", ") + ")"

References:

• ORA-01722
• SSRS 2005 Problem with Multivalued Report Parameters for Oracle 8i
• SSRS 2008 - How to pass an integer Multi Value Parameter to an Oracle Query

Hope this helps.

Regards,

---

Mike Yin
TechNet Community Support