Page Comparison

Columns Panel

Using columns panel you can specify the columns which you want to be displayed in your query results.

You have a list of lines, each of them represents a column in the query results (in the Results Panel). Every line consists of Expression, which is an attribute, a Title, which serves as a column title in the Results Panel, and buttons – for sorting (on the left), aggregating and deleting (on the right).

Click to enlarge

Conditions Panel

Here you can specify filtering conditions for data selection. So the result of your query will include only the data that satisfy those conditions.

Every line in the Conditions Panel corresponds to a single query condition. Every condition consists of 3 elements – an expression (some entity attribute), an operator, and value(s) (one or more).

We have mentioned attributes above (e.g., ‘Company Name,’ ‘Product Price,’ and ‘Order Date’). Together with values, operators set certain limitations regarding data which is stored under a given attribute. For example, “(Date) is equal to 23 Jul 2014,” “(Quantity) is greater than 35,” “(Price) is less than 1,000,” and “(Name) starts with A.”

Click to enlarge

Basic queries: hot to make them with Advanced Search

1) Display Table Data

Let us imagine that we need a table, which contains following data: names of our customer companies along with countries of their origin, names of categories in which they ordered products, and a total sum of orders each of them made.

So, we select following entities and their attributes to add them in the Columns Panel:

1. Customer -> Company Name
2. Customer -> Country
3. Product -> Category
4. Order -> Total Sum

Clicking on the Execute button displays our query result just as we need it.

Click to enlarge

2) Table Data with Aggregated Columns

Advanced Search allows to aggregate data for each column in the query result. For example, we may need a total quantity (amount) of the orders for every company.

We are going to add the following columns:

1. Customer -> Company Name
2. Customer -> Country
3. Order -> Order ID (we need an attribute of the Order entity to calculate the amount of orders)
4. Click on the button and select the "Count" function
5. Change the title for last column to "Order Count" - just to represent its purpose.

Click to enlarge

We get the following result:

Click to enlarge

3) Table Data with Sorting

Let us use the previous example, in which, as a result, we get columns with data on:

• names of customer companies,
• countries of their origin,
• amount of products they paid for.

First steps are the same as in the previous example:

1. Customer -> Company Name
2. Customer -> Country
3. Order -> Order ID
4. We make the last line active and select Count after clicking on the Function button

This time, let us sort data by the number of orders, in the descending order. To do this, we

1. make the line Count of Order ID active, click on the sorting icon and select Descending

Click to enlarge

Results are displayed according to the number of orders made by every company, in the descending order.

Click to enlarge

4) Filtering Data 1 (One Condition)

We often deal with situations when, for example, of all the customer companies, we must select only those which have purchased goods worth 450 or more. In cases like this we should use the Conditions Panel for data sorting.

Let us do a sample query with the following data displayed: customer companies, countries of their origin, and total sum of their purchases. So, in the Columns Panel we select:

1. Customer -> Country,
2. Customer -> Name,
3. Order -> Total sum.

Now we switch to the Conditions Panel and:

1. add Order -> Total sum,
2. change the operator 'is equal to' to 'is greater than or equal to,'
3. and enter 450 in the value slot, as it is shown in the picture below.

It will look like this:

Click to enlarge

5) Filtering Data 2 (Several Conditions)

Often we have not one, but several conditions for filtering data. For example, we need a list containing information on:

• customer companies from Germany,
• whose purchases are worth more than 450,
• and those purchased products are in the 'Dairy products' category.

Firstly, we need to specify that, if (by default) every new condition is an additional limitation to the existing ones, they are connected by a conjunction 'and.' In this particular case, it absolutely suits us, but we will show you how to change this (when necessary) a little bit later.

Thus, when building a query, we indicate first which columns to display, through the Columns Panel:

1. Customer -> Company name,
2. Customer -> Country,
3. Order -> Total sum,
4. Product -> Category,

then we add the following to the Conditions Panel:

1. Order Total sum -> "is greater than" -> 450
2. and: Product Category -> "is equal to" -> Dairy products
3. and: Customer Country -> "is equal to" -> Germany

See how it looks below:

Click to enlarge

6) Filtering Data 3 (Several Conditions with AND and OR Conjunctions)

Let us suppose that the query conditions in the example 5) have changed to the following:

We need a list of customer companies which: 

• purchased a total of $450 worth of products
• in the Diary category,
• and their postal code contains 4 or 5.

In the Columns Panel, we select:

1. Customer -> Company name,
2. Order -> OrderID (f = Count),
3. Product -> Name
4. We keep 'all' in the Conditions Panel's topmost line ("Select records where all of the following apply")
5. Using a [+] button, we add a condition: Order -> Total sum -> "is greater than" -> 450
6. and another condition: (and) Product -> Category -> "is equal to" -> Dairy Products
7. Next to the first line at the top, we add a group of conditions with the help of a symbol {+} and leave 'any' in a newly formed line.

Consistently, after "and any of the following apply," we add two conditions, by clicking a [+] button:

1. Customer -> Postal code -> "is equal to" -> 4
2. (or) Customer -> Postal code -> "is equal to" -> 5

It will look this way:

Click to enlarge

7) Enabling/Disabling of Conditions

Let us assume that we are interested in a particular product - Aniseed Syrup. All we need to know is the list of companies that either have paid for their order, or have made an order starting from the first day of the year.
Therefore, we indicate the following in the Columns Panel:

1. Customer -> Company name,
2. Order -> Freight (f = Sum),
3. Product Name (we will use it only to illustrate the example better).

In the Conditions Panel, we will take a set of steps:

1. Product -> Product -> "is equal to" Aniseed Syrup
2. We create a group of conditions by clicking {+} and using 'any' in "and any of the following apply"
3. Order -> Paid -> "is true"
4. (or) Order -> Date -> "after (special)" -> First day of the year

Below is the visual representation of our steps:

Click to enlarge

And the result we get:

Click to enlarge

It can happen so that we will be needing only total sums of orders made after beginning of the year, or right the opposite - only orders which have been paid for (with no time restrictions).

There is an option of hiding particular conditions specifically for cases like this one. To the right of a condition itself, you will see a 'hide condition' button - [o]. By clicking on it, we get a broader range of data we want displayed:

Click to enlarge

The result is slightly different from the previous one:

Click to enlarge

Advanced Search: What’s That?

It is always good when there are just a few search parameters, and when one can perform a query with a single text line (for example, Full Text Search). Nevertheless, in most cases, you need to conduct search while operating several parameters at the same time. For example, “Find all male files that has blue trousers, an Omega watch and the appendix removed.” In order to specify the conditions of such a query, you will need more than just one text line.

In such cases, Advanced Search would be much of a help. It is a universal query builder UI for data search and filtering. It will allow you setting the search conditions of almost any difficulty, while using exclusively natural language, terms and expressions of which are familiar to user.

Advanced Search Components

The Advanced Search user interface consists of three main components – an File Attributes Panel, Search Conditions Panel, and Result Columns Panel. The picture below can help you understand which is what:

About File Attributes

While working with Advanced Search, you operate file attributes. They constitute something like the field structure of a DVI file that one can extract through construction of queries.

File Attributes Panel

This panel can be used as a visual representation of all attributes/fields of a DVI file, which we can use to build queries. It can be used to add entity attributes to a Columns Panel (and they will be displayed in the result) or to a Conditions Panel (in order to actually set the query conditions). You can either move selected attributes to the corresponding panels with a mouse or use buttons Add column and Add condition  ( ).  

	Select all attributes (never use this - will be removed)
	Remove selection from all attributes
	Add selected attributes to the Result Columns panel to include them in the search result
	Add selected attributes to the Search Conditions panel to be able to specify

Related articles