Free trial

Python function for querying BI data

How to query BI data using Python

Operating mode

Cloud Suite

ON-PREMISES

Modules

Services & CRM

Budget & Phases

Purchases

Resource Planning

Business Intelligence

Created: 02.05.2025

Updated: 19.05.2025 | Python function introduced with Vertec 6.8.

Starting with Vertec 6.8, you can now use the Business Intelligence Module (BI) figures outside the BI views, for example in lists or reports. With this, even complex calculations can be shown without sacrificing performance, as the pre-calculated BI figures are accessed.

For this, there is a new Python function:

 vtcapp.bigetdata(source, dimensions, measures, from, to, currency=None)

source	The source can be a single object or a list of objects for which the BI data is to be read. In this case, only the values belonging to these objects are taken into account. If source is `None`, all available data in the time interval are taken into account.
dimensions	Here, the desired dimensions are specified as a list. In contrast to the BI interface, if you specify several dimensions, the values are grouped according to them. For example, if `["Projekt", "Projektbearbeiter"]`is passed, then a result line is obtained for each occurring combination of project and user. The following values can be passed: For a simple dimension, the class name (string) For a dimension with role, a tuple with the name of the dimension and the class name with dimension index (order), e.g. ("Projektleiter", "Projektbearbeiter_1") ("Bearbeiter", "Projektbearbeiter_2") For a time dimension, the string value `"Month"`. If at least one key date value is present in the measures, then the time dimension (`"Month"`) must be present in the dimensions, otherwise there will be an error. For a calculated dimension, a tuple consisting of the name of the calculated dimension, the class name, and an OCL expression: (`<name>, <classname>, <OCL expression>`). The OCL expression is evaluated on the result dimension objects and leads to the actual result of the calculated dimension (e.g. for evaluation by customers of a project). For a folder projection, a tuple consisting of the dimension name, the class name and the folder object whose subfolders represent the displayed dimensions: (`<name>, <classname>, <ordner>`).
measures	A list of BI measures. The list can be passed either as a string list with the names of the measures, or as a list of measure objects.
from, to	Date values, which designate the start or end point of the evaluation. If `None` is specified, start or end is open.
currency	Optional. Currency abbreviation or a currency object from Vertec. Indicates the currency in which the data should be output. Only necessary if currency data (amount, rate) are output. If no `currency` is specified, the key currency is used.

The return value of bigetdata() is an auxiliary object with the following methods:

get_dimensions()

Returns a list of all (found) dimension tuples.

get_value(dimension_tuple, measure_name, default_value=0)

Returns the value of a measure for a dimension tuple.

dimension_tuple: Dimensionstupel. The dimensions can be determined with the get_dimensions()method. If only one dimension is used, the dimension object can be passed instead of the tuple.
measure_name: Internal name of the measure to be determined.
default_value: Optional. If the measure for the given dimension tuple has no value, the default value is 0. This parameter can be used to write a different value to the cell, such as None for an empty display.

To use this Python function, a licensed Business Intelligence module is required; otherwise, an error message appears.

Examples

The following examples can be run in the Script Editor. They output the result in the output window and should help you understand the mechanism.

Simple example with one dimension and one measure

projects = vtcapp.currentlogin().evalocl("eigprojekte->select(aktiv)")
result = vtcapp.bigetdata(projects, ["Projekt"], ["FeesExt"], None, None)

for project in projects:
    # standardmässig "0", wenn es für eine Kennzahl keinen Wert gibt
    print("Fee: {}".format(result.get_value(project, "FeesExt")))

    # alternatively, you can specify a default value, here None
    print("Fee: {}".format(result.get_value(project, "FeesExt", None)))

Example with dimensions and roles

projects = vtcapp.currentlogin().evalocl("eigprojekte->select(aktiv)")
dimensions = [
  ("Project manager", "Projektbearbeiter_1"),
  ("User", "Projektbearbeiter_2")
]
measures = ["MinutesInt"]
result = vtcapp.bigetdata(projects, dimensions, measures, None, None)

for key in result.get_dimensions():
    text = "Project manager {}, User {}\nHours {}".format(
            key[0],
            key[1],
            result.get_value(key, "MinutesInt"))
    print(text)

Python function for querying BI data

source

dimensions

measures

from, to

currency

get_dimensions()

get_value(dimension_tuple, measure_name, default_value=0)

Examples

Simple example with one dimension and one measure

Example with dimensions and roles

Schweiz

Deutschland

Österreich

Netherlands

United Kingdom