How to Control an SQL Dashboard Query

InetSoft's award-winning dashboard software allows users to query SQL databases for a live and interative data mashup reporting experience. View the example below to learn more about the Style Intelligence solution.

To view the SQL statements that a table will attempt to execute (the query plan), right-click the title bar, and select 'Show Plan' from the context menu. This opens the 'Query Plan' dialog box.

The 'Query Plan' dialog box displays the queries that will be sent to the database at runtime. Additionally, it shows the data operations that the Worksheet will perform in post-processing, after the data is retrieved from the database. The plan also shows the source information for parameterized conditions.

By default, the Worksheet attempts to create a single SQL query that fully generates the table's data. This is typically the most efficient approach because it allows the database to perform all the needed data operations (joins, filtering, etc.). However, you can override this behavior for a particular table by deselecting the 'Merge SQL' option in the 'Table Properties' dialog box.

This example illustrates how you can control the SQL query that a table sends to the database.

1. Expand the 'TABLE' > 'SA' node of the 'Orders' data source.
2. Drag the 'products' table to an empty location on the Worksheet.
3. Drag the 'categories' table to an empty location on the Worksheet.
4. Drag the 'category_id' column header from the 'categories' table onto the 'category_id' column header of the 'products' table, and release when the 'join' symbol appears. This creates a new table called 'Query' that contains the inner join between the two tables based on the 'category_id' columns.
5. Right-click the title bar of the 'Query' table, and select 'Show Plan'. This displays the 'Query Plan' dialog box.

The 'Query Plan' dialog box displays the query that the table will send to the database. The statement below the query indicates that this query (including the instructions to join the two tables) will be sent to the database as a single SQL statement, which is also indicated by the single icon in the top panel.

To override the default single-query behavior, follow the steps below:

1. Right-click the title row of the 'Query' table again, and select 'Properties' from the context menu. This opens the 'Table Properties' dialog box.
2. Deselect 'Merge SQL' and click 'OK' to exit the dialog box.
3. Right-click the table title bar, and select 'Show Plan' again.

The query plan is again displayed, but the top panel now shows an expandable hierarchy of queries. The 'products' and 'categories' queries are each individually executed as single queries.

View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software.