GXquery enables you to obtain sorted lists, such as rankings, for example. There is a section in GXquery editor, known as “order by attributes area”, where you may include the attributes that will be taken into account in sorting information. This area is indicated with the "Drop order by attributes here" message, as shown below.
The order may also be established directly from any of the attributes present in the query’s result. To this end, we use the Order type property located the properties’ Format node, as shown in the image below.
In sum, in GXquery we can sort in two different ways: either based on the attributes existent in the result, or through the “order by attributes” area with which we can even sort by some attribute not present in the result.
Both ways of sorting are available in all types of queries in GXquery.
Sorting based on attributes present in the result
Attributes present in the result, known as query elements, are placed in rows, columns, series, etc., depending on the queries’ different types of output.
Order type property
Query elements include a property called Order type, which enables you to define a value depending on what is needed. This property is available for the query elements present in rows or columns (when it is a PivotTable), for all attributes (when it is a table), or for the attributes in categories (when it is a graph).
||Indicates that it will be considered in ascending order.
||Enables you to define a customized order for query elements showing values of a domain with an order different from numeric or alphabetic (like months or days of the week - see Custom order property below).
||Indicates that it will be considered in descending order.
||Values of the QueryElement are not sorted. This is the default value.
Custom order property
When the Custom value is selected in the Order type property, this property appears below it. It is meant to communicate to GXquery the order in which we want certain values. Consider the example of the days of the week: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday. If we order by the days of the week, the listing would be in this order: Friday, Monday, Thursday, Tuesday, Saturday and Sunday, but this is not what we want.
So, what we do is declare natural values in the property. To do so we press the magnifier to open the customization editor, as shown below.
Let’s suppose that the name of the query element, as shown on the image, is InvoiceDayOfWeek, and corresponds to a field of the Character type that stores the name of the day of InvoiceDate; in the small window we press the Insert button and enter each successive day. The final window would be as follows:
This will lead to the execution as expected. As shown in the image below, the information starts ordered by the first value in the series of seven: Monday.
Result of the InvoicesByDayOfWeek query
Sorting by attributes not present in the query’s result
GXquery offers the possibility to sort by query elements that will not be part of the set of data to be shown. Considering the example above, we now wish to view the same report but this time sorted by the ID of customers, a data that is not included in the information shown.
In this case, what we do is simply drag the Customer Id attribute to the “Drop order by attributes here” area. The result will be similar to the one shown below.
(We should note here that the order by day of the week in the example shown before is not considered, because when reading the article from the beginning could lead to the idea that this order is added to the one defined in the example above, while in fact this is the one used instead of the other one).
Result of the CustomerAmounts query
Hierarchy when both sorting types are applied at the same time
There is the possibility of applying both types of sorting (GXquery allows it), but it is important to bear in mind that the order according to attributes present in the result takes priority over the other group, which will not be considered at the time of exhibiting the information.