October 7, 2008 at 12:43 pm
We recently moved to a student information system that uses Oracle 10g as the backend. For the past 3 years, I've been using SSRS for custom reports because the old backend was MSSQL and I loved working with it. I'm trying to make the teh SSRS-ORacle thing work.
I've create 3 shared data sources using the following connectivity: 1)OLE DB 2)ODBC & 3) Oracle
All test connections work. When I run the SQL below I get data returned but NOT with the OLE DB data source:
select
s.Enrollment_SchoolID as From_Sch,
s.schoolid as To_Sch,
cast(s.student_number as int) as Student_Number,
s.grade_level as Grade,
rtrim(s.LastFirst) as Name,
rtrim(s.ethnicity) as Eth,
rtrim(s.gender) as Gen,
rtrim(s.home_phone) as Phone,
rtrim(s.street) || ', ' || rtrim(s.city) || ' ' || upper(rtrim(s.state)) ||' ' || rtrim(s.zip) as Address,
s.entrydate as Entry,
s.exitdate as Exit
from
students s
where
exitdate between '09-SEP-08' and '03-OCT-08'
order by
s.schoolid,
s.lastfirst,
s.grade_level
My issue is that I need to turn the dates in the WHERE clause to parameters ( .... @from_date and @to_date), I get "ORA-000939: missing expression". Any guidance would be appreciated.
I don't want to learn Crystal Reports.
October 8, 2008 at 10:57 am
Oracle does not use the @start_date logic for parameters, they use :start_date. At my previous job we had several hundred reports in SSRS running against an Oracle environment and depending upon which access method we used - OLE DB, ODBC or Oracle driver we had to use either the :start_date logic or ? if I recall correctly so I would try one of those methods.
October 8, 2008 at 11:01 am
Thanks for the reply. I eventually found the "?" works with ODBC. I'll try the ":" with the Oracle drive. Are you aware of any good documentation that directly addresses SSRS and Oracle?
October 8, 2008 at 11:50 am
Glad to hear you got the parameters working with ODBC. Unfortunately no I don't know of any other documentation beyond books on line or Oracle's documentation regarding PL SQL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply