Hi All, I am using Oracle datasource and I wrote one join query in SSRS 2008 Query Designer window (for shared dataset query) If I am removing the below part from my query then it is running fine with results but with below filtering it gives the error message: ORA-01008: not all variable bound. Where :Year is my report parameter, and if I run the same query even with below filtering in Toad oracle then it is running. So, there seems to be something wrong in SSRS query designer with parameter defining. Any idea? to_char(e.VALID_FROM, 'yyyy') < :Year AND to_char(e.VALID_TO, 'yyyy') >= :Year

a similar query works for me in SSRS using an oracle datasource. What are the datatypes for those 2 database fields and that year parameter?

Hi, Please replace the : Year with @Year as shown below. Then in parameters tab of the dataset, map the query parameter @Year to your report parameter Year. to_char(e.VALID_FROM, 'yyyy') < :Year AND to_char(e.VALID_TO, 'yyyy') >= :Year Please lets know if this helps. Regards Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Hi Nehemiah, Datatypes for VALID_FROM and VALID_TO database fields are DATE. And for the "Year" report parameter, I am using properties "No Available Values" and "No Default Values" i.e. user have to type-in the parameter in 4 digit year YYYY format. However, I am filtering my dataset as Year = @Year where, Year database field is extracted as substring:- select '20' || substr(year_name_ref,4,3) as Year Bilal, If I replace :Year with @Year in SSRS query designer then also there is an error ORA 00936:missing expression. Thanks,

using @year would be if you are using sql server datasources, you are correct in using :year by parameter data type I meant, is :year a text, integer, etc the following works for me when setting :year to type text select 'a' from dual where to_char(sysdate, 'yyyy') <= (:year)

Hi Nehemiah, Even for me :Year is of datatype "Text" Still no luck. Below is my structure of query:- SELECT e.CODE, e.NAME, c.AMOUNT, e.valid_from, e.valid_to FROM cycle c, emerg e WHERE c.REF_KEY = e.REF_KEY --and to_char(e.VALID_FROM, 'yyyy') < :Year --and (to_char(e.VALID_TO, 'yyyy') >= :Year and e.VALID_TO <= '31-Dec-2011') You see, if I comment out that part then the query is running but using that part will give error in SSRS query designer. AND if I run the same query even with that "filter" in Toad then also it is running without any issue. Thanks,

im unable to reproduce this problem on my end can you try doing this just in case? and to_char(e.VALID_FROM, 'yyyy') < to_char(:Year)

Perhaps your error isn't related to that part of the query at all... If you replace :Year with '2010' or any other year, does it run? Check the report spec for any residual Parameters that may need to be deleted. Also, I would create a blank report and start from scratch with the existing query. Once you paste in the above query and click to parameterize Year then you can create a simple report to sit on that query and only pull in VALID_FROM and VALID_TO in a Table or List and see if it runs.Brian

Hi, Sorry i did not provide the correct syntax. :Year is not valid within SSRS query editior, replace the : with @. It should be as to_char(e.VALID_FROM, 'yyyy') < @Year AND to_char(e.VALID_TO, 'yyyy') >= @Year Then in the dataset-->parameters tab, map the @Year used above (query parameter) to the report parameter for which a value is entered by users. Please try this and lets know how it goes. RegardsPlease click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Hi Nehemiah, if I replace RHS with < to_char(:Year) then also does not work. Brian, If I put values '2010' instead of parameter :Year then it works i.e. not giving that error atleast. So, it proves that there is something wrong in that part of the query only (problem with ":Year" part) One thing I have to clarify here that I am having some bad data in database i.e. when I extract Year field using SUBSTR function (for ex. select '20' || substr(year_name, 4,2,)) it gives me result as '20us' that is bcas of bad data. I am wondering that might be cause here why I am getting that error "ORA-01008: not all variable bound" in SSRS query designer. Not sure though. The same query works fine in Toad. Then it should work here as well. Thanks,

where are you attempting to do a substr at? I definitely wouldnt use that to extract years from a database field. How about turning the year parameter into an integer type and doing this: to_number(to_char(e.VALID_FROM, 'yyyy')) < :Year

Hi Nehemiah, That's the way requirement is to get the Year from database since we do not have any straight forward field to get "Year" from database. And that extract of year I am using in the above query itself in SELECT clause but I just did not include that in my last post just for a sake of simplicity. I think converting :Year parameter into an integer type won't help since the one "Year" field which I am extracting as above using SUBSTR function is also ultimately STRING and the :Year parameter I have that is also of type TEXT so I think that would be okay. But I can think of trying it out. Also, I am filtering my dataset using expression like, YEAR (database field extracted using substr function as above) = @Year (report parameter)

is year_name a date field?

its VARCHAR2 hi, i tried running that query on another computer and in SSRS query designer it is running well there.

This is an internal SSRS bug it seems. On another computer the same query runs. I have to copy the whole project and paste it into new project it seems. How can i do that bcas simply copy-paste datasets and rdls from Document/VS 2008/Project is not loading the project when I open it as new project.

Another strange behaviour I noted about SSRS is that when I put that query in SSRS query designer it is complaining "error in function arguments: e not recognized" and "error in function arguments: , not recognized" while creating entire new project and pasting that query into new shared dataset also gives that error here as well. But creating it on another computer's SSRS designer it is running well. If anyone has idea how to deal with this wierdo situation? Thanks,

Hi Nehemiah, I think I found the solution to this. My code was fine earlier and there was no issue in the query. It's just that if I run the query from Edit As Text window then it runs smoothly. Just running it from designer was causing all this errors (e not recognized, varialbes not bound etc etc.) This thing to be noticed while dealing with designer. can be reported as SSRS internal bug. Thanks,

Hi, I found the Solution for your problem is Year you passing parameter for two condition has same Year you given Make it as that Year1 = yourparameter1 and Year2 = yourparameter2 In your Dataset create two Parameter Year1 and Year2 assign for the two as same parameter which is going to sent. i.e to_char(e.VALID_FROM, 'yyyy') < :Year1 AND to_char(e.VALID_TO, 'yyyy') >= :Year2

I had this same issue this morning and it took me four hours to figure out, the problem ended up being that because I was using a parameter more than once, I had to map it more than once... The syntax in the query is fine. So in your example, when you map your Parameter Name :Year to your Parameter Value in the Dataset Properties, you may have to list it twice since you use it twice (or however many times you use it).