Using Parameters in Reporting services against an Oracle DB
Hi, I have created my first report using reporting services and all was going fine until I tried to add parameters. All the data was displaying just fine. I found several articles that indicated the paramenter in the query needs to be formatted such as :Fiscal_Year with the : instead of the @. However, I have tried that and now my report fails with ORA-01008 not all variables bound. I have tried changing around the connection to 'Oracle, OLD DB, etc and nothing seems to work. I tested out the connections and they all worked fine. Here is the Query. It's pretty simple: SELECT ORDER_W_LINE_NUMBER, FISCAL_YEAR, PERIOD, PERIOD_NAME, INDUSTRY, SHIP_CUST_GROUP, SHIP_CUST_SUBGROUP, GL_PRODUCT, GL_DIVISION, PRODUCT_GROUP, SF_BRAND, BUDGET, ACTUAL, VARIANCE FROM JIH_SOP_BUD_ACT_VER1_V WHERE FISCAL_YEAR = :FiscalYear AND SHIP_CUST_SUBGROUP = :ShipCustSubgroup What am I missing? Thanks, Trish
November 10th, 2010 6:09pm

I dont have any problems using Oracle parameters. What are the datatypes for fiscal_year and SHIP_CUST_SUBGROUP and what are the corresponding parameter value types? have you tried a simple test like: select * from dual where 'a' = :value
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2010 7:47pm

hi, I'm having a problem with this. So for example I have a query like the one below: SELECT ORDER_W_LINE_NUMBER, FISCAL_YEAR, PERIOD, PERIOD_NAME, INDUSTRY, SHIP_CUST_GROUP, SHIP_CUST_SUBGROUP, GL_PRODUCT, GL_DIVISION, PRODUCT_GROUP, SF_BRAND, BUDGET, ACTUAL, VARIANCE FROM JIH_SOP_BUD_ACT_VER1_V WHERE FISCAL_YEAR = :FiscalYear AND SHIP_CUST_SUBGROUP = :ShipCustSubgroup When I ran my SSRS and pass a value to parameter :ShipCustSubgroup no data is generated. But if I chage my data set and replace :ShipCustSubgroup with a value, my report is able to generate the data. I check my parameters in the data set and everythin is bound correctly. Am i missing something? please help.
March 25th, 2011 9:55am

Have you tried IN (:parameter) instead of = :parameter c?
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2011 5:01pm

You might trim the values on each side of the = if they are string/varchar. There might be leading/trailing blanks on one side of the compare. Oracle and RS do not play well together sometimes.
March 30th, 2011 5:27pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics