The screenshot on the left shows an excerpt from the 'Last modifier' column, for which not only the Restriction projection (as above) has been adjusted, but also the Projection, so that for Guest users the (positive) ID of the account appears with the prefix 'G#'.

As the filter expression (7251|2701) makes clear, the filter still refers exclusively to the positive ID values of the relevant accounts as desired.

►NOTE◄ The Projection uses a Case projection (see screenshot below), which compiles the display text for references to the Guest users in the right branch of the case differentiation using a simple Concatenated projection. Again, an Aggregate with the Type 'Absolute' (ABS) is used to eliminate the minus sign of the negative lastModifierId value. The default Subsearch projection, which looks up the user name in the account, was placed in the left branch of the case differentiation.

The tuple search uses the two Projections shown on the right:

• The first, a Property projection, identifies the parent entity (the relevant 'account') using the parentId reference property.
  ►IMPORTANT◄ This property must also be projected in the context of the Group by so that all items are aggregated per 'account'.
  
  

• As a second projection, an Aggregate with the Type 'Sum' (SUM) is used, which calculates the aggregated balance per 'Account' using the Name TOTAL.
  
  

• To ensure that the positive payment amounts stored for each line item are correctly offset against each other in the context of the Aggregate depending on the specified 'payment type', a sign reversal must be carried out for the amount in the payment.value property within the Projection if the 'payment type' (paymentType) begins with the code letter 'R':
  
  

  • A Case projection checks the paymentType via a Field restriction and uses an aggregate with the Type 'Negate' (NEG) in the THEN branch to reverse the sign for the numerical value from the Property projection payment.value.
    
    

  • In the OTHER branch (for deposits), the payment.value is 'passed through' unchanged.

►NOTE◄ The example uses the NEG calculation function here more by chance in the context of a 'real' aggregation function (SUM). Calculation functions can also be used at any time without an aggregation function and grouping, for example to 'adjust' values in conditions.

The Projection shown on the right, in conjunction with a Group by of the search by the 'ID' (id) property of the shipment – not shown here – provides the desired key figure for the 'complexity':

• The inner Aggregate with the Type 'Count (distinct)' determines the Group by 'variety' of values for the 'Parent line item no.' (parentLineItemId) property, taking into account the 'line items' (lineItems) per shipment.
  
  

• The outer Aggregate with the Type 'Square root' (SQRT) calculates the square root value from the aggregated numerical value from the inner Aggregate.

Runtime example:

ID,VOLUME,COMPLEXITY

8455,1,2.0
7955,10,1.7320508075688772
7951,,1.7320508075688772
4006,2,1.7320508075688772
9251,4,1.4142135623730951
9252,4,1.0

The example data on the left shows the calculated Doublevalues for 'complexity values' of √4, √3, √2 and √1 for the COMPLEXITY column.

The square root is used here because doubling the number of 'parent line items' in the hierarchy should not mean twice the complexity. However, quadrupling does.

The Aggregate shown on the right defines the output column DEACTIVATED by Name:

• The Type 'Not' (NOT) expects a Boolean value for the Projection, which is logically negated.
  
  

• The Projection uses a Property projection that accesses the 'Active' (active) Property.

Group by's are required as the basis for the use of an Aggregate with the Type 'Count' (see below) for the actual aggregation of user accounts, which can be configured as shown on the right:

• Within an Aggregate, the Type 'Upper' (UPPER) defines that the return value of the Projection should be converted to upper case.
  
  

• The Projection is a Sub string projection that returns the first character from the 'username' (username) property.

The following two columns are to be output as Projections for the CSV search:

• The first column to be output is the capital letter according to which the Users are grouped for counting purposes:
  
  

  • For formal reasons, the projection used in the Group by's (see above) must be used in the context of a real aggregation function. The Type 'Min' was selected here for the outer Aggregate. The corresponding Projection can be taken directly from the Group by's with this setting.
    
    

• The second column defines the numerical value for the number of Users who share the initial letter specified in the first column in the 'username' property by a further Aggregate:
  
  

  • The Type selected here is 'Count' (COUNT).
    
    

  • The Projection refers to the 'ID' (id) property, i.e. the unique identifier of the user account, which reliably contains a value for each Users.

The Field restriction shown on the right produces the desired adjustment:

• The check value (left) is a Sub string projection that returns the first two characters of the internal identifier for the user's 'locale' (locale). By convention (see Locale), these should be lowercase letters..
  
  

• As a compare value (right), an Aggregate with the Type 'Lower' (LOWER) converts the return value from the address property 'Country' (address.countryCode)into lower case letters. By convention (see Country), this is a combination of two capital letters in accordance with the ISO 3166 standard.

The Where condition shown on the right uses a Field restriction to check whether the text specified as 'Name' (name1) is different from the return value of the trim function for the same property (!=):

• The 'Name' (name1) property is accessed directly as a check value (left) via a Property projection.
  
  

• As a compare value, the same Property projection is used as a Projection within an Aggregate with the Type 'Trim' (TRIM).

►NOTE◄ A search with this Where condition returns all Addresses that can be accessed in the execution context because Address book entries refer to them. Addresses that were created in the context of other entities (e.g. Company accounts or Users) are not included in the search result, as long as they are not accessed via Joins, a Sub search or similar.

The example on the right shows the result of a tuple search with the following characteristics:

• There are 3 result rows (row), each of which shows the data for a specific company.
  
  

• The value (item) for the first column (COMPANY) identifies the company via its 'ID' (id).
  
  

• The second column (GUEST_ACCOUNTS) shows the number of all Guest users created for each company as an integer (long).
  
  

• The third column (GUEST_LOGINS) shows the total number of logins carried out for each company as an integer (int).
  
  

• The fourth column (LOGIN_AVG) shows the average number of logins per account as a decimal number (double).
  ► NOTE◄ The aggregated average value can be 'recalculated' in the given scenario by dividing the aggregation results in columns 3 and 2 (GUEST_LOGINS/GUEST_ACCOUNTS) for verification purposes.

   <core:TupleSearchResult maxResults="100" count="3">
      <columns>
         <name>COMPANY</name>
         <name>GUEST_ACCOUNTS</name>
         <name>GUEST_LOGINS</name>
         <name>LOGIN_AVG</name>
      </columns>
      <result>
         <row>
            <item xsi:type="xsd:long">101</item>
            <item xsi:type="xsd:long">2</item>
            <item xsi:type="xsd:int">0</item>
            <item xsi:type="xsd:double">0.0</item>
         </row>
         <row>
            <item xsi:type="xsd:long">1902</item>
            <item xsi:type="xsd:long">12</item>
            <item xsi:type="xsd:int">179</item>
            <item xsi:type="xsd:double">14.916666666666666</item>
         </row>
         <row>
            <item xsi:type="xsd:long">2202</item>
            <item xsi:type="xsd:long">2</item>
            <item xsi:type="xsd:int">9</item>
            <item xsi:type="xsd:double">4.5</item>
         </row>
      </result>

As our search is intended to aggregate key figures for Guest users per company, Group by's must be defined for the search:

• The screenshot shows (top right) a Property projection on the 'Company' (companyId) property, which is set up for this purpose.
  
  

• As the screenshot (bottom right) shows, the same Property projection should also be used as a Projection in the context of Order by.
  
  ►NOTE◄ It is not necessary to define Order by. This sorting criteria should only be defined if the Order by represents added value for the intended use. Instead of Projections from the Group by, aggregation results could also be accessed within the Order by and in the given example, for example, the companies could be sorted in descending order by the number of guest users.

The screenshot on the right shows an overview of the Projections set up for the tuple search, which define the output columns:

• The first column (COMPANY) uses the Property projection on the 'Company' (companyId) property, which is also specified as a Group by.

• The configuration for the following 'statistics columns' is explained step by step below.

The GUEST_ACCOUNTS column uses an Aggregate with the Type 'Count' (COUNT) with a Property projection for the 'ID' (id) property of the Guest users to be aggregated.

For the 'Count' aggregation type, the property selection is irrelevant as long as it is ensured that it cannot contain 'No value' ($null). Otherwise, Guest users for which the field does not contain a value in the Projection are not counted.

Without an entry for the Name parameter, the title of the output column would be 'COUNT of id'.

The GUEST_LOGINS column uses an Aggregate with the Type 'Sum' (SUM) with a Property projection for the 'Number of logins' (loginCount) property of the Guest users to be aggregated.

The 'Sum' aggregation type adds up all the numerical values found. Whether the property in the Projection is filled for all aggregated Guest users is irrelevant for the value returned as 'Sum'.

Without an input for the Name parameter, the title of the output column would be 'SUM of loginCount'.

The LOGIN_AVG column uses an Aggregate with the Type 'Average' (AVG) with a Property projection for the 'Number of logins' (loginCount) property of the Gastbenutze to be aggregated.

The 'Average' aggregation type relates the sum of all values from the Projection to the number of aggregated individual values that are not 'No value' ($null). Since the loginCount property is initialized with the value 0 and cannot contain a $null value on the database side, the interpretation of the mean value in the context is not critical.

Without an entry for the Name parameter, the title of the output column would be 'AVG of loginCount'.

The key figures for the evaluation should 'aggregate' all companies for which read access exists in the execution context.

This means:

• The search works without grouping.
  
  

• Only exactly one result line is expected (see example data), if a company (with read access) exists at all.

   <core:TupleSearchResult maxResults="100" count="1">
      <columns>
         <name>COMPANIES</name>
         <name>WITH_COUNTRY</name>
         <name>COUNTRIES</name>
      </columns>
      <result>
         <row>
            <item xsi:type="xsd:long">40</item>
            <item xsi:type="xsd:long">28</item>
            <item xsi:type="xsd:long">10</item>
         </row>
      </result>
   </core:TupleSearchResult>

The COMPANIES column should show the number of all evaluated Company accounts.

The Type 'Count' enables this in conjunction with a Property projection that refers to the 'ID' (id) property of the company. The result provides the number of Company accounts for which read access exists, subject to two conditions:

1. The Projection does not return 'No value' ($null) for any company, which is certainly the case for the 'ID' property.

2. There is always exactly one result line per company. This applies as long as no Projections on multi-value properties are used (see the following variant).

The WITH_COUNTRY column shows the number of all evaluated Company accounts in whose address field 'Country' (countryCode) a Country is selected.

Since the Type 'Count' only takes into account the values from the Projection that are not $null, it uses the Property projection on the path to the property in question (address.countryCode) to return exactly the number searched for, as long as it is guaranteed that none of the other Projections affect a multi-value property (see the following variant).

The COUNTRIES column is intended to indicate the number of different 'Countries' found in the addresses of the evaluated companies.

The Type 'Count (distinct )' (COUNT_DISTINCT) enables a Property projection in connection with the address property 'Country' (countryCode), as in the previous column.

►IMPORTANT◄ The condition that no country is specified in the address is not counted. If the 'Count (distinct)' results in the value 1, this does not mean that all Company accounts are located in the same country, but only that no company address refers to a different Country.

In contrast to the Type 'Count', the Type 'Count (distinct)' is fundamentally resistant to the scenario that a multi-value projection generates several result rows per company (see following variant). The variety of values in the Projection does not increase if the same values are mentioned repeatedly.

An Aggregate with the Type 'Count' should include all Company accounts that have at least one parent company.

For this purpose, the Projection must return any value other than 'No value' ($null) if the number of parent companies is >0.

• The Case projection within the Projection formulates this condition via a Field restriction and arbitrarily assigns an empty string as the return value via a Literal projection if the condition is fulfilled. Otherwise, the return value is 'No value' ($null).
  
  

• As a check value for the Field restriction, the number of parentCompanies of the company account to which the parent search refers must be determined. This number can only be determined in the context of an Aggregate via a Subsearch projection. Direct nesting of aggregation functions is not permitted.
  

  • The 'Company account' type is again selected as the Entity for the sub-select.
    
    

  • The Projection for column of subselect defines the Aggregate for determining the 'number' of parent companies (parentCompanies).
    
    

  • The Property to join and Property of entity to join with parameters require a match with regard to the 'ID' (id) property. The subselect is used here as an exception to map a (1:1) relation. The inner aggregation result ('Number of parent companies') should logically be assigned to the exact company for which it was collected.

COMPANIES

Previously, the Company accounts could be counted using the Type 'Count' (COUNT) because exactly one line was output per company.

The Aggregate are now converted to the Type 'Count (distinct)' (COUNT_DISTINCT), so that each company 'found' by the search (identified by the unique 'ID') is only included once in the count.

WITH_COUNTRY

The previous Aggregate utilized the effect that an aggregation with the Type 'Count' (COUNT) only counts values from the Projection that are not 'No value' ($null).

Instead, we now use a Case projection within the Projection to explicitly check whether the address property 'Country' (countryCode) to be evaluated contains a value. Only then should the Case projection transfer the 'ID' (id) of the company return value to the Aggregate.

For the Aggregate, the Type is changed to 'Count (distinct)' (COUNT_DISTINCT). This compensates for multiple mentions of the same 'ID' value if the return value is 'multiplied' because the additional projection for the HAS_PARENT column (see below) finds several parent companies.

COUNTRIES

The projection for the COUNTRIES column can be retained unchanged, as the Type 'Count (distinct)' (COUNT_DISTINCT) is already used here.

Multiplying the input values for the aggregation does not change the variety of countries.

unchanged:

HAS_PARENT

not yet available
(or realized via Subsearch projection; see above)

The projection for the HAS_PARENT column reproduces the concept for the WITH_COUNTRY column:

• In the Projection, the 'parent companies' list (parentCompanies) is checked for content using Case projection.
  
  

• The Field restriction applies to each listed reference for a parent company. Only then should the Case projection transfer the 'ID' (id) of the child company return value to the Aggregate.
  
  

• The Aggregate use the Type 'Count (distinct)' (COUNT_DISTINCT), so that each ID of a 'child' company is simply included in the count. The IDs of companies without a parent company do not appear in the count anyway because there is no ELSE branch with a projection in the Case projection.