Enterprise Recon ODBC Connection Guide

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

This section discusses the following topics:

• Connection Requirements
• Connection Guide for Microsoft Power BI Desktop
• Connection Guide for Microsoft Excel
• Connection Guide for SAP Crystal Reports
• Connection Guide for Windows PowerShell
• Connection Guide for Tableau Desktop

Connection Requirements

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

Connection Guide for Microsoft Power BI Desktop

The following section describes how to connect Microsoft Power BI Desktop to Enterprise Recon 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 Enterprise Recon 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.
   
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 Enterprise Recon user name and password in the ODBC driver dialog.
   
9. If no SQL query is provided in Step 6, select the Enterprise Recon 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 Enterprise Recon 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.
   
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.
   
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 Enterprise Recon 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
   

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	Enterprise Recon user account login name. For example, userA.
   Password	Enterprise Recon user account password. For example, password123546.

   

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 Enterprise Recon 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.
   
10. Click OK.
11. (Optional) Repeat the previous three steps to add additional tables / data from the Enterprise Recon Master Server.
12. Click OK.

Connection Guide for Windows PowerShell

The following section describes how to use Windows PowerShell to access Enterprise Recon data by creating a connection to the Enterprise Recon 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 Enterprise Recon 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 Enterprise Recon data source with the connection string properties defined in the previous step.

   $masterServerConnection.Open()

5. (Optional) Get the Enterprise Recon 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 Enterprise Recon data source.

   $masterServerConnection.Close()

Connection Guide for Tableau Desktop BETA

The following section describes how to connect Tableau Desktop to Enterprise Recon 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.
   
6. Click Sign In.
7. In the Table section in the left panel,
   • Click the search icon to list and select all Enterprise Recon 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.