QueryBuilder allows you to examine your database information interactively, using a drag-and-drop interface. QueryBuilder can review your data, perform calculations such as counts and sums, and add dimensions to group and sort. In QueryBuilder, you can focus on a set of data in a SQLManager table and arrange that data logically.
Once you select the information you wish to see, with the click of a button, QueryBuilder allows you to see the data.
Why Use QueryBuilder?
QueryBuilder excels as a review, count, and calculate tool to briefly analyze your data. It is most helpful when attaching a function, such as 'count occurrences' or 'average' to a certain field such as 'CustomerNumber' or 'OrderAmount.' You can quickly determine how many customers you have or the average amount of an order.
Topics
| Table of Contents |
|---|
Getting Started with QueryBuilder
QueryBuilder is a tool used across MarketWide that allows you to construct queries to pull information from various tables and fields. After selecting a table from SQLManager (
), you must select the fields that you wish to display in your results by dragging them from the left column into the Fields area. Then you can construct the query using a filter (
). After this, you can adjust the dimensions as well as sorting fields. If you wish, you can also use a KPI filter based on your dimensions to further customize your results. Additionally, QueryBuilder offers flexibility by allowing you to choose how you build your query. You can build a query graphically or, if you already know SQL, you can type your query directly into the interface.
Constructing a Query
Constructing a query only has a few simple steps, each of which we outlined in further detail below.
| Tip | ||
|---|---|---|
| ||
|
| Note | ||
|---|---|---|
| ||
Another way to open the QueryBuilder module is to right click on a table you wish to work with in SQLManager and select QueryBuilder from the menu that appears. |
Choosing a Table with QueryBuilder
QueryBuilder uses SQLManager for you to easily select a table to query. After opening QueryBuilder, click on the SQLManager (
) icon to open SQLManager. Within SQLManager, choose a table by clicking the table name and then OK, or by double-clicking on the table name. SQLManager will close and the table's fields you selected will 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. 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 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 function to the CustomerNumber field, which will count the number of customers that meet your queries criteria.
- Field – Here, simply choose a desired field from the list. A value for this area must be selected. Click on this area to select from the drop-down menuor move the fields.
- Distinct – Checking this box will filter out all repetitive instances of the value, leaving you with one sample of the potentially many identical values
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. For more information on using Filter, please see the "Filter" section earlier in this manual.
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 (
) button.
The Sort Fields Pane
The sort field pane is located at the bottom right of QueryBuilder. HereIn the Sort Fields pane, you can change the order or sorting of your fields, facilitating custom access to your data and orderly presentationyour 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 pane and drag it to the Sort Fields Panepane. A field that has an up arrow will be sorted sorts in ascending order, and one with a down arrow will be sorted sort in descending order. Again, these fields can be cleared easily by clicking the Clear () button.
QueryBuilder Output
Once you have finished constructing your query and have clicked the Submit (
) button, your QueryBuilder Output will be displayed in a new tab. From here, you can save the data as an Excel document, Word document, or HTML document.
The QueryBuilder Menu and Toolbar
The toolbar provides gives quick access to commonly used commands in the QueryBuilder main window. Click the icon once to carry out the action represented by that iconit represents.
Icon | Description | Menu Equivalent |
|---|---|---|
| Creates Opens a new queryQueryBuilder window. | 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 a query 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 and reports if it finds a problem. | File > Validate Query |
| Opens the print dialog. | File > Print |
| Allows you to Sequentially sequentially undo up to the last 20 actions. | Edit > Undo |
| Allows you to Sequentially 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 previously copied 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 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 selected column in ascending order. | Data > Sort Ascending |
| Sorts the currently displayed segments selected column 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 |
Creating KPI Filters
KPI filters differ from normal filters in that they allow you to filter on all of a customer's records at once rather than individually. This means that you can do 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. Essentially, you are placing a function on a filter.
KPI filters use the Filter module, and are essentially created in the same fashion as a normal filter. Clicking on the KPI Filter (
) button will open the KPI Filter dialog. For more information on using Filter, please see the "Filter" section earlier in this manual.
Options Dialog
This dialog, accessed through Tools > Options. Enable SQL options and set the maximum number of rows viewable on a page at a time.
- Enable Code Completion - In SQL Edit mode, as you type a command, MarketWide will make 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 using your arrow keys or by clicking, you can select 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 - The maximum number of result rows to be displayed per page.
Column Properties
The Column Properties dialog allows show you the specifics of a chosen field. In already existing fields, some properties are editable and some are read only. When creating a custom field, however, you can specify the name of the field under Alias, and enter the SQL to define the field by clicking on the ellipses button.