InetSoft's award-winning dashboard software allows users to design a dashboard subquery for hierarchical data sources creating an all-in-one business intelligence solution. View the example below to learn more about the Style Intelligence solution.
When you add a filter to a table, you can choose to use a subquery to return the test conditions. This allows you to design a filter with conditions that are set at runtime based on current data.
For example, consider a table with a column named 'CompanyName'. You want this table to display only companies in NY state, but the table has no 'State' column on which you can define the filter. If you knew in advance which companies reside in NY, you could enter the condition explicitly; for example, [Company][is][one of][explicit list of companies in NY].
However, if the set of companies in NY is not fixed, and can therefore change whenever the underlying database is updated, any fixed list that you enter in the condition will become outdated. In order to filter the table correctly, you need to generate a list of “companies in NY” that is always true to the current data.
View more examples in the InetSoft visualization gallery
You can do this by using a subquery to provide the current list of “companies in NY.” The condition you place on the table will then have the form [Company][is][one of][results of subquery]. The list of “companies in NY” generated by the subquery will always be up-to-date, because the subquery itself executes at runtime.
To enter a subquery in a filter condition, follow these steps:
1. Open the Worksheet containing the table you want to filter.
2. Select the query you wish to use as a subquery in the condition. Drag it from the asset tree to an empty cell on the Worksheet. This creates a new table with the name of the query.
3. Click the 'Condition' button on the title bar of the table you wish to filter. This opens the 'Condition' dialog box.
4. Open the edit field in either the 'Simple Condition' mode (click the 'More' button) or 'Advanced Condition' mode (click the pre-aggregate or post-aggregate 'Edit' button).
5. Select 'Subquery' from the rightmost popup menu. This displays the 'Subquery' button.
6. Click the 'Subquery' button. This opens the 'Subquery' dialog box.
7. In the 'Subquery' menu, specify the desired subquery table. (This is the table created when you dragged the query to the Worksheet.)
8. Select options in the lower three popup menus, as needed.
You may not need to make every menu selection; this will depend on how you want to use the subquery result set. The 'In column' menu specifies the subquery column that contains the result set to use in the condition. The bottom two menus control table synchronization for row-dependent conditions.
9. Click 'OK' to close the 'Subquery' dialog box.
10. After you have specified the subquery to use, define the rest of the condition as described earlier.
Copyright © 2024, InetSoft Technology Corp.
