Query Designer
- Keith Mangold
Overview
The Query Designer screen is where you will define a query against your database. The screen consists of the following elements:
1 - Ribbon Bar
Functions to Save, Exit, Run, and Data Model Optimizations exist here.
Save - Save the Query.
Exit - Close the Query and return to the Main Menu.
Run Query - Executes the Query against the database and displays the results in the Query Grid.
Data Model Optimize - Filters out unused tables from the Query. Only used for very large Data Models.
Data Model Save - Saves the optimized Data Model to the database.
Data Model Restore - Reverts the Data Model to its original design.
2 - Entities
This section lists all of the different tables contained within your Data Model. This panel is used as a visual representation of all entities (tables) and their attributes (columns), which we can use to build queries. It can be used to add entity attributes to a Columns Panel (and they will be displayed in the result) or to a Conditions Panel (in order to actually set the query conditions).
A - Listing of Entities / Tables. Clicking the +/- signs will expand or collapse the table.
B - Listing of Attributes / Columns. Clicking the Checkbox will select the attribute for inclusion.
C - Select All
D - Deselect All / Clear Selection
E - Add Checked / Selected Attributes to the Columns listing.
F - Add Checked / Selected Attributes to the Conditions listing.
3 - Columns
The section lists all of the different columns you have selected to be displayed in your query results. You have a list of lines, each of them represents a column in the query results (in the Results Panel). Every line consists of Expression, which is an attribute, a Title, which serves as a column title in the Results Panel, and buttons – for sorting (on the left), aggregating and deleting (on the right).
A - Command Button - Clicking this displays options specific to the selected column.
B - Table & Column Name - The orignal table and name of the column selected from the Entities panel.
C - Display Name - The name you wish to have the column appear within the results grid. Clicking on this text will allow you to change the title.
D - Sort Order - Ascending or Descending options. Note: This can be overridden within the Query Grid display.
4 - Conditions
This sections lists all of the filtering conditions for data selection. The result of your query will include only the data that satisfy those conditions. Every line in the Conditions Panel corresponds to a single query condition. Every condition consists of 3 elements – an expression (some entity attribute), an operator, and value(s) (one or more).
A - Condition Connectors - Options for All or Or
B - Enable / Disable Checkbox - Check or Uncheck to include this condition when running the query.
C - Command Button - Clicking this displays options for the Condition.
D - Attribute - The selected column the condition applies to.
E - Operator - Type of filter against the Attribute.
F - Value - Value to check against.
We have mentioned attributes above (e.g., ‘Invoice Balance Amount' and 'Customer #’). Together with values, operators set certain limitations regarding data which is stored under a given attribute. For example, “(Invoice Balance Amount) is not equal to $0,” “(Customer) is either 10200, 10300, or 41500.”
5 - SQL
This area displays the SQL code used to run against the database. This is display for debugging purposes only and will most likely not be used except by Administrators and Aeros Customer Care.
6 - Properties
This sections lists different configuration options specific to the Query.
Name - The title of the Query. Required to save.
Description - Optional text to describe the purpose of the Query.
Category - The first level grouping of the Query. Select from available options.
Sub Category - The second level grouping of the Query. Select from available options.
Active - When checked, the user can run the Query from the Main Menu.
Delete - Flag to mark the Query to delete. Note: The system does NOT delete any Queries. Must contact Customer Care to delete a Query.
Distinct Records - Selects only the unique records to return in the Results grid.
Select Top Rows - Numeric value option to specify how many records to return. For example: 100
Pivot as Source - When selected, the results data will return a Pivot Table as the raw data.
Pivot Settings - Configuration for the Pivot Source option.
Lock Criteria - When checked, when the user runs the Query, they cannot modify the Conditions options.
Lock Values - When check, when the user runs the Query, they cannot modify the Values in the Conditions options.
Release - Not Yet Implemented
System Query - Not Yet Implemented
Last Modified - Display option review of the last time the Query was saved.
Version # - Display option of a counter of Query revisions.
Query ID - Display option of the unique identifier of the Query stored in the database.
Data Model ID - Display option of the unique Data Model used as the source for the Query.
Connection - Display option of the Connection used by the Data Model to connect to the raw data source.
7 - Results
This section displays the results of the query in an Excel-like type grid format. For further documentation and usage of the grid, please view the Query Grid page.
Step-by-Step Guide
Creating a New Query
Access the Vision Main Menu.
Click New Query.
Select a Data Model from the drop-down list. Generally the default is automatically selected.
Click OK.
In the Properties panel, specify a Name of the Query.
Optional: Specify any other options desired within the Properties panel.
In the Entities panel, expand the Entities to located the desired columns.
Click the checkbox next to the desired columns.
Click the Add Columns button to add the checked columns to the Columns panel.
Optional: Alter column names and configured options within the Columns panel.
Recommended: Add options to the Conditions panel to filter your results.
Note: If you leave this section empty before clicking Run Query, all records will be returned from the database. Depending on the size and number of records, this may take a large amount of time to return data.
Click the Deselect All
button at the bottom of the Entities panel.
Locate the columns that you wish to add a condition for.
Click the checkbox next to the desired columns.
Click the Add Conditions button to add the checked columns to the Conditions panel.
Within the Conditions panel, specify a Value for each of the Condition options.
Suggested: Click Save.
Click Run Query in the Ribbon Bar.
Your results will display within the Results panel.
Editing a Query
Access the Vision Main Menu.
Locate the Query you wish to edit.
Click the pencil / edit icon next the Query name.
The Query Designer screen will appear.
Video |
|---|