SSRS Parameters OLEDB

• ijaz32

  SSC Enthusiast

  Points: 174

  More actions

  February 12, 2020 at 9:26 pm

  #3725386

  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: 90303

  More actions

  February 12, 2020 at 10:19 pm

  #3725399

  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
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

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