External Data Connections Advanced Options

Warning

The following options impact how DAS finds and queries tables in a database. Misconfiguration here can cause reports that use the connection to fail.

Custom Schema Discovery

Note

This option will not need to be utilized for most external connections.

DAS queries databases metadata tables to discover the tables that can be opened. These settings allow advanced configuration of this process. This will also effect how qualified table names are created for table alias mappings. The following are a few of the known scenarios where the Schema Discovery option should be utilized:

  • If the table alias mappings should not be added on certain parts to the qualified table name.
    • An example of this is when a database provider adds the current user's role name as the schema. This means a user running in another role could not use the report. Assuming the provider supports running without this qualification (like Oracle NetSuite®), use this configuration to not allow the schema to be added.
  • If DAS does not recognize what the schema or catalog name should be.
    • An example illustrating this situation occurs when reviewing the automatically generated table alias mappings, which may lack proper qualification. This could manifest in various ways, ranging from the absence of qualification to instances where the database column is used as the schema, and vice versa.

To utilize this feature select the Use Custom Column Names to turn it on.

The columns that correctly identifies what should be used for these comes from the database's metadata. To identify the correct column name for the text box, navigate to External Data Connections and open the connection to access all the tables. Then right click on a column and select Hide and Show Columns. A dialog should pop up similar to what is shown below; the example is from a Microsoft SQL Server connection.

Then from this screen you can see what the column alias is to use for these text boxes. In this case, for the schema column it would be TABLE_SCHEMA and for the database it would be TABLE_CATALOG.

Any textbox that is left empty, that part of the table qualification will not be used by DAS to query data. The database textbox cannot be filled out if the schema textbox is left empty, since qualification goes [DATABASE].[SCHEMA].[TABLE]. However, the database textbox can be empty while schema is filled out.

Oracle NetSuite® Connections

NetSuite is a known example of the first scenario that was listed above in Custom Schema Discovery of when to use the Custom Column Names option and to not qualify the generated table aliases. Because it is known, DAS will configure this options automatically upon creation.

Custom Quotes

Note

This option will not show up for most external connections. If visible, any input provided in the custom quotes text boxes will be used to automatically quote all tables generated for table alias mappings and will be used on column names during database queries.

This option will show if we do not know what default quoting to use for the external connection. The known quotes for external connections are the following:

  1. Microsoft SQL Server
  2. Microsoft Access
  3. Oracle
  4. DB2
  5. MySQL
  6. Oracle NetSuite®
  7. Any CData provider