Exposing Oracle Synonyms / Tables in SSRS RRS feed

  • Question

  • User-137565683 posted

    I can set up a connection as a datasource to an Oracle Database Schema.  In the Schema. The schema contains synonyms. But when adding a dataset in Report Builder, the Table option is grayed out.  I can access the synonyms (table names) through a select query, and then I can refresh the fields.  Ideally I want all of the choose in a drill down to tables and columns much like you do in SSMS.   Or at least be able to specify a table name in the Table drop down and then have it retrieve all of the columns as it refreshes the dataset.  Is there some type of grant I need to do to expose the table (synonym)  to SSRS / Report Builder?  The option is to create views, which I would rather not since synonyms are already available.

    Other tools like SQL Navigator or SQL Developer can see the synonyms with the same logon as SSRS datasource. I would just like to see those exposed in SSRS / Report Builder in the Table Dropdown for the dataset.  Again, currently the Table option in the embedded dataset is grayed out when I select the datasource.


    Tuesday, December 12, 2017 4:27 PM

All replies

  • User347430248 posted

    Hi aspdev0978,

    you can confirm that you follow the steps below to fetch the data.

    To create a dataset for an Oracle data source

    1. In the Report Data pane, right-click the name of the data source that connects to an Oracle data source, and then click Add Dataset.

    2. In the Query page of the Dataset Properties dialog box, type a name in the Name text box or accept the default name.

    3. In the Data source text box, verify that the name of the data source you right-clicked appears.

    4. Verify that Text is selected in the Query type box. Click Query Designer. The text-based query designer opens.

    5. Type or paste a query from the clipboard directly into the Query pane, or import an existing SQL query from a file or from another report. For more information, see Text-based Query Designer User Interface (Report Builder 2.0).

    6. Click OK.

      The dataset and its field collection appear in the Report Data pane under the data source node.


    How to: Retrieve Data from an Oracle Data Source (Report Builder 2.0)



    Wednesday, December 13, 2017 9:31 AM
  • User-137565683 posted

    Thanks for the instructions.  Being that it is Report Builder,   When I select my datasource, I want it to be able to select table button (see below)  select the table from the drop down.  And when I select the table, it will pull in the columns, so they can be dragged and dropped to the report.  Having a blank text and writing the select statement without being able to drill down to a table and columns is less intuitive.  The same with selecting stored procedures.  I am really looking for what to do on the Oracle side to have the table button below become active, and the tables exposed from the schema.  It may just be specifying a default schema in the connect string in the datasources itself.  I really don't want to create a model or have to create views.  Since this is a SELECT only type of schema.   Thanks!  Edit sorry it didn't attach the image I inserted in the dialog.  So you will have to open that dialog and you will see where it indicates table...  In my case it is grayed out.

    Wednesday, December 13, 2017 8:57 PM
  • User347430248 posted

    Hi aspdev0978,

    I will try to find the solution and let you inform about it, once I get any useful information on this issue.

    Thanks for your understanding.




    Tuesday, December 19, 2017 8:55 AM