Oracle with Reporting Services. "illegal variable name/number"

I'm developing a report with SQL Server Reporting Solutions (obviously ;)) against an Oracle DB and have run into a problem.

This is the code that I'm using for the dataset query that my report is to be based on:

="SELECT * FROM IRMFE_*************_V WHERE " &
IIf(Parameters!Invoice_No.Value(0)<> "ALL", "SUPPLIER_INVOICE_NO IN (:InvNo)", "SUPPLIER_INVOICE_NO LIKE '%'")

The parameter ":InvNo" is a paramter in the report and the user can select multiple values. I also included an "ALL" option manually in the query for the parameter. The query works fine with single or multiple options but if I select "ALL" I get the following error:

"Query execution failed for dataset 'FREIGHT'.
ORA-01036: illegal variable name/number'

There seems to be no problem when I manually run the query (SELECT * FROM IRMFE_*************_V WHERE SUPPLIER_INVOICE_NO LIKE '%')

The reason I used "SUPPLIER_INVOICE_NO LIKE '%'" is because I'll be adding a few more parameters and wanted to avoid any problems with adding "AND" between the conditions.

Any resolution or suggestion on this would be much appreciated guys.
Kev.After 1/2 of bangin my head against the wall I've resolved it. The reason it wasn't picking it up in the first place was that the check should have been:

IIf(Parameters!Invoice_No.Value(0)<> "'ALL'"

instead of

IIf(Parameters!Invoice_No.Value(0)<> "ALL"

I found Chris Hays solution and will just use that though.

Oracle Data Source Problem: Cannot connect during deployment. Help!

I am developing an SSAS project which looks at an Oracle database but I cannot for the life of me get it to process.

This is one of those awful Oracle connectivity problems!

Now, in the DataSource, I can edit and the test connection works perfectly. Every other application can connect to the Oracel server correctly. The problem comes when I try to deploy my project. I get errors left right and centre. They even change between attempts! Most common is TNS name resolution error:

Error 1 OLE DB error: OLE DB or ODBC error: ORA-12154: TNS:could not resolve the connect identifier specified. 0 0

Yet, as I have said, when I test the connection everything is OK. And I can use the same connection details in every other application. What is so different about the deployment operation that means it cannot find the connection configuration?

I have tried all the providers, impersonation etc. FYI: the main method for getting the Oracle connection details is through LDAP.

OK.. I found a work around.. I put the connection details directly into the tnsnames.ora file and everything went smoothly again!

So, why does the deployment processing not look at sqlnet.ora when everything else does, including the UI/designer? I am just worried things might get difficult in a production environment.

The other interesting thing is that this has worked from my other machine, so their must be some Oracle config somewhere? Any Oracle connectivity experts out there?

|||

If you running 64 bit be aware of this thread as well --> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1