Page Comparison

The QueryBuilder Menu and Toolbar

The toolbar provides quick access to commonly used commands in the QueryBuilder main window. Click the icon once to carry out the action represented by that icon.

Icon

Description

Menu Equivalent

Creates a new query.

File > New

Opens the Open Report dialog, allowing you to retrieve a saved query. 

File > Open

Saves the open query.
 

File > Save
 

--

Allows you to select how and where the query is saved.

File > Save As

Opens the saved query on your local machine.

File > Local > Open

Saves the open query to your local machine.

File > Local > Save

Submits your query for execution.

File > Submit

Checks the syntax of the query.

File > Validate Query

Opens the print dialog.

File > Print

Allows you to Sequentially undo up to the last 20 actions.

Edit > Undo

Allows you to Sequentially redo up to the last 20 actions.

Edit > Redo

Copies and removes the currently selected data.

Edit > Cut

Copies the currently selected data.

Edit > Copy

--

Copies the currently selected object with each column header.

Edit > Copy with Headers

Pastes the highlighted data.

Edit > Paste

--

Selects all the data.

Edit > Select All

Clears all values of the current query.
 

Edit > Clear
 

--

Renames the currently selected field.

Edit > Rename

Removes the currently selected field.

Edit > Remove

Opens the properties dialog.

Edit > Properties

Turn descriptions on or off.

Data > Toggle Descriptions

Goes to the previous page of results.

Data > Previous

Goes to the next page of results.

Data > Next

Sorts the currently displayed segments in ascending order.

Data > Sort Ascending

Sorts the currently displayed segments in descending order.

Data > Sort Descending

Opens SQLManager.
 

Query > SQLManager
 

Opens the Filter window
 

Query > Filter
 

Opens the KPI Filter window.

Query > KPI Filter

Opens the Add Custom Field Dialog.

Query > Add Custom Field

Moves the highlighted field to the left (or up).

Query > Move Left

Moves the highlighted field to the right (or down).

Query > Move Right

Refreshes any cubes or tasks you are using.

Query > Refresh Cubes/Tasks

Opens the Export dialog to Export the query output.

Tools > Export

--

Tools > Open Reference

--

Opens drilldown dialog.

Tools > Drilldowns

--

Opens the Options dialog.

Tools > Options

Constructing The Query

Constructing a Query

Constructing a query only has a few simple steps, each of which are we outlined in further detail below the following basic instructions.

Choosing a Table with QueryBuilder

QueryBuilder uses SQLManager for you to easily select a table to query. After opening QueryBuilder, clicking click on the SQLManager  () button will  icon to open SQLManager. Within SQLManager, you must then choose a table by selecting a clicking the table name and clicking then OK, or by double-clicking on the table name. SQLManager will close and the table's fields you selected will be shown at the left of appear in the Select Fields pane in the QueryBuilder window.

The Select Fields Pane

The Select Fields pane allows you to pick the fields that you want to use within your query. Drag and drop or double-click the fields you wish to include in your query. To select all the fields in a table, double click on the table name. These fields can be cleared easily by clicking Click the Clear () button to remove all of the selected fields.

• Function – If you choose to attach a function to your field, you can select one from the
top left box
• Function box above the Select Fields Pane. If you leave this option as <none>, then no function will be attached to this field. For instance, you can attach a Count
Occurrences function to the State field
• function to the CustomerNumber field, which will count the number of
instances each state is listed.
• customers that meet your queries criteria. 
• Field – Here, simply choose a desired field from the
drop-down menu
• list. A value for this area must be selected. Click on this area to select
from the drop-down menu
• or move the fields.
• Distinct
– This check-box option will make the query select only distinct fields.

Using Filter to Construct a Query 

• – Checking this box will filter out all repetitive instances of the value, leaving you with one sample of the potentially

• many identical values

The Dimensions Pane

The Dimensions pane allows you to select which dimensions your query will display. Every field that you add will be displayed in your query. To add dimensions, simply drag the field from the field list, then drop the field in the dimension's column. These fields can be cleared easily by clicking the Clear (Image Removed) button.

The sort field pane is located at the bottom right of QueryBuilder. Here, you can change the order or sorting of your fields, facilitating custom access to your data and orderly presentation. Simply select a field from the Select Fields Pane and drag it to the Sort Fields Pane. A field that has an up arrow will be sorted in ascending order, and one with a down arrow will be sorted in descending order. Again, these fields can be cleared easily by clicking the Clear (Image Removed) button.

Creating KPI Filters

KPI filters differ  differ from normal filters in that they allow you to filter on all of a customer's records at once rather than individually. This just the fields selected in the KPI or Dimension windows. This means that you can do add a filter on functions such things as summing up order amounts or other additional functions. KPI filters are also effective for eliminating outliers in a data set when retrieving the frequency. Essentiallyas the sum of a field must be greater than a particular value. Essentially, you are placing a function filter on a filterfunction.

KPI filters use the Filter module, and are essentially created in the same fashion as a normal filter. Clicking on the KPI Filter () button icon will open the KPI Filter dialog. For more information on using Filter, please see the "Filter" section earlier in this manual.

. 

Column Properties

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.

QueryBuilder Results

Once you have finished constructing your query and have clicked the Submit (Image Modified) 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. 

Options Dialog

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 (Image Added), 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.