Running a Query Using Microsoft Excel

This section discusses how to use Microsoft Excel to create and run a query.

To run a query using Microsoft Excel:

1. From the Data menu, select Get External Data.

2. Select Create New Query.

3. On the Databases tab, select the appropriate data source (for example, JD Edwards EnterpriseOne Local or JD Edwards EnterpriseOne ODA).

   Because Excel uses file data sources, the ODA data source you set up in the 32-bit ODBC Administrator does not appear on the list of databases. You should create a File-type Data Source by selecting New Data Source and then follow the procedures for setting up a data source.

   When you select the ODA data source, you might need to log on to JD Edwards EnterpriseOne to use the ODA driver. Once you log on, you will not see the Solution Explorer because it is only activated so that the ODA driver can check security and environment mappings.

   The Excel Query Wizard displays a list of available tables in the JD Edwards EnterpriseOne data source. Expanding any table name shows the available columns or fields in each table. If you are using the ODA driver, you see long descriptions of each field (for example, DateUpdated). If not, you see the alpha codes for the fields (for example ABUPMJ).

4. To translate field and column names from the JD Edwards EnterpriseOne alpha codes, use the F9202 table. Select all rows and sort (on FRDTAI) to create a cross-reference.

   The first two letters of all JD Edwards EnterpriseOne column names are the application code, and the remaining letters are in this table as a suffix.

5. Finish building your query with Query Wizard and save the query.

6. Run your query and review it in Excel or MicroSoft Query.

   After you run a query from Excel, if you view the results using Microsoft Query, results are returned quickly. MicroSoft Query selects a page at a time. If you are working with a large result set, you should close JD Edwards EnterpriseOne and any applications that require a lot of memory so that you can more quickly navigate through the records. If you convert the query results directly into a spreadsheet instead of into Microsoft Query, the process might take significantly longer, and you cannot view the results until the entire file builds.

To verify the outcome of each query, you should run each one first using the non-ODA JD Edwards EnterpriseOne data source and then use the ODA data source and compare the results.