Nested Queries

How Nesting Works

Let's use a simple data relationship to help illustrate how nesting occurs.  Imagine we have data collected from two unrelated sources that look the ones seen in this table.

We want to create a data source connecting all these things for reporting using nesting. The trick is to identify where the two datasources connect. You may notice in the data above The CodePK column in the Codes datasource matches up with the CodeID column of the Frequency datasource. This is where we will connect the two datasources. We will make our Codes datasource the parent and the Frequency the child. The queries would look something like below.

-- Parent Query 
SELECT CodePK, Code FROM Codes
 
-- In the Child Query, accessed by clicking on the Frequency leaf of the Nested Queries Tree
-- This assumes the value of Parameter 1 would be equal to {CodePK}
SELECT FrequencyID, FreqValue FROM Frequency WHERE CodeID = ?

What this means for our resulting data is that the parent query is called first, and a set of results is returned to the parent query, named Codes. After this data has been retrieved, the Child query will execute, once for each row of the parent, substituting the value of CodePK into the child query where we have the {CodePK} reference.

The resulting data will have a structure like this:

Codes
    |-Row 1
    |     |_ CodePK - 1
    |     |_ Code   - ZG
    |     |     Frequency
    |                   |-Row 1
    |                         |_ FrequencyID - 2
    |                         |_ FreqValue - 41
    |                   |-Row 2
    |                         |_FrequencyID - 5
    |                         |_ FreqValue - 13
    |                   |-Row 3
    |                         |_ FrequencyID - 7
    |                         |_ FreqValue - 11
    |-Row 2
    |     |_ CodePK - 2
    |     |_ Code   - GB
    |     |     Frequency
    |                   |-Row 1
    |                         |_ FrequencyID - 3
    |                         |_ FreqValue - 13
    |-Row 3
    |     |_ CodePK - 3
    |     |_ Code   - DC
    |     |     Frequency
    |                   |-Row 1
    |                         |_ FrequencyID - 1
    |                         |_ FreqValue - 11
    |                   |-Row 2
    |                         |_FrequencyID - 4
    |                         |_ FreqValue - 26
    |                   |-Row 3
    |                         |_ FrequencyID - 6
    |                         |_ FreqValue - 32

Each row returns the CodePK and Code from the parent query, but also the results of the child query, that apply to the CodeID we get from the parent PK.

Now, in our Report Design, we will have access to our datasource Frequency that linked these two sets of data together through a shared value.

Special Considerations

Nested Queries are powerful and easy to use, but users should be aware of runtime implications.  Imagine the scenario above, where we have two sets of data, each with 5000+ rows.  When our child query executes, each row of its query is going to require a lookup from the parent.  For most common sets of data and database sizes, this won't be an issue, but it's possible to imagine that instead of just one child query, we have a dozen.  

In addition, some of those children also have many children.   It's very easy to see in this scenario how exponential growth occurs and our system performance may suffer.  Most report designers will limit query sizing as oversized data structures are simply not as easy to work with.  However, if you feel an urge to generate massive complex trees of million line queries, you may be waiting a while.

Nested Query Example - Equipment Downtime

Here in this example, we have two database tables: an equipment table, which contains all of the equipment we have, as well as a downtime table, which contains a list of every downtime event. The tables are put together like the tables below.

1. Create two tables in the database that are similar to the tables listed above.
   
   
2. Open a report, and navigate to the Data Panel.
   
   
3. Click the Plus  button and add a SQL Query to the data sources.
   
   

4. Change the Data Key property to Equipment.
   
   

5. Type the query below into the query area. Here we are pulling out three columns of our equipment table, and giving each column a more descriptive name. Pay close attention to the spelling of the aliased names (names following the AS keywords), as we will have to reference EquipmentIDNumber later in our subquery. 

   SQL - Equipment Table Query

   SELECT id AS EquipmentIDNumber,
     Name AS EquipmentName,
     Description AS EquipmentDescription
   FROM Equipment_Table

6. On the right of the Nested Query section, click the Plus button to create a sub_query.
   
   
   
   

7. Select the sub_query, and rename the Data Key property to rename it. In this example, we will use the name EquipDowntime. 
   
   

8. Type the following query into the query area.

   SQL - Downtime Table Query

   SELECT cause AS DowntimeCause,
     minutes_down AS DowntimeMinutes
   FROM Downtime_Table
   WHERE Equipment_id = ?

   Here we pull in our downtime table, but we only need the cause and minutes_down, since we are already grabbing the Equpiment_id from the first query. The WHERE clause is where we link this query to the parent with the equipment ids.
   
   

9. In the Parameter 1 field, type the following:

   Expression Language - Referencing a Parameter

   {EquipmentIDNumber}

   Note that we are directly referencing one of the aliased column names from the Equipment query.
   
   

10. That is all it takes to make a Nested Query! We now have two separate tables being called and linked together by the equipment id. 
    
    

11. To check that your queries worked, go to the Preview tab and look at the XML data that comes up. You should have a data set inside (indented) each row of Equipment data.

    If you'd like, you can continue with this example and either use this data in a table group, or in a nested chart.