Creating an External Lookup from a Report

Using Monarch Classic’s External Lookups window, you can create an external lookup to add columns from an external database to the Monarch Classic table.

For example, let’s say you have a monthly sales report that details monthly sales to each of your customers. The report includes each customer’s name and address along with the items purchased by the customer during the month. Using Monarch Classic, you could load the report and extract all of the data it contains, yielding a table that lists all customers and all items sold to each customer. If you could then combine this information about your specific customers with some demographics (median household income broken out by postal code or street address, for example) you could perform some interesting analysis revealing purchase patterns across your product line. You could then use the resulting information to market specific products to geographic areas that match the demographic of those who tend to purchase that product.

Here’s where an external lookup comes in. If you have access to a database detailing demographics broken out by city, postal code, street, or any other field that is present in your monthly sales report, you can use Monarch Classic to create a lookup to that database and pull fields from it into your Monarch Classic table. An example might be the median income broken out by postal code. By joining the Monarch Classic table to the demographics database on the postal code field, you can add a median income field to your table that lists the median income for each customer’s postal code. Once you have this information in your table, you could create a Monarch Classic summary that breaks out product sales by income levels, revealing which income levels purchase the majority of each product. Using this analysis, you could better target each product to the appropriate customers.

In the above example, the Monarch Classic table was initially populated using data extracted from a report, with an additional field added by creating an external lookup to a database. This same example might have used data imported from an external database rather than from a report file.

Creating an external lookup

Steps:

Do the following while a report, a model, and a table are open:

1. Select Add on the Table Design ribbon, select External Lookups, and then Database from the drop-down lists. The Open Database dialog displays.

2. Select the data source.

   To select a previously used data source, click the arrow on the Data Source box to view a list of recently used data sources, then select the desired data source from the list.

   To select a new data source, click the Browse button to display a dialog that lists available data sources:

   • Select Local or Network File option if your data resides in a file on your local drive or on a network drive that is accessible to you. Monarch Classic can join to a number of database file types, including MS-Access MDB/ACCDB files, MS-Excel XLS/XLSX/XLSM files, and dBASE DBF files.

   • Select Web file... if your data resides on a web page. You will need to enter the URL of the web page in the Open Web File Dialog.

   • Select ODBC connection...option if your data resides in a database management system (DBMS) that you can access via an ODBC connection. Some examples of ODBC compatible database systems include SQL Server, Oracle, DB2, and Informix client/server databases.

   • Note: Monarch Classic can join to ODBC data sources for which you have established an ODBC Data Source Name definition (referred to as a DSN). Monarch Classic does not create a DSN for you; you must create a DSN using the Windows Control Panel ODBC Data Sources applet or a similar utility.

   • Select OLEDB Connection... option to specify the appropriate OLE DB provider for the type of data you want to access.

   When browsing for a local or network file as a data source, a standard File Open dialog displays. Use the dialog to navigate to the drive and folder containing your database file, then select the file and click the Open button.

   When browsing for an ODBC data source, a dialog displays that lists the available ODBC DSNs. This dialog may have two or more tabs at the top under which various types of data sources are logically grouped. Select the tab that includes the desired data source name, then select the data source name and click the OK button.

   The selected data source appears in the Data Source box.

3. Select the table that holds the data you wish to link to.

4. Select OK. The External Lookup window displays.

5. Enter a name for the external lookup in the Name field, and the click on the green check icon to accept.

   Note: If you don’t assign a name to the external lookup, it will be named according to its table name.

6. Define the linking column(s).

   A linking column defines the link(s) between the Monarch Classic table and the external database.

   Click the Linking Column check box corresponding to a field you wish to use as a linking column. Once checked, complete the link by selecting the corresponding link field in the Table Window Name column.

   Note: The two columns must be of the same data type (character, numeric or date) and they must contain matching values that can be used to create a relationship between the two tables. The columns need not have the same name (e.g., one column could be named "Cust ID" and the other named "Customer Number").

7. Check the boxes of the fields you wish to import into the Monarch Classic table window.

   If you want to select all fields click on Select All at the bottom of the table.

8. To apply an import filter go to the External Lookup window's External Lookup Filter tab.

9. Select Accept to accept the new External Lookup.

You can use the linked fields just like any other fields in Monarch Classic. Linked fields can be used in filter, sort, calculated field, and summary definitions and can be printed or exported along with fields that you extract from a report.

Storing lookup parameters in a model file

Lookup parameters can be saved in a Monarch Classic model file along with other information about your Monarch Classic session. When you load the model file, the lookup parameters are used to reestablish the lookup with the external database.

Storing lookup parameters in a model file is easy. You simply save the model file. The model file records the name of the lookup database along with the fields used to establish the lookup and the fields you linked to the Monarch Classic table. By applying the model in a future Monarch Classic session, Monarch Classic preserves the work you did in this session.