InetSoft Product Information: Subquery Dashboard Options

InetSoft's award-winning dashboard software provides users with sophisticated subquery dashboard options for arranging hierarchical data sources in a real-time reporting solution. View the example below to learn more about the Style Intelligence solution.

You may not need to specify every setting in the 'Subquery' dialog box. The required menu choices depend on how you wish to use the subquery result set. This section explains three different cases for using a subquery.

If the subquery returns a single fixed value (i.e., the subquery table in the Worksheet contains a single cell), then in the 'Subquery' dialog box you only need to specify the name of the subquery. No further settings are required.

If you want to use a particular column from the subquery as a fixed list in the condition, you need to specify the query name in the 'Subquery' dialog box, and then select the desired subquery column from the 'In column' menu.

A subquery that returns a column is typically used in conjunction with the 'one of' condition clause to filter values that are members (or non-members) of the returned list. You do not need to make selections in the 'Subquery column' and 'Current table column' menus.

A subquery can return a value that is dependent on the row of the main table. Such a row-dependent subquery is useful when you want to filter rows of the main table based on a comparison with corresponding rows of another table (i.e., the subquery table).

For example, consider a case where one table displays sales by year for NY, and a second table displays sales by year for CA. You want the NY table to display records only for those years where sales were greater than sales in CA. For instance, you want the NY table to display the record for year 2002 only if sales in NY for 2002 were greater than sales in CA for 2002.

To configure a row-dependent subquery, follow these steps:

1. Select the table you wish to filter.
2. Open the 'Condition' dialog box, and then open the 'Subquery' dialog box.
3. Select the subquery name in the 'Subquery' menu. (In the example above, the subquery is the CA sales table.)
4. Select the desired subquery column from the 'In column' menu. This is the column of the subquery containing the actual values to be tested in the condition. (In the example above, this would be the 'sales' column of the CA table, since this column contains the values that the condition will test.)
5. From the 'Subquery column' menu, select the column in the subquery table that indexes the subquery condition column (selected in the previous step). The 'Subquery column' column is the subquery join column; it will be used to “look up” the condition values corresponding to a particular row in the main table. (In the example above, the 'Subquery column' column would be the 'year' column of the CA table, since this is the column that indexes the 'sales' column of the CA table.)
6. From the 'Current table column' menu, select the column in the main table that contains the indexing values corresponding to those in the 'Subquery column' of the subquery table. (In the example above, the 'Current table column' would be the 'year' column of the NY table, since this is the column that corresponds to the CA 'year' column.)

The 'Subquery column' and 'Current table column' serve to connect the subquery table and main table. For each row in the main table, the value in the 'Current table column' is matched against the values in the 'Subquery column'. The results returned by the subquery for each row are the values of the condition 'In column' in the rows where the 'Subquery column' value is matched.

Previous: Dashboard Subquery

Next: Aggregating Dashboard Data