Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Analysis Server and Reporting Services (2005)  pass date report parameter (DateTime) to Oracle

Author  Topic  live2ski Starting Member 1 Post Posted - 2009-03-25 : 11:38:14 Hi, I'm new to the forum and am hoping someone can help me out. I'm having troubles passing a date parameter to Oracle. I have a very simple query that I'm testing to try to make this work. I've created a data source to connect to an Oracle 9i database and my query has a date parameter passed to it to retrieve records based on a specified accounting period (parameter = :ACCTPER). When I set up the report parameter as a string data type I have no problems and the query retrieves the records as expected. However, if i try to set the report parameter up as DateTime data type in order to use the calendar control, i cannot get it to work. Here's the query I'm using: select a.org_id, a.major_acct, sum(a.adjusted_amt) from account_actuals a, major_accounts maj, organizations org where maj.major_acct = a.major_acct and a.org_id = org.org_id and a.curr_code = org.op_curr_code and maj.gross_or_net_code = 'G' and a.acct_per_date = TO_DATE(:ACCTPER,'dd/MM/yyyy') group by a.org_id, a.major_acct When I set :ACCTPER to be DateTime data type the query runs fine in the Data screen, but when I try to run it in the Preview screen (and select the date using the calendar control) I get the error message "the value provided for the report parameter 'ACCTPER' is not valid for its type." I'm thinking it may be something to do with the formatting of the :ACCTPER parameter when I select the date from the calendar control but I'm lost as to how to fix it. I don't want the user to have to enter in an accounting period as it will be much easier for them to select a date using the calendar control. Any help is greatly appreciated. bwh