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

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.

To Construct a Query

Open QueryBuilder.
Select a table using SQLManager. Your query is based off your table selection in SQLManager. Double-click on the table or click 'OK' to open it in QueryBuilder.
Select the fields that you wish to include in your query through the The Select Fields Pane dialog on the left.
Use a Filter to constrain your field information if necessary.
Select the Dimensions for your query. For more information, see "The Dimensions Pane" section of this manual.
Sort the fields of your query.
If necessary, add a KPI filter.
Click Submit ()

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 or 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

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.

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.

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

Printing Output

MarketWide uses a standard print dialog to print query output. From the print dialog, you can access the Page Setup options.

Page Setup
When printing QueryBuilder output, you can customize the format and settings of your output to enhance your reports. Adjusting scaling, changing fonts, adding dates, and margins, gives an abundance of presentation options.

Access the Page Setup dialog by clicking on File > Print from the menu in the QueryBuilder output, and then clicking File > Page Setup from the print preview page.

There are three main sections in the Page Setup dialog: Paper Size, Margins, and Orientation.

- Paper Size – In the Paper dialog, you can select the paper type and source.
- Orientation – The Orientation dialog allows you to choose either Portrait (vertical) or Landscape (horizontal) orientation. Landscape is particularly useful for wide report outputs.
- Margins in Inches– In the Margins dialog, you can adjust page margins. A preview is displayed in the preview pane.

The QueryBuilder Menu and Toolbar

The toolbar gives quick access to commonly used commands in the QueryBuilder main window. Click the icon once to carry out the action it represents.

Icon	Description	Menu Equivalent
	Opens a new QueryBuilder 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 a query saved 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 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 selected data.	Edit > Cut
	Copies the selected data.	Edit > Copy
--	Copies the selected object with each column header.	Edit > Copy with Headers
	Pastes previously copied data.	Edit > Paste
--	Selects all the data.	Edit > Select All
	Clears all values of the current query.	Edit > Clear
--	Renames the selected field.	Edit > Rename
	Removes the 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 selected column in ascending order.	Data > Sort Ascending
	Sorts the 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.	Query > Move Left
	Moves the highlighted field to the right.	Query > Move Right
	Refreshes any cubes or tasks you are using.	Query > Refresh Cubes/Tasks
	Opens the DataExport dialog to export your query results.	Tools > Export
--	Opens the file or hyperlink referenced in a field.	Tools > Open Reference
--	Opens Drilldown dialog.	Tools > Drilldowns
--	Opens the Options dialog.	Tools > Options

Browser not supported