Reporting on lookup tables

Run a query against a lookup table so you can analyze the table data and generate reports. When lookup tables are loaded or reloaded into your Network instance, they are immediately available in reporting. Existing lookup tables will be automatically available to report on.

All users with access to create SQL queries using the SQL Query Editor or on the Saved Reports page can run reports against the lookup tables in their Network instance. The Basic Report Builder and Aggregate Report Builder do not support lookup tables.

Create a query

Use the tree view to display the lookup tables and fields that are available to report against. You can also perform JOIN operations between lookup tables and any table available in your Network instance; for example, Network defined tables or custom tables.

To query a lookup table:

1. On the Network menu bar, click Reports > SQL Query Builder to run a query against the table.

   If you have just loaded or reloaded the lookup table, a spinner might display as the reporting schema is being updated to include the lookup table and fields.

2. Type your query in the query box.

   Lookup table options:

   • Add the lookup table - Use the tree view beside the SQL Query Editor box to find the table. To choose the lookup table, find the table in that category. Click the Add to Query button to insert the table into the query box.

     The Lookup Tables category displays if you have uploaded at least one lookup table.

     Tip: You can also find fields and tables using the search bar in the tree view or using the CTRL-Space keyboard shortcut.

     To help you recognize which tables are lookup tables, the lookup table names contain the __t suffix.

     Example

     Create a query using the addressdata__t lookup table.

     

   • Add fields - Expand the addressdata__t table. The fields (columns) are listed alphabetically.

     Click the Add to Query button to insert each field into the query box.

   • Perform JOIN operations (optional) - >Join the lookup table with another lookup table or any table in your Network instance.

     Example

     To compare postal codes in your lookup table to postal codes in your Network instance, join the lookup table (addressdata__t) to Network's Address table. This example returns the addresses where the postal code from the lookup table does not match the postal code in Network’s address table, but the city, state and country are the same.

     SELECT
             address.address_line_1__v,
             address.address_line_2__v,
             address.locality__v,
             address.administrative_area__v,
             address.postal_code__v,
             addressdata__t.postal_code__v,
             address.country__v
         FROM
             addressdata__t INNER JOIN address
                 ON address.country__v = addressdata__t.country__v
             AND address.administrative_area__v = addressdata__t.administrative_area__v
             AND address.locality__v = addressdata__t.locality__v
         WHERE
             address.postal_code__v <> addressdata__t.postal_code__v
         GROUP BY
             address.address_line_1__v,
             address.address_line_2__v,
             address.locality__v,
             address.administrative_area__v,
             address.postal_code__v,
             addressdata__t.postal_code__v,
             address.country__v
     

3. Click Run Query to view the results.

   

4. Download the report results or save the query.

Deleted lookup tables

When lookup tables are removed from your Network instance, the table is also removed from Network Reporting. The table will not be available in the query helper buttons. Saved reports or queries that contain the table will not validate.

Note: If you have used a lookup table in a NEX rule, the deleted table is not automatically removed from the rule. Ensure that you remove the lookup table from the NEX rule.

SQL reserved words

Lookup tables can contain column headers that are also SQL reserved words; for example, where. To ensure these words do not cause issues when Network validates the query, double quotes (") are automatically added to the word when you select the column from the query helper; for example, "where".

If you type the column header into the query, a validation error displays if double quotes (") are not used.