Metadata Definition: Lookup

Lookups apply metadata to values that are queried by the Show Values command in the limit dialog box. If the database tracks data by codes, abbreviations, or ID numbers, lookup values can help users effectively limit queries.

For example, the product table may track sales by product ID number. When the user attempts to limit the Product ID column in a query, a Show Values call to the database yields only ambiguous product ID numbers. It can be hard to tell where to apply the limit.

Using the Lookup tab, you can map the product ID values to a column of descriptive product names elsewhere in the database. When the user clicks Show Values, he or she selects among descriptive product names to set the limit on the underlying product ID numbers.

Note:

To use this feature, you need a table of descriptive lookup values in the database, and an additional mapping table to verify which items are supported by lookup values and where the corresponding lookup values are stored.

  To apply metadata to limit lookup values:

  1. On the Lookup tab, select Use SQL Definition.

    The SQL entry fields activate. Click Clear to clear the entry fields if you make a mistake and want to start over.

  2. In the Select fields, enter the appropriate column names as they displayed in the domain registry table. The Lookup Table, Lookup Value Column, Lookup Description Column, and Lookup Domain ID Column are required for Interactive Reporting Studio to locate lookup values.

    • Lookup Database—Name of the column of databases in the domain registry table.

    • Lookup Owner—Name of the column of owners in the domain registry table.

    • Lookup Table—Name of the column of tables containing lookup domain description values in the domain registry table.

    • Lookup Description Column—Name of the column of columns containing descriptive lookup values in the domain registry table.

    • Lookup Value Column—Name of the column of columns of original column values in the domain registry table.

    • Lookup Domain ID Column—Name of the column of domain IDs in the domain registry table.

  3. In the From field, enter the physical name of the domain registry table.

    Interactive Reporting Studio first sends SQL to the domain registry table to see if

    Lookup values are available for a given item.

  4. Use the Where field to identify which items have lookup values.

    Use the following format (do not include brackets): 

    <tables column>=’:TABLE’ and <columns column>=’:COLUMN’

    When you limit an item and show values, Interactive Reporting Studio stores the physical table and column names of the item in the variables, :TABLE and :COLUMN.

    Interactive Reporting Studio searches the domain registry table for a row that matches the values temporarily stored in :TABLE and :COLUMN. When it finds a row that matches, it pulls lookup values from the specified columns in the domain descriptions table. You can also use the :LOOKUPID variable to store the lookup domain ID value.

    Note:

    The database variables must be entered in upper case and preceded with a colon.

  5. Use the Lookup Where field to synchronize the values in the domain registry and domain description tables.