Enterprise Recon 2.6.1

Enterprise Recon ODBC Connection Guide

Enterprise Recon ODBC connection guides provide basic information about setting up ODBC connections to ER2 from third-party ODBC-ready client applications.

This section discusses the following topics:

Connection Requirements

Using the ODBC Driver to connect a client application to access ER2 data requires the following connection string parameters:

Attribute Description Example
dsn Data source name (DSN). Use the default ER2 System DSN ("Enterprise Recon 64 System" or "Enterprise Recon 32 System") or the created User DSN. dsn=Enterprise Recon 64 System
srv Master Server hostname or IP address. srv=10.52.100.230
uid ER2 user account login name. See Requirements for more information. uid=userA
pwd ER2 user account password. pwd=password123456

Connection Guide for Microsoft Power BI Desktop

The following section describes how to connect Microsoft Power BI Desktop to ER2 data via ODBC.

  1. Launch the Microsoft Power BI Desktop application.
  2. On the Microsoft Power BI Desktop Home tab, click on Get data > More….
  3. In the Get Data dialog, select Other > ODBC and click Connect.
  4. In the From ODBC dialog, select the default ER2 System DSN ("Enterprise Recon 64 System" or "Enterprise Recon 32 System") or the created User DSN from the Data source name (DSN) dropdown list.
  5. Expand Advanced Options and key in the Master Server hostname or IP address in the Connection string input field. For example, srv=10.52.100.230.
    Enterprise Recon ODBC data source connection in Microsoft Power BI.
  6. (Optional) Provide an SQL statement to retrieve specific tables or data sets from the Master Server. See SQL Reference for more information.
  7. Click OK.
  8. When prompted, enter the ER2 user name and password in the ODBC driver dialog.
    Enterprise Recon ODBC data source connection in Microsoft Power BI.
  9. If no SQL query is provided in Step 6, select the ER2 data table(s) in the Navigator dialog.
  10. Click LOAD.

Connection Guide for Microsoft Excel

The following section describes how to connect Microsoft Excel to ER2 data via ODBC.

  1. Launch the Microsoft Excel application.
  2. Select Data > New Query > From Other Sources > From ODBC from the top navigation menu.
  3. In the From ODBC dialog, select the default ER2 System DSN ("Enterprise Recon 64 System" or "Enterprise Recon 32 System") or the created User DSN from the Data source name (DSN) dropdown list.
  4. Expand Advanced Options and key in the Master Server hostname or IP address in the Connection string input field. For example, srv=10.52.100.230.
    Enterprise Recon ODBC data source connection in Microsoft Excel.
  5. (Optional) Provide an SQL statement to retrieve specific tables or data sets from the Master Server. See SQL Reference for more information.
  6. Click OK.
  7. When prompted, enter the ER2 user name and password in the ODBC driver dialog.
    Enterprise Recon ODBC data source connection in Microsoft Excel.
  8. If no SQL query is provided in Step 5, select the ER2 data table(s) in the Navigator dialog.
  9. Click LOAD.

Connection Guide for SAP Crystal Reports

The following section describes how to connect SAP Crystal Reports to ER2 data via ODBC.

  1. Launch the SAP Crystal Reports application.
  2. Open a new or existing report and select Database > Database Expert from the top navigation menu.
  3. In the Database Expert dialog Data tab, under Create New Connection, click to expand the ODBC (RDO) option.
  4. In the ODBC (RDO) - Data Source Selection dialog, select the "Enter Connection String" radio button, and fill in the following in the Connection String input field:

    # dsn=<default System DSN or created User DSN>;srv=<Master Server host name or IP address> dsn=Enterprise Recon 64 System;srv=10.52.100.230
    Enterprise Recon ODBC data source connection in SAP Crystal Reports.

  5. Click Next.
  6. In the ODBC (RDO) - Connection Information dialog, fill in the following information:

    Field Value
    Server Enter a name for the Master Server.
    User ID ER2 user account login name. For example, userA.
    Password ER2 user account password. For example, password123546.

    Enterprise Recon ODBC data source connection information in SAP Crystal Reports.

  7. Click Finish. The selected DSN will be listed as an ODBC (RDO) connection.
  8. Back in the Database Expert dialog, select [ER2 System or User DSN] > Add Command under the list of ODBC (RDO) connections. Click > to add ER2 tables to the report.
  9. In the Add Command To Report dialog, enter a SQL query to retrieve a specific table or dataset. For example, SELECT * FROM SCANREPORT_SUMMARY.
    See SQL Reference for more information.
    Adding SQL commands to query Enterprise Recon Datasource in SAP Crystal Reports.
  10. Click OK.
  11. (Optional) Repeat the previous three steps to add additional tables / data from the ER2 Master Server.
  12. Click OK.

Connection Guide for Windows PowerShell

The following section describes how to use Windows PowerShell to access ER2 data by creating a connection to the ER2 ODBC data source.

  1. Launch the Windows PowerShell console window and execute the following commands.
  2. Initialize a new instance of the OdbcConnection class.

    $masterServerConnection = New-Object System.Data.Odbc.OdbcConnection

  3. Specify the connection string to connect to the ER2 data source:

    # $masterServerConnection.connectionstring = "DSN=<default System DSN or created User DSN>;SRV=<Master Server hostname or IP address>;UID=<User account login name>;PWD=<User account password>" $masterServerConnection.connectionstring = "DSN=Enterprise Recon 64 System;SRV=10.52.100.230;UID=admin;PWD=admin123456"

  4. Open a connection to the ER2 data source with the connection string properties defined in the previous step.

    $masterServerConnection.Open()

  5. (Optional) Get the ER2 data source properties.

    Write-Output $masterServerConnection

  6. (Optional) Get a list of data tables.

    $masterServerTables = $masterServerConnection.GetSchema("Tables") | SELECT INTERNAL_TABLES.TABLE_NAME
    ForEach ($table in $masterServerTables) { if (($table -match "DATA_") -or ($table -match "SCANREPORT_")) { Write-Output $table } }

  7. Execute a SQL query to retrieve a specific table or dataset. See SQL Reference and Data Tables for more information.

    $sqlCommand = New-Object System.Data.Odbc.OdbcCommand("SELECT * FROM DATA_GROUP",$masterServerConnection)
    $dataTable = New-Object System.Data.DataTable $dataTableAdapter = New-Object System.Data.Odbc.OdbcDataAdapter($sqlCommand) $numRecords = $dataTableAdapter.fill($dataTable)
    Write-Output "Number of Groups: ${numRecords}" Write-Output $dataTable

  8. Close the connection to the ER2 data source.

    $masterServerConnection.Close()

Connection Guide for Tableau Desktop BETA

The following section describes how to connect Tableau Desktop to ER2 data via ODBC.

  1. Create a User DSN to be used with Tableau Desktop.
  2. Launch the Tableau Desktop application.
  3. From the Connect sidebar, go to To a Server > More... > Other Databases (ODBC).
  4. In the Other Databases (ODBC) dialog, select the User DSN created in Step 1 from the (DSN) dropdown list and click Connect.
  5. Check that the Connection Attributes (Server, Username) for the selected User DSN are as expected.
    Enterprise Recon ODBC User DSN connection attributes in Tableau Desktop.
  6. Click Sign In.
  7. In the Table section in the left panel,
    • Click the search icon to list and select all ER2 data tables, or
    • Click on New Custom SQL to enter a SQL query to retrieve a specific table or dataset.
      See SQL Reference for more information.

Third-Party Software Disclaimer

Any information or links to third-party software available on this website are provided "as is" without warranty of any kind, either expressed or implied and such software is to be used at your own risk.

The use of the third-party software information and links on this website is done at your own discretion and risk and with agreement that you will be solely responsible for any damage to your computer system or loss of data that results from such activities. Ground Labs will not be liable for any damages that you may suffer with downloading, installing, using, modifying or distributing such software. No advice or information, whether oral or written, obtained by you from us or from this website shall create any warranty for the software.

Ground Labs does not provide support for these third-party products. If you have a question regarding the use of any of these items, which is not addressed by the documentation, you should contact the respective third-party item owner.


BETA This is a Beta feature. Ground Labs does not give any warranties, whether express or implied, as to the suitability or usability of its Beta features.

If you have any feedback on bugs or usability of the Beta feature, please email your feedback to product@groundlabs.com. Your assistance on this is highly appreciated.