Bringing Back Data from an External Connection

External data connections can be used as the report data source or as a data source for a Table Lookup calculation. The basic query and retrieval methods used for JDE data sources will be the same for these connections. The data sources differ in terms of authentication and general reliability. This article will cover some of these considerations.

Excel File Locking

A very common data source for reports in Data Access Studio are Excel files using the Microsoft Access Runtime (ACE OLEDB) driver. One of the most common issues with this data source is that querying it will fail when the file is open in Excel. This is a limitation of the ACE OLEDB driver and is not something that can be changed by ReportsNow. To work around this limitation, make sure Excel files are closed or the files are marked in Windows as read-only before they are opened and before running a report that uses them as a data source.

Authentication Prompts for OAuth 2 and Per-User Server Connections

Certain external data connections require interactive authentication by end users. This includes any connection that has its authentication type set to OAuth 2 and server side connections that are configured to authenticate for each user (per-user server connections). For such connections, the user may be prompted to authenticate when the report is opened or when the connection is first used during a run of the report. The user only needs to successfully sign-in to a data source once and, after that, DAS will store these credentials until the credentials fail (either due to an OAuth token expiration or a password change).

The data sources that require authentication will be listed under the 'Authenticate' dropdown on the report's, ProReport's, or dashboard's tool bar. This will show for both private and published reports within Data Access Studio. If the dropdown does not exist, then the report does not use connections that require authentication.

Note

ProReports and dashboards published to mobie will not show the 'Authenticate' dropdown because mobie published objects only connect to the single mobie database. Instead, the authentication described here will occur when reports backing mobie-based ProReports and dashboards are run during the web publishing process.

Controlling When Authentication Prompts Occur

External data Connections used by the main report and the main report's variables must always be authenticated by the user when the report is opened. However, if the report uses a Table Lookup calculation, the user can choose to authenticate the connection when the report is opened or when the Table Lookup calculation is run. This can be controlled by the 'Authentication timing' option in the Table Lookup and Burst calculation settings.

The 'Authentication timing' setting has two options.

• On report open - Indicates to authenticate the connection when the report is opened. This is the default setting.
• When first used - Indicates to authenticate when the connection is first used.

Use the When first used option for report-level table lookups and bursts that are not always required when running. For example, there might be a drilldown to a report that uses an OAuth connection that only applies for certain business units. Such a burst would be a good candidate for the When first used option where only the users accessing those business units would need to authenticate.