Using Named Parameters From SSRS against Oracle Database

Howdy,

I am using SSRS to develop reports against an Oracle database, I am experiencing problems using named parameters. Here is a screenshot of the data source I have defined for use in my datasets and as you can see it connects successfully:

 Connection Settings.
I have defined a dataset that executes against this datasource and if I supply it with a SQL query that has hard coded values in it then it returs successfully:

 Hard coded example.
I then proceeded to define the following report parameter for use inside the dataset call:

 Report Parameter.
When using the parameter inside the sql query as @Location & then using the "!' to run the dataset I get the following results:

I am prompted for the value:

 Report Parameter Prompt.
...after supplying the value I get the following error:

 Report Parameter Prompt Error.
I have read elsewhere online that the use of ":" instead of "@" is required for queries against Oracle databases from SSRS, but when executing "!" after replacing the "@" with a ":" I get the following error:

 Report Parameter Prompt Colon Error.
The only thing that seems to work is using unnamed parameters by using the "?" character like seem below:

 Report Parameter Prompt Question Mark.
 Report Parameter Prompt Question Mark Result.
...how would I go about using named parameters against the Oracle database, should I use a different data source provider type or something?

Thank you,
SpaceLaika.
SpaceLaikaAsked:
Who is Participating?
 
JacobfwConnect With a Mentor Commented:
It has been noticed as a problem with SSRS designer.

Try to run the query from Edit As Text window and it should run smoothly. Just running it from designer was causing all this errors (e not recognized, varialbes not bound etc etc.)

0
 
SpaceLaikaAuthor Commented:
Thank you very much for your response Jacobfw.

Could you please tell me where I can find the "Edit As Text" window?

Thank you,
SpaceLaika.
0
 
JacobfwCommented:
0
 
SpaceLaikaAuthor Commented:
Oh goodie, thank you I see it was right next to the "!" button.

Thank you,
SpaceLaika.
0
 
SpaceLaikaAuthor Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.