[savethepennies]

Here’s the situation. I am using SSRS to construct a report using an ODBC connection to NetSuite. We have built plenty of reports this way so I know my setup is good in terms of environment, connection, etc. We are using the latest version of the Connect driver.

Let’s say the query looks something like this:

SELECT C.CUSTOMER_ID, C.NAME, C.FULL_NAME FROM CUSTOMERS C WHERE C.CUSTOMER_ID =

The query runs fine if I provide a hard coded value for the id. The problem is when I attempt to pass the id as a parameter like so:

SELECT C.CUSTOMER_ID, C.NAME, C.FULL_NAME FROM CUSTOMERS C WHERE C.CUSTOMER_ID = ?

Nothing is returned. There is no error but there is also no data. Most of the time I have resorted to building the query a little more unrestricted and then filtering on the report side. In this case there isn’t a way to do that and the dataset is too big if I have no where clause. I’m stuck.

Has anyone successfully run parameterized queries over the ODBC? Is there something quirky about this process I’m missing? Thanks in advance for the feedback.
This is a cached copy. Click here to see the original post.

[stephen.gray]

I’ve done this via excel (using MS query) many times. You’ll be prompted after you run the query to choose the cell with the value in it. Message me if you want to chat more.

[JCirocco]

I am curious how you specify the parameter within the MS/SQL. Any insights?

[stephen.gray]

In microsoft query in the criteria field you can use the square brackets [ ] instead of entering a value. When you then run the query it’ll prompt you to put in a cell reference with the value you want to use e.g. the month. You can then update the month in that cell refresh the query and it’ll return the new months data. If you’re writing the SQL directly its the same as @savethepennies had above e.g. WHERE C.CUSTOMER_ID = ?

Message me if you’ve any issues

[Author]

Posts