Service sums
How to use service sums and the groupServices OCL operator
Operating mode
Cloud Suite
On-Premises
Base module
Services & CRM
Additional modules
Budget & Phases
Purchases
Resource Planning
Business Intelligence
To optimize customer-specific evaluations or for application in user-defined reports, you can use an OCL operator for accelerated summation and grouping.
Operators
There are various kinds of groupServices operators, which differ mainly in that they are applicable to different object types:
| Operator | Description |
|---|---|
groupServices |
Global service sum operator. Refers to all services in the system. Since OCL operators must always be based on a specific data type, the operator is registered to |
groupServicesP |
Project service sum operator. Can be applied to a single project or to a list of projects. The fee values are output in the key currency. |
groupServicesPC |
Project service sum operator. Can be applied to a single project or to a list of projects. The fee values are output in the project currency. If this operator is applied to a list of projects, you must make sure that all projects in the list have the same currency. Otherwise, the amounts in the different currencies will simply be added together, which would result in nonsensical numbers. |
groupServicesPCG |
Global operator which calculates all project service sums in the project currency, without currency conversion. A global operator is not called on a single object, but on If you work with several currencies, you must group the service sums by project, otherwise values in different currencies will be summed up. Example:
|
groupServicesU |
User service sum operator. Can be executed on a single user or a list of users. The fee values are output in the key currency. |
groupServicesPh |
Phase service sum operator. Can be applied to a single phase or to a list of phases. The fee values are output in the key currency. |
groupServicesPhC |
This operator is called on phases and calculates the values in the project currency. |
groupServicesC |
Service sum operator. Can be executed on projects, phases and users and returns the values in any currency, calculated according to the indicated exchange rates. groupServicesC(from, to, group, currency) Expects a string argument for currency: If an empty string is given as currency argument, results are output in the key currency.
|
Syntax
The syntax for all these operators is as follows:
<obj>->groupServicesOperator(<from>, <to>, <group>)
The arguments (in chevrons/angle brackets) of the groupService operator have the following meanings:
obj |
Base object to which the operator is applied. Can be of type project, user or phase, single or list. In many cases, the base object is the result of a partial OCL expression. For groupServices, the operator must be called on the session. The current session can be called via ( |
from, to |
From, To date of a date period. Services are summed only for the specified period. The data from and to must be passed as a string in the format You must ensure that the German date format (dd.mm.yyyy) is passed, even with other language settings. Otherwise, there will be an error. For this (as of Vertec 6.2), you can use the method dateToStrGerman:
|
group |
Grouping statement. Controls the grouping and sorting of the resulting sums. Can contain various grouping terms, separated by commas. If an empty string is given, the sums are not grouped. Possible grouping terms:
|
The result is a list of objects of type ServiceSum, which are sorted according to the specified grouping.
ServiceSum
A service sum object has different attributes and a value for each grouping term:
| Member | Description |
|---|---|
| minutesExtOpen | Total of external minutes not yet charged. |
| minutesExtOpenUnprod | Total of external minutes not yet charged of services entered on projects with unproductive project types. |
| minutesExtCharged | Total of external minutes charged |
| minutesExtChargedUnprod | Total of external minutes charged of services entered on projects with unproductive project types. |
| minutesIntOpen | Total of internal minutes not yet charged. |
| minutesIntOpenUnprod | Total of internal minutes not yet charged of services entered on projects with unproductive project types. |
| minutesIntCharged | Total of internal minutes charged |
| minutesIntChargedUnprod | Total internal minutes charged of services entered on projects with unproductive project types. |
| servicesCount | Number of services in this service sum. |
| project | Project of this service sum, if grouped by project. |
| user | User, if grouped by user. |
| phase | Phase, if grouped by phase. |
| type | Service type, if grouped by service type. |
| invoice | Invoice, if grouped by invoice. |
| entrydate | Start date of the date group, if grouped by year, month or day. |
| feeUserOpen | Total fee according to the user rate, not charged. |
| feeUserCharged | Total fee according to the user rate, charged. |
| feeExtOpen | Total of external fee not yet charged. |
| feeExtOpenUnprod | Total of external fee not yet charged, for services entered on projects with unproductive project types. |
| feeExtCharged | Total of the external fee charged. |
| feeExtChargedUnprod | Total of external fee charged, for services entered on projects with unproductive project types. |
| feeIntOpen | Total of internal fee not yet charged. |
| feeIntOpenUnprod | Total of internal fee not yet charged, for services entered on projects with unproductive project types. |
| feeIntCharged | Total of internal fee charged. |
| feeIntChargedUnprod | Total internal fee charged, for services entered on projects with unproductive project types. |
| costOpen | Total of fee according to the cost rate, not yet charged. |
| costCharged | Total of fee according to the cost rate, charged. |
Example
On a project (or a list of projects) the groupServicesP operator is called with the following arguments:
- Date period is January to December
- Sums are grouped by months and projects
->groupServicesP('01.01.2024','31.12.2024','MONTH,PROJECT’)
As a result, we get a list of ServiceSum objects, including the following attributes:
- Month:
entrydate.formatDateTime('mm.yyyy') - Project:
project - Internal hours:
minutesintOpen + minutesIntCharged - External fee:
feeExtOpen + feeExtCharged
Special cases
Special cases for the calculation arise, for example, in the case of fixed-price invoices without services or in the case of invoice discounts. In this case, the external value of the services cannot simply be used for the calculation of the service sums. How these special cases are dealt with in detail is explained below:
| Special case | Calculation |
|---|---|
|
Fixed-price phases without services (if the phase is on a fixed-price invoice without services, see there) |
Is displayed as a service sum as follows:
|
| Fixed-price invoice without services |
Is displayed as a service sum as follows:
|
| Invoices with discount |
If there is a discount on an invoice, it is deducted pro rata from the feeExt of the service sum. The percentage is calculated as follows:
|
| Fixed-price phases and invoices with fixed services |
In the case of fixed-price invoices or fixed-price phases, the fixed price is distributed proportionally to the external fee of the services that are not fixed (see also fee for fixed-price phases and invoices), i.e. have not checked the fix external values checkbox. However, if ALL services of an invoice are fixed, then the fixed price on the phase or on the invoice cannot be allocated to the services. In this case, the service sum cannot reflect the fixed price. |
Total service sums <> total services
As a result of these special cases, deviations may arise between the sum of the services and the service sums. If, for example:
openServices->union(charged)->select((entrydate >= encodedate(2024,1,1)) and (entrydate < encodedate(2025,1,1))).wertExt->sum
returns a different value than:
self->groupServicesP('01.01.2024','31.12.2024','')->collect(feeExtOpen + feeExtCharged)->sum
check whether it has any of the above-mentioned special cases in the period analysed.
In the case of fixed services on fixed-price phases or invoices, no difference can be found here, since neither the services nor the service sums include the fixed amount. In this case, you will only see the difference if you compare the sums of the services with the sums of the invoices.
Restriction for large amounts of data and Firebird < 5
As Vertec ships with Firebird 5 as of version 6.7.0.9 and later, this issue no longer exists. However, if, with an earlier version of Firebird, a groupServicesOperator is called on a list of projects, phases, or users, the following limitation arises:
If the list of calling objects (i.e. not the number of services in the result, but the projects, phases or users on which the operator is called) is greater than 1500 entries, there is an error from the Firebird server due to invalid SQL:
General SQL Error. Implementation limit exceeded too many values (more than 1500) in member list to match against.
One workaround is to use the global groupServices operator, which applies to all services.
Example: If there are more than 1500 projects, and you want to call project->groupServicesP(...), you can work around this:
Session.allInstances->first->groupServices('01.01.2025', '30.06.2025', 'PROJECT')
Returns a list of service sums for all services, grouped by projects.
Performance behavior of service sums
Since the service sums are calculated optimally via SQL, the behavior of changes to basic data is slightly different than otherwise in Vertec.
Service sums are always recalculated as soon as any service is changed in the system. However, the recalculation only takes place if the service sum values are somehow visible. It is therefore not advisable to have a list of service sums on the screen at the same time as entering new services, as all sums would be recalculated with each new entry. Calculating service sums is relatively fast, but if you recalculate unnecessarily often, the delay is still noticeable.
In particular, it is not recommended to use groupServicesOperators in column expressions of lists, since the performance gain in calculating the sum is usually overcompensated by the large number of individual SQL queries.
Shadow attributes
Various values in Vertec are derived, which means that they are calculated each time they are accessed. This means that they are always up to date, but not retrievable in the database.
To ensure that these values are still available for the accelerated summation, there are so-called shadow attributes, which are written when calculating the derived attributes. These shadow attributes are persistent, i.e. they are stored in the database and can be queried directly.
The following shadow attributes are available. They are prefixed with sql.
| Attribute | Class |
|---|---|
|
sqlFeeExt |
Service, Expense, Outlay |
|
sqlMinutesExt |
Service |
|
sqlFeeInt, sqlDiscountAmount |
Expense and Outlay |
|
sqlRabattBetrag (only versions before 6.4.0.17) |
Invoice |
|
sqlValutaDatum (only versions before 6.4.0.17) |
Invoice |