Creating KPI Filters
KPI filters differ from normal filters in that they allow you to filter on just the fields selected in the KPI or Dimension windows. This means that you can add a filter on functions such as the sum of a field must be greater than a particular value. Essentially, you are placing a filter on a function.
KPI filters use the Filter module, and are created in the same fashion as a normal filter. Clicking on the KPI Filter ( ) icon will open the KPI Filter dialog.
By right clicking over a field and picking Properties, you will open the Column Properties dialog. Some properties are editable and some are read only.
Another option available in QueryBuilder is to create your own custom field by selecting Add Custom Field... from the Query menu of the toolbar. When creating a custom field, you can specify the name of the field under Alias. Enter your own custom SQL code to define the field by clicking on the ellipses button under Custom Value.
Once you have finished constructing your query and have clicked the Submit ( ) button, your QueryBuilder Results will be displayed in a new tab. From the Results tab you can review your data, change how your data is sorted or you can use DataExport to export your results. You can also return to the Query tab to make adjustment to your query if needed.
The Options Dialog allows you to set preferences for QueryBuilder. It provides options such as setting the maximum number of rows viewable on a page at a time.
- Enable Code Completion - In the SQL window, as you type a command, MarketWide makes suggestions to complete the word you are typing. For example, if you were to type in the SELECT keyword, you would start with S and a list of database keywords that start with S would become visible to you. Likewise, SE would reveal all the database keywords that start with SE. You can select the desired option by using your arrow keys or by clicking one of the options in the drop-down list.
- Word Wrap - A line of text will wrap onto the next line to fit the screen instead of continuing and requiring scrolling.
- Parse AdHoc Query Changes - When this option is checked, SQL that is entered into the SQL Edit Mode is reflected in ListBox and Diagram mode.
- Maximum rows - This sets the maximum number of rows to be displayed per page.
Using Filter to Construct a Query
QueryBuilder uses the MarketWide Filter module to filter out values from your query. Clicking on Filter ( ), or the Filter pane at the bottom of the QueryBuilder window, will open the Filter window.
The Sort Fields Pane
In the Sort Fields pane, you can change the order your results appear by selecting a field or fields to sort by in ascending or descending order. Simply select a field from the Select Fields pane and drag it to the Sort Fields pane. A field that has an up arrow sorts in ascending order, and one with a down arrow will sort in descending order.