CODEWITHSUNDEEP
oracle-databasereporting-servicesssrs-2008
SarekOfVulcan
SarekOfVulcan

Reputation: 1358

How do I pass a parameter into the Query.CommandText in SQL Server Reporting Services?

I have the following query against an Oracle database:

select * from (
    select Person.pId, Person.lastName as patLast, Person.firstName as patFirst
                , Person.dateOfBirth
            , Medicate.mId, Medicate.startDate as medStart, Medicate.description
                , cast(substr(Medicate.instructions, 1, 50) as char(50)) as instruct
            , ml.convert_id_to_date(Prescrib.pubTime) as scripSigned
                , max(ml.convert_id_to_date(Prescrib.pubTime)) over (partition by Prescrib.pId, Prescrib.mId) as LastScrip
            , UsrInfo.pvId, UsrInfo.lastName as provLast, UsrInfo.firstName as provFirst
        from ml.Medicate 
            join ml.Prescrib on Medicate.mId = Prescrib.mId
                join ml.UsrInfo on Prescrib.pvId = UsrInfo.pvId
            join ml.Person on Medicate.pId = Person.pId
        where Person.isPatient = 'Y' 
                and Person.pStatus = 'A'
            and Medicate.xId = 1.e+035
                and Medicate.change = 2
                and Medicate.stopDate > sysdate
                and REGEXP_LIKE(Medicate.instructions
                    , ' [qtb]\.?[oi]?\.?[dw][^ayieo]'
                        || '|[^a-z]mg?s'
                        || '|ij'
                        || '|[^a-z]iu[^a-z]'
                        || '|[0-9 ]u '
                        || '|[^tu]hs'
                    , 'i')
        order by ScripSigned desc
) where scripSigned = lastScrip and scripSigned > date '2011-01-01'

I have a Report Parameter defined, DateBegin, defined as a DateTime, and I've associated it with a Query Parameter also called DateBegin. I just can't figure out how to replace "date '2011-01-01'" with "DateBegin" so that the blinkin' thing actually works. Thanks!

Upvotes: 1

Views: 977

Answers (1)

user359040
user359040

Reputation:

Use the Oracle format for parameters - so use the following:

) where scripSigned = lastScrip and scripSigned > :DateBegin

(The @ sign is used in SQLServer to identify SQLServer variables.)

Upvotes: 3

Related Questions