Creating Primary Queries that Execute Sub-Queries

  To create a primary query that executes sub-queries:

1. In Interactive Reporting Studio , open a dashboard that contains the Conditional Sub-Queries component.

2. Build a primary query to retrieve columns that contain transactions of interest.

3. Process the query.

   Use computed values in the results section filters to limit results and display transactions of interest.

   A sub-query needs keys to identify the transactions. Primary Query Results may contain key and additional information, such as computed items or columns used to analyze transactions; therefore, another table may be required that contains only keys.

4. Optional: If Primary Query Results contains more than key information, build a Limit Lookup Table that contains only the keys needed to identify the transactions.

   Each column in the table is transferred as a filter to the sub-query.

5. Build a sub-query that displays transaction information that is identified in the primary query.

   For example, to obtain information on under–performing stores, drag the columns; store location, manager, and contact phone number onto the Request line.

   For each primary query, one or more sub-queries can be built.

6. Create filters within the sub-query to match columns in the Limit Lookup Table by performing an action:

   • If the Conditional Sub-Queries component is used, values are transferred from the Limit Lookup Table into the sub-query as filters before processing the sub-query (Because the sub-query contains filters, the component retrieves details for a small number of rows, saving time and disk space)

   • The conventional way of searching for exceptions without using the component, is to obtain details for all transactions and search for exceptions all in one query (A column in the Limit Lookup Table may have no equivalent filter in a sub-query. This scenario is possible if two or more sub-queries are present. In this case, there is no need to add the filter to the sub-query. The component does not apply the missing filter when it executes the sub-query)

7. Under File, then Save Options, select Save Query Results with document.

8. Clear the primary query.

   Clearing prevents the Interactive Reporting Studio document from becoming too large (by including the primary query results).

9. After the primary query is processed:

   1. Open the Interactive Reporting Studio document and navigate to the Conditional Sub-Query Properties frame.

      Available Queries displays all queries available in the document.

   2. Move selected queries between Available Queries and Primary Events by clicking and .

      Multiple primary queries can exist in an Interactive Reporting Studio document.

   3. In Sub-Query Properties, define the action that the component must perform when the primary query is processed.

   4. From Event Name, select the primary query added in step b.

      The component loads the sub-query that the primary query is to launch. It also displays the Limit Lookup Table from which the sub-query obtains the filter values. As it is the first time that the primary query is defined, the items are blank.

   5. Optional: Select Add Custom Event. See Creating Custom Events that Execute Sub-Queries.

   6. From Limit Lookup Table, select the results or table that contains values to filter the sub-query.

   7. Move selected sub-queries to be launched when the selected primary query is processed, between Available Queries and Sub-Queries by clicking and .

   8. Click Apply.

  To test the primary query:

1. Navigate to the primary query results.

   The results should be empty.

2. Process the primary query.

   The results section is populated.

3. Navigate to the sub-query results section.

   The results are updated to reflect the transactions identified in the Limit Lookup Table.