|
Beta
Please note, this applet is currently in Beta please use with the understanding that results should be closely reviewed.
Overview
Advanced applets in InFocus leverage the power and flexibility of SQL queries, where the user can directly query their InFocus Data.
Query Builder visualizes query writing and dynamically builds InFocus-optimized queries for use in applets like Dashboard Queries Manager, Custom Reports, etc.
Please note: SQL Queries directly affect the InFocus database. Use caution.
Key Concepts
Datasets
Queries written with Query Builder begin with a Dataset. As such, each query can only reference one dataset.
Fields to be used in the query are selected from the Available Fields list based on the Dataset and can then be leveraged as Selected field, Calculated Columns, and Filters.
SQL Statements
Simplified SQL statements are build on the following commands: SELECT, FROM and WHERE. Query Builder compiles these as follows:
| • | SELECT - Built from Selected Fields and Calculated Columns based on the dataset |
| • | FROM - Dynamically built from the selected Dataset |
| • | WHERE - Built from defined Filters |
Additional keywords such as GROUP BY and ORDER BY are dynamically built from the dataset.
Generated SQL can be further customized as necessary in the editor.
Field Descriptions
Datasets/Fields
| • | Datasets - Lists available data sets. Each data set contains a list of available fields that can be selected for use in the query |
| • | Available Fields - Displays a list of fields per dataset. Fields can be filtered by typing in the search box and selected for use in the query by checking the box next to each field. Field descriptions are displayed at the bottom of the pane when clicking the field. |
| • | Clear Selected - Clears all sections made in the Datasets field list. |
Toolbar
| • | Build Query - Generates the query based on Dataset selections, calculated Columns and Filters. Clicking this will overwrite previous builds. |
| • | Build & Execute Query - Generates the query as described above and then runs the SQL Query as displayed in the editor. Note, SQL Queries directly affect the InFocus database. Use caution. |
| • | Execute Query - Executes the query only. Note, SQL Queries directly affect the InFocus database. Use caution. |
| • | Copy - Copies the text of the generated query |
| • | Clear - Clears the generated query |
| • | Export Query - Exports the text of the query to a .sql file. |
Query Options
| • | Selected Fields - Displays a running list of selected fields from the dataset |
| • | Calculated Columns - Adds calculated columns using selected fields from the dataset. Calculated columns use standard SQL formatting. |
| • | Filters - Adds filters to the query using selected fields from the dataset |
| • | Results - Displays the results of an executed query |
|