Using parameters in MSRS with Oracle database

[globalbear]

I am using Reporting Services to connect to an Oracle database. How do I manage to use parameters with Oracle?

Example: I want to select all transactions from a certain period. I set up a parameter called Period.

If I would have an SQLServer DB it would be fine to filter the Period-field with @Period. This does not work in Oracle however. I found out that I should use : instead of @, thus I try eriod instead as a filter. This works fine on the data-tab but I cannot render a complete report.

The error message reads:
"ORA-01008: not all variables bound"

Hmmmm. How do I "bound" a variable (=parameter)?

 

[MisterMo]

colud you post a sample of the query?

how do you bind the parameter in the report paramenter section?

is it a range? a single value?

-Mo

 

[globalbear]

This query will give me all transactions for a specific journal in the general ledger:

SELECT
COMPANY, FISCAL_YEAR, ACCT_PERIOD,CONTROL_GROUP,LINE_NBR,
ACCT_UNIT, ACCOUNT, BASE_AMOUNT, R_SYSTEM
FROM
LAWSON.GLTRANS
WHERE
(COMPANY = 10) AND (CONTROL_GROUP = 2) AND (R_SYSTEM
= 'GL') AND (ACCT_PERIOD = 2) AND (FISCAL_YEAR = :yr)

Notice the parameter :yr. This will prompt me to populate :yr with a value, for example 2007.

This works fine on the data tab but not on the preview tab. Somehow MSRS does not understand that :yr is a parameter. This is where the "binding" comes in. I haven't "bound" anything yet, I don't understand how to do that.

 

[MisterMo]

ok lets start from the beginning,

which connection do you use, for oracle you have two options

1) oracle native driver, with this one your parameter have to start with : like your :yr

@)OLE for oracle which takes different type of params they are entered in your query like this (?)

depending on the connection one will work the other won't.

is this what you're experiencing?

-Mo

 

[globalbear]

OLE DB

 

[MisterMo]

I strongly suggest you use oracxle native drivers, but if you are using OLEDB then your parameters will have to be (?)

that means that there will be no names they will be sequencial eg

select * from table
where field1 = (?)
and datefield2 between (?) and (?)

they will show on your report parameter panel as (?) and you would have to guess what type of binding to use.

-Mo