Filtering the query results

Every query is built to retrieve a specific set of results.

When we define a query, the data can be processed and analyzed in several possible contexts:

• Time: This gives information about when an event occurred; for example, the sales for the current week (a "when" business question)
• Data type and specification: This gives the model specifications of the cars that were rented or sold (a "which" business question)
• Measure: This gives a range or a value that is greater or less than (a "how much" business question)

Without a filtering context, a query is somewhere between serving a meaningless purpose and answering a very general question. The main idea when creating query filters is to fetch only the data that is relevant to the business question. The rule of "garbage in garbage out" applies here. By defining all the relevant conditions, we ensure that we will get accurate results as well as better performance.

Every condition is structured from the following three main parts:

• Object: Every condition is based on an object from the universe
• Operator: This is a logical operation that the condition uses, for example, equal to or greater than
• Operand: This part can contain a value, a constant, a prompt, or even use other query results

Getting ready

We want to add a filter to the query that will present only the sales for the United Kingdom clients.

How to do it...

Drag the Client Country to the Query Filters pane.

In the second part of the condition, click on the arrow. A drop-down list of operators will open. We will pick the Equal to operator, as shown in the following screenshot. This operator enables you to choose only one value.

In the third and last part of the condition, we will pick the second option, Value(s) from list, as shown in the following screenshot:

In the next window that will appear, we pick the value United Kingdom from the list and click on the OK button at the bottom-left side of the window, as shown in the following screenshot:

After filling the three parts of the condition, it will be set and the query is ready to retrieve the results based on this query filter, as shown in the following screenshot:

We will click on the Run query button and get the relevant results. There's a whole bunch of operators that can be used:

• In list: This enables you to pick more than one value of the object. This operator uses an OR operator on the values.
• Not in list: This enables you to retrieve data that doesn't correspond to more than one value.
• Equal to: This enables you to pick only one value.
• Not equal to: This enables you to pick a value that is different from to the Equal to operator.
• Greater than: This will retrieve data which is greater than a value. It is very useful for date, measure, age, and numeric data.
• Greater than or equal to: This retrieves data that is greater than or equal to a value (includes the value we are matching plus anything greater than it).
• Less than: This operator retrieves which is data lower than a value.
• Less than or equal to: This retrieves data that is less than or equal to a value.
• Between: This retrieves data between and including two values. It is very useful for date range and measure range filtering.
• Not between: This retrieves data that is outside the range of two values.
• Is null: This retrieves records that have a null value. A null value can signify an event that has not happened, for example, if there is a client in the database who hasn't paid, he will have a null pay date. We can use this to query which customers haven't paid yet.
• Is not null: This retrieves records that do not have a null value.
• Matches pattern: This operator retrieves data that includes a specific string or part of a string. This is a search operator that enables us to identify data by running a search for it. In order to identify strings that start with the X character, we will use the syntax X%. For strings that contain the X value, we will use the syntax %X%, and for strings that end with the X value, we will use the syntax %X.
• Different from pattern: This returns data that doesn't include a specific string.
• Both: This retrieves data that corresponds to two values by establishing an AND operator between them; for example, who bought products A and B?
• Except: This retrieves data that corresponds to one value and excludes another.

  Tip

  You can pick more than one value from a list by using the In list operator. Holding the Ctrl or the Shift key and pressing the down arrow will enable you to mark several values at once.

How it works...

A query is simply a set of result objects combined with a set of conditions. A condition in the query runs a search for all the rows in the database tables that correspond to the value or values in the operand.

By choosing a specific value, we define which rows will be retrieved, and the result objects will display the data that corresponds to the filter.

Tip

Note that we didn't use the Client Country in the result objects. This is the rule for single-value returned objects as they bring nothing new to the rows' display as they will repeat the same value in each row. If these types of objects are used to display informative titles, then they can be useful in the report.

There's more...

A simple condition can also be created by dragging an object from the Result Objects pane to the Query Filters pane.

Another practical operand type is Object from this query. This option enables us to compare objects of the same type, such as dates and just values.

Using this operand can help us answer business questions such as which products were shipped on the same day they were ordered and which customer with a current purchase purchased the same product the previous time they purchased a product? This is illustrated in the following screenshot: