Monday, 4 January 2010

SSRS - adding parameters to a dataset with an Oracle data source

When you are creating a SQL Server Reporting Services (SSRS) report and you want to add a dataset that connects to an Oracle dataset using OLE DB and you want to add parameters to the PL/SQL query, you might make the mistake of using the same syntax as in T-SQL which is @param. The PL/SQL version is :param.


For example, in T-SQL we would write:


SELECT   id, name
FROM
   myTable
WHERE
   cond   =   @param


while in  PL/SQL we would write:


SELECT   id, name
FROM
   myTable
WHERE
   cond   =   :param

Also, don't forget to pass the report parameter variable value to the dataset parameter on the dataset properties tab aptly called "Parameters". See below for the screen shot in 2008.




No comments:

Post a Comment