This article describes how you can use Showcase Query or Report Writer to access data from Microsoft Excel, Oracle, or Lotus Domino through the use of ODBC drivers provided by the respective platforms.
Note: These ODBC drivers are NOT provided by ShowCase.
If running Showcase Query or Report Writer against Microsoft Excel files in Excel97 or later, save the file as usual. In pre-Excel97, files must be saved as Microsoft Excel 4.0 worksheets. (either through Excel or as a target file type in Query)
- Highlight the data in Excel that you want to query. This "named range" becomes the database.
- In M/S Excel click Insert | Name >Define; type in a name for the highlighted data then click (Add).
- Repeat steps 1 and 2 as needed, each new name defines a (table) as seen in Query.
- Save Excel sheet. (remember to save as an Excel 4.0 worksheet if the PC is running a version of Excel earlier than Excel97)
Open Microsoft ODBC Administrator:
- Click ADD, choose Microsoft Excel Driver (*.xls).
- Type in the data source name and description.
- Click OK and CLOSE.
To run a Query:
- Within Query, click File | New. Choose the Excel Driver as the data source.
- Browse for Excel file you want to query.
- If the table window is blank, click the drop-down menu to see the tables that were defined in the first steps.
- Continue to set up the query as desired.
Running Showcase Query or Report Writer against Oracle:
You can access your Oracle data on AIX or Windows NT or 2000 via Showcase Query or Report Writer, which enables you to:
- Create and run queries and reports from Oracle data sources.
- Use the Query add-ins for Excel.
Before using Query or Report Writer to access your Oracle data on AIX or Windows NT or 2000, make sure your environment is ready.
- ShowCase - A minimum of version 4.1 with patch 3 or later
- Oracle 8i Release 3 (8.1.7) or later for Windows NT or 2000
- Configure your service and verify your connection in Oracle Net8 Assistant. For help, see your Oracle documentation.
- Oracle ODBC Driver
- Version 8.01.72 or later on Windows NT or 2000 provided by Oracle (www.oracle.com).
- Oracle database
- Must run on AIX or Windows NT or 2000.
Set Up an ODBC Data Source Set up your ODBC data source(s) on all PCs that will use Query, Report Writer.
- On the Windows Start menu, choose: Programs ShowCase Microsoft ODBC Administrator
- To add an Oracle data source, click "Add".
- Choose the Oracle driver (File name SQORA32.DLL), and click "Finish". This opens the Oracle 8 ODBC Driver Configuration dialog box.
- Type a name for your Oracle data source.
- In the TNS Service Name drop-down box, select or type your Oracle service name. Note: If you select the name from the list rather than typing it, there may be an extra space after your TNS Service Name. To prevent errors, delete the space.
- Click the "Test Connection" button.
- In the Oracle8 ODBC Driver Connect box, make sure the Service Name is correct for your Oracle database.
- Enter your user name and password, and click OK to test your Oracle ODBC driver connection. If your connection is successful, continue to the next step. If you receive an error message, consult your Oracle documentation.
- In the Oracle 8 ODBC Driver Configuration dialog box, click "OK". You have set up your Oracle ODBC data source.
Connect to Your New Data Source
- Open Query or Report Writer.
- On the File menu, choose: New Query (or Report)
- In the Data Source dialog box, choose your new data source.
- Connect to your Oracle ODBC driver. Make sure the Service Name is correct for your database.
- Continue to create and run your query.
When running a query against an Oracle data source, the following Query and Report Writer functionality will be unavailable: batch processing, performance analyzing, Warehouse Manager security, and data view creation. Because different ODBC drivers support different SQL functions, a query created against an Oracle data source may not run against an iSeries data source. Likewise, a query created against an iSeries data source may not run against an Oracle data source.
For example, you may use the SQL function "ROUND" with a ShowCase ODBC driver (or data source). The Oracle ODBC driver does not support the same "ROUND" function, so Oracle users will not have access to that function. To check which functions your Oracle driver supports, open ShowCase Query, and run an Oracle query. From the Query menu, choose Columns, and click the New button. The New column dialog box will appear. The functions supported by the Oracle ODBC driver appear in the drop-down box under Functions & variables.