QAD BI Portal Designer > Queries > Creating Queries
  
Creating Queries
Follow these steps to create a query:
1 Click New from the Query Home toolbar.
2 Choose the Design tab.
3 Choose a query area from the drop-down list.
4 Drag and drop facts and dimensions from the navigation pane into the Selections pane.
To change the arrangement or order of the items, drag and drop them into the desired position.
To remove an item, select it and click Remove.

Query Designer
5 To sort the results, select ascending or descending in the Sort Type column.
6 Follow these steps to add a side-by-side comparison:
a Click the Compare By tab and then drag and drop a dimension into the pane. Comparisons are limited to only one dimension but as many values or measures as you want within that dimension.
b Click the dimension to display the Edit and Value tabs:
Edit. Enter a display label for the side-by-side comparison.
Value. Select a value from this drop-down list of dimension values.
7 If needed, create calculated columns for the query. See Adding Calculated Columns.
8 If needed, apply filters to the query. See Adding Filters.
9 To view the first 100 rows of query results, click Test Run.
10 To view all the query results, click Run.
11 When you finish building the query, click Save. See Saving an Item.
Adding Calculated Columns
Calculated columns display the results of mathematical operations between columns.
Follow these steps to add a calculated column:
1 Click Add Calculated Column.
2 Enter a title in the Label field.
3 Choose Numeric or Integer from the Data Type drop-down list:
Numeric. Displays numbers with decimals.
Integer. Displays whole numbers.
4 Drag and drop a dimension into the formula pane to begin building a formula using SQL syntax. Use mathematical operators, such as *, +, /, -, and parentheses to properly build the formulas.

Calculated Column Window
5 Click Check Syntax to ensure that no errors exist in the formula.
6 When finished, click OK.
Adding Filters
The Filters pane is used to apply filters to the query.
Follow these steps to apply a filter to the query:
1 Select the type of filter by clicking the Detail or Summary tab:
Detail. Applies the filter to each row or record.
Summary. Applies the filter to rows or records after they are extracted by the detail filter.

Filters Pane
2 Click the AND folder, then choose AND or OR from the drop-down list.
Note: This folder is the setting for the Boolean logic used to join the filters. The default setting is AND.
AND. A narrowing term. When connecting a filter with the AND operator, the filter returns records that match all of the criteria you chose.
OR. A broadening term. When connecting a filter with the OR operator, the filter returns records that match any of the criteria you chose.
3 Drag and drop a selected fact or dimension under the AND/OR folder.
4 Click the dimension and configure the filter using one of the following tabs:
Edit. Filter on a single fixed value that is entered into a field.
Column. Filter on a selected column.
Value. Filter on multiple defined values. To select multiple values, press the Ctrl or Shift button while clicking the value.
Parameter. Allows the user to select the filter values at runtime. Use this type of filter to prompt for a value, either interactively or in a drill-down of reports or dashboards.
5 Click Run and verify that the query results are correctly filtered. Modify the filter if necessary.
Filter Tabs
Each filter tab contains a drop-down menu of the following filter operators:
 
Operator
 
Definition
In
 
Finds records that include one or more of the values entered. Value and Parameter tabs only.
Not In
 
Finds records that do not match the values entered. Value and Parameter tabs only.
Like
 
Finds records that are similar to the value entered. Wildcards, such as %, can be used.
Not Like
 
Finds records that are not similar to value entered. Wildcards, such as %, can be used.
Equal to
 
Finds records that exactly match the value entered.
Not Equal to
 
Finds all records except for the value entered.
Less than
 
Finds all records that are less than the value entered.
Less than or equal to
 
Finds all records that are less than or equal to the value entered.
Greater than
 
Finds all records that are greater than the value entered.
Greater than or equal to
 
Finds all records that are greater than or equal to the value entered.
Filter Options
Right click the AND/OR folder to display the following options:
 
Option
 
Definition
Add Group
 
Adds another AND/OR folder.
Add Filter
 
Adds a filter under the parent folder.
Add Child Group
 
Adds a child AND/OR folder under the parent folder.
Add Child Filter
 
Adds a filter under the parent folder.