Last post Dec 19, 2017 08:55 AM by Deepak Panchal
Dec 12, 2017 04:27 PM|aspdev0978|LINK
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.
Dec 13, 2017 09:31 AM|Deepak Panchal|LINK
you can confirm that you follow the steps below to fetch the data.
In the Report Data pane, right-click the name of the data source that connects to an Oracle data source, and then click
In the Query page of the Dataset Properties dialog box, type a name in the
Name text box or accept the default name.
In the Data source text box, verify that the name of the data source you right-clicked appears.
Verify that Text is selected in the
Query type box. Click Query Designer. The text-based query designer opens.
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).
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)
Dec 13, 2017 08:57 PM|aspdev0978|LINK
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.
Dec 19, 2017 08:55 AM|Deepak Panchal|LINK
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.