OCL operators for querying BI data
Access Vertec Business Intelligence (BI) data via OCL
Operating mode
Cloud Suite
|ON-PREMISES
Modules
Services & CRM
Budget & Phases
Purchases
Resource Planning
Business Intelligence
Created: 02.05.2025
Machine translated
Updated: 02.05.2025
|
Available from Vertec 6.8
From Vertec Lists 6.8 onwards, BI data can be accessed via OCL in Vertec Lists.
Access to measures
For access to individual Bi measures there is the operator
bigetdata(measurename, from, to, currency)
callable on individual objects. The expression returns the sum of all values of this measure for the calling object in the specified time period.
measurename |
Internal name of the desired measure. If there is no measure of this name, an error is returned. |
from, to |
Start or end date of the query value. If all values are to be queried, |
currency |
Optional. Currency abbreviation of the Currency in which the data should be output. If nothing or a space is passed, the key currency is used. |
The OCL expression can be used anywhere, not just in list settings, e.g. directly on a project:
argobject.evalocl("bigetdata('measurename', encodedate(2024,1,1), encodedate(2024,12,31), '')")
It can also be applied to lists of objects, but then returns a list of numbers. ->sum summing:
project.evalocl("phasen->bigetdata('measurename', DateTime.null, DateTime.null, '')")->sum
The class of the calling object is always used as the result dimension.
For classes that occur in more than one dimension in the evaluated measure (with different Role), the first occurrence / Role is always used.
For more complex use cases (multi-dimensional, projections), the Python feature can vtcapp.bigetdata() be used, see Python feature for querying bi data.
Access to objects that have BI data
To get a list of objects that have BI data in a certain period of time, there is the operator
bigetdimensions(measure, from, to)
executable on an OCL class.
The result is a list of objects of this class that have BI data for the given measure in the given period.
measurename |
Internal name of the desired measure. If there is no measure of this name, an error is returned. |
from, to |
Start or end date of the requested period. If the entire period is to be queried, |
For example, this expression returns a list of projects that have revenue in the first quarter of 2025:
Project.bigetdimensions('TurnoverServices', encodedate(2025,1,1), encodedate(2025,3,31))
In this way, only the objects that have certain values can be shown very efficiently.
Example
As an example, a list should show all projects that generated sales in a certain period of time.
The expression Expression folder used for this is filtered accordingly:
Projekt->bigetdimensions('TurnoverServices', varDatumVon, varDatumBis)
The variables varDatumVon and varDatumBis are available by the field names (date from, date to).
The column expression for the turnover in the list is:
self->bigetdata('TurnoverServices', varDatumVon, varDatumBis, '')
