OCL operators for querying BI data
How to 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
Updated: 19.05.2025
|
Introduced with Vertec 6.8.
From Vertec 6.8 onwards, BI data can be accessed via OCL in Vertec lists.
Access to measures
With the following operator, you can access individual BI measures
bigetdata(measurename, from, to, currency)
The operator can be called on single 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 with this name, an error is returned. |
from, to |
Start or end date of the queried 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 the list settings, e.g. directly on a project:
argobject.evalocl("bigetdata('measurename', encodedate(2024,1,1), encodedate(2024,12,31), '')")
The OCL expression can also be applied to lists of objects, but then returns a list of numbers. You can sum these with ->sum, for example:
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 roles), the first occurrence/role is always used.
For more complex use cases (multi-dimensional, projections), you can use the Python function vtcapp.bigetdata(). See Python function 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, the operator
bigetdimensions(measure, from, to)
is executable on an OCL class.
The result is a list of objects of this class that have BI data for the passed measure 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 requested period. If the entire period is to be queried, |
For example, this expression returns a list of projects that have turnover 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
For example, a list should show all projects that generated turnover in a specific period of time.
The expression folder used in this example is filtered accordingly:
Projekt->bigetdimensions('TurnoverServices', varDatumVon, varDatumBis)
The variables varDateFrom and varDateTo are available by the field names (Date from, Date to).
The column expression for the turnover in the list is:
self->bigetdata('TurnoverServices', varDateFrom, varDateTo, '')
