Expressions in-line in GXqueryOfficial Content

In-line expressions are used to define different types of calculations in the database’s tables.

They may be defined in GXquery the same way as they are defined in GeneXus, and they are included in the list of attributes.

In-line expressions supported are Sum, Count, Average, Max and Min, and they are included in GXquery’s metadatas in two different ways::

  • They are received as formula attributes in the metadata included by the manager.
  • They are created as in-line in the query.

In the first case, the aggregation is defined in a formula attribute, regardless of whether it was received in GXquery in the metadata, or if it was created as UDA in GXquery.

When the formula is defined in-line, the following syntax is used.

Sum | Average | Count | Max | Min (attribute [, where(<condition before aggregate>)]) [where(<condition after aggregate>)] )

Where:

attribute is the name of the attribute on which the formula will be executed.

<condition before aggregate> is the condition considered in runtime of the formula.

<condition after aggregate> is the condition considered after the formula’s execution.

Note

The aggregation formula Find is not supported in in-line mode.

Example

In the example below, the addition involves the total amounts of invoices of the first hundred customers and whose resulting sum exceeds one million.

Sum(InvoiceAmount where(CustomerId < 101)) where(>1000000)

How to create an in-line expression

In-line expressions may be created in any type of graph supported by GXquery. In the following exercise we will create a Pivot table, we will use the default formula assigned by GXquery, and we will make changes in it as necessary. 

To begin, select the TravelAgency metadata in the Metadata combo box. Then select New pivot table (if you are not familiar with creating Pivot tables read HowTo: Build a Pivot table).

Write the name TotalTicketsAmountByFlights in each Name box.

In the Attributes panel, open the Flight Instance folder and drag and drop the following attributes in the “Drop row attributes” area”:

GXquery4 - QueryElement icon  Flight Instance Date
GXquery4 - QueryElement icon  Flight Departure City Name
GXquery4 - QueryElement icon  Flight Departure Airport Name
GXquery4 - QueryElement icon  Flight Arrival City Name
GXquery4 - QueryElement icon  Flight Arrival Airport Name

Now drag and drop the following attribute in the “Drop data attributes" area:

GXquery4 - QueryElement icon  Flight Final Price

Double click on the Query Element FlightFinalPrice, which represents a formula that is the list price minus the discount, and the following window will appear:

GXquery4 - Structure formula window

We can see that the tab opened by default is Formula, and GXquery has already included the most basic one for an attribute of the numeric data type: Sum. Press OK and then View. You should see an image similar to the one below.

GXquery4 – TotalTicketsAmountByFlights

We now modify the formula so that it shows us only the records with amounts greater than 3000 and the flight numbers lower than 14000.

We edit the query element again, erase the formula and replace it by writing the following:

Sum (FlightFinalPrice where (FlightId < 14000)) where (> 3000)

Press OK and then press View. Now you should see an image like the one below.

GXquery4 – TotalTicketsAmountByFlights with Sum formula 1

Another example of use could be to create an expression to give us the difference in sales that exists between two years.

We create a Table, for example, and press the GXquery4 - Insert green corner symbol symbol in the upper right corner of the attributes area to open the New query element window.

GXquery4 - New query element

Enter AnnualDifference in name and then press the Formula tab. Click on the expressions edition area and write the following:

(Sum(FlightFinalPrice) where Year(InvoiceDate)=2013)-(Sum(FlightFinalPrice) where Year(InvoiceDate)=2012)

…and then press View to check the result.

Other examples of use:

InvoiceAmount / 1.22
InvoiceSubtotal * 2
Sum(CustomerBalance + InvoiceAmount)
Sum(InvoiceTaxes + InvoiceNonTaxes)
Sum(InvoiceTotal) / Count(InvoiceId)
Month(InvoiceDate) = 10 And Year(InvoiceDate) = 2013

Related info

Query object expressions

 

Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.