SSRS Parameters OLEDB

  • ijaz32

    SSC Enthusiast

    Points: 114

    Hi

    I am using OLEDB to connect to an Oracle. I have two parameter which I want to use in my dataset query. What should I put for the parameter.

    Dataset Query:

    Parameter positions are indicated as YearParameter and MonthParameter

    SELECT        DATA_FACT2.HMO_ID, DATA_FACT2.HRS_AUTH, DATA_FACT2.HRS_PAID, DATA_FACT2.Year, DATA_FACT2.Month, DATA_FACT2.RPT_TYPE

    FROM            DATA_FACT2

    WHERE        (DATA_FACT2.Year = YearParameter) AND (DATA_FACT2.Month IN ('04', '05', '01', '02', '03')) AND (DATA_FACT2.Month = MonthParameter) AND (DATA_FACT2.RPT_TYPE = 'M') OR

    (DATA_FACT2.Year =  YearParameter) AND (DATA_FACT2.Month IN ('06', '07', '08', '09', '10', '11', '12')) AND (DATA_FACT2.Month = MonthParameter) AND (DATA_FACT2.RPT_TYPE = 'M')

    ORDER BY DATA_FACT2.HMO_ID, DATA_FACT2.Month

    Thank you very much.

    Ijaz

  • Jeffrey Williams

    SSC Guru

    Points: 88603

    You really should use the Oracle Connection - and make sure you have installed the Oracle client on your workstation and the SSRS server.

    https://docs.microsoft.com/en-us/sql/reporting-services/report-data/oracle-connection-type-ssrs?view=sql-server-ver15

    If using the Oracle provider, your parameters would be defined with the prefix ':'.  For example:

    SELECT ... FROM ... WHERE p1 = :p1

    If you must use OLEDB, the parameters are positional and denoted with a ?.  For example:

    SELECT ... FROM ... WHERE p1 = ?

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply