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.
The aggregation formula Find is not supported in in-line mode.
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)
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”:
Flight Instance Date
Flight Departure City Name
Flight Departure Airport Name
Flight Arrival City Name
Flight Arrival Airport Name
Now drag and drop the following attribute in the “Drop data attributes" area:
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:
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.
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.
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 
symbol in the upper right corner of the attributes area to open the New query element window.
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
Query object expressions