Query by Example
Viewer users can access the Query by Example or QBE line; however, this functionality is turned off by default and must be enabled by the Power User when designing the inquiry. When enabled, you can use the QBE line to narrow down the search results. The QBE line can be used in conjunction with a filter (again, if the filter has been made available), so the two criteria work in conjunction to return the data being selected.
To use the QBE line, double-click in the white space above the relevant column and type in the search criteria. In the example below, you see two different filtering mechanisms; exact matching and wildcard matching.
Exact Matching
For example, to search for Object Account 5010, enter the exact value into the Object Account Column in the QBE line. All accounts matching this value will be displayed.
Wildcard Matching
Some text is used to identify selections, giving a match at the beginning, end, or both beginning and end of the character string. For example, the asterisk character (*) is used to search for Object Accounts that start with 51, e. g. 5110, 5150, etc.
Had the asterisk appeared prior to the value 51 (e. g. *51), then Object Accounts such as 1151, 1351, 2551, etc. would be returned using this criteria. Searching on *Sales* would return ‘Store Sales’ and ‘Wholesale Sales’ as possible selections.
Note: Filtering based on exact matching is the quickest filtering mechanism. Full wildcard matching is the slowest. Therefore, it is recommended that you use the most appropriate matching mechanism for your desired search.
Entering multiple filter criteria within a single QBE filter or column
You can enter multiple filter criteria in a single QBE filter. For example, to search for items with ‘Banks’ and ‘Cash’ in the object account description, the QBE would *Banks*,*Cash*.
QBE options
The options listed below can be used in any combination by separating each value with a comma. The following can be used with either text (label) or numeric (value) columns:
OPTION |
EXAMPLE |
DESCRIPTION |
|---|---|---|
Equal |
= |
Combined with other options, this will return all values equal to the selection. If not specified otherwise, an equal sign is always assumed. |
Not Equal |
<> |
Combined with other options, this will return all values not equal to the selection |
Item |
A |
Items that are equal to the one item listed in the filter |
List of Items |
A,B,C |
Items that are equal to the list of items in the filter |
Range |
A:C |
Items that are in the range of values in the filter |
Wildcard |
C* |
The wildcard is positional and will bring back all the items that start, end or contain a specific value (this example would return all values that start with the letter C) |
Blank |
<space> |
A space represents blank values in the QBE line |
The following can be used with text columns (labels):
OPTION |
EXAMPLE |
DESCRIPTION |
|---|---|---|
Greater |
>C |
Items that are greater than the value specified in the filter |
Greater Than or Equal To |
>=C |
Items that are greater than or equal to the value specified in the filter |
Less Than |
<C |
Items that are less than the value specified in the filter |
Less Than or Equal To |
<=C |
Items that are less than or equal to the value specified in the filter |
The following can be used with numeric columns (values):
OPTION |
EXAMPLE |
DESCRIPTION |
|---|---|---|
Greater Than |
>10 |
When a number is entered, it is treated as an absolute value .This example would return items that are greater than 10 or less than -10) |
|
Greater Than or Equal To To |
>=10 |
When a number is entered, it is treated as an absolute value (this example would return items that are greater than or equal to 10 and less than or equal to -10) |
Less Than |
<10 |
When a number is entered, it is treated as an absolute value (this example would return items that are less than 10 and greater than -10) |
|
Less Than or Equal To Number |
<=10 |
When a number is entered, it is treated as an absolute value. This example would return items that are less than or equal to 10 and greater than or equal to -10. |
Greater Than Signed |
>+10 |
When a specific sign (‘+’ or ‘-‘) is used, only values that are greater than that value will be returned. This example would return items greater than +10). The same logic can be used for negative values with a ‘-‘ symbol. |
| Number | ||
|
Greater Than or Equal To Signed Number |
=+10 |
When a specific sign (‘+’ or ‘-‘) is used, only values that are greater than or equal to that value will be returned. This example would return items greater than or equal to +10). The same logic can be used for negative values with a ‘-‘ symbol. |
| Less Than Signed Number | <+10 | When a specific sign (‘+’ or ‘-‘) is used, only values that are less than that value will be returned (this example would return items less than +10). The same logic can be used for negative values with a ‘-‘ symbol. |
| Less Than or Equal To Signed Number | <+10 | When a specific sign (‘+’ or ‘-‘) is used, only values that are less than or equal to that value will be returned (this example would return items less than or equal to +10). The same logic can be used for negative values with a ‘-‘ symbol. |
Invalid selection in the QBE line
Unlike filters, there is no warning message to tell you that you have entered in invalid data into a QBE line. Data selections made in the QBE line are not validated against your ERP data. If the selection you enter does not match any data, no results will be returned.
Multiple values in the QBE filter that are all ‘not equal to’ values
To enter in multiple ‘not equal to’ values, you need to put the ‘not equal to’ sign before each value, for example <>301, <>302, <>303.
Filters in combination with a QBE line
You can narrow down your data selection using both filters and the QBE line. For example, you could define a range in the Object Account filter and use the QBE line to ignore a specific object account within that range:
Null Values
You can filter over null values by either using the Query by Example (QBE) line OR by using a Filter.
- To filter over null values by using the QBE line:
-
Use the equals sign (=) in the specific column for which you want to filter over null values.
In the example below, the = sign in the Subsidiary column results in all rows being returned
with a null value in that column:
-
When you run the report, the text in the QBE changes to “=NULL”:
-
Use the equals sign (=) in the specific column for which you want to filter over null values.
In the example below, the = sign in the Subsidiary column results in all rows being returned
with a null value in that column:
- To filter over null values using a filter for any inquiry that uses more than one table in either a standard inquiry or one that has been created by Designer Express:
- From within an inquiry, right-click on the filter for which you would like to display nulls and select Include Nulls.
Examples using the Include Nulls and Include Non-Matching options:
In the first example, we will use an HR inquiry.
When filtering on Date Worked from the F06116, we only get active employees who have a timecard record for 1/1/2005 through 4/15/2015. 35 rows are returned:
- Change the filter to include nulls. This shows active employees with timecards between 1/1/2005 and 4/15/2005
AND active employees with NO timecards in the F06116. 53 records are returned.
- Change the filter to include non-matching rows. This shows all the active employees
in the F060116 and those employees that have a timecard in the F06116 for the dates 1/1/2005 thru 4/ 15/2005.
136 records are returned.
- For the 2nd example, the scenario is that you have a report that combines customer data with address data and you want to filter on a country, say the United States.
- If you only say "US" in the filter, then the report will return just the customers with an address in the US.
- If you say "always include nulls", it will also include rows where the country was set to Null.
- If you say "include no match", it will include all customers but will only display address data for customers from the US.