Using Named Parameters From SSRS against Oracle Database

Posted on 2011-10-24
Last Modified: 2012-05-12

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. would I go about using named parameters against the Oracle database, should I use a different data source provider type or something?

Thank you,
Question by:SpaceLaika
    LVL 7

    Accepted Solution

    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.)


    Author Comment

    Thank you very much for your response Jacobfw.

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

    Thank you,
    LVL 7

    Expert Comment


    Author Comment

    Oh goodie, thank you I see it was right next to the "!" button.

    Thank you,

    Author Closing Comment


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Via a live example, show how to take different types of Oracle backups using RMAN.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now