Query expressions

Query Expressions are global functions that can be used to resolve organizations dynamically in organization based functions such as AGG, AGGAVG, INDEX and TOP functions.

 

The Query expression performs a search based on the current organization or another element you select.

 

Query expressions are created in Expressions in the View menu or under Structure in the Window menu. They are store in Categories, so you must create at least one category before you can create a Query expression.

Creating Query expressions

To create a Query expression, right click a Category in the Expression panel and choose Add -> Expression -> Query expression.

Properties

General

Filter

Create a set of conditions that define what you are looking for and where to look by selecting from the drop-downs in this section.

Now, create rules that define the conditions that are to be met. This is done by selecting the desired properties, operations and values from the drop-down lists, e.g. Name contains "school"

Specify:

All sub-organizations are always included when 'Current organization' is selected and no conditions are defined. Filters should therefore always be used to make the Query expression return only one level. If the query finds more than one level, organization entries may be duplicated in drills and have consequences such as department values being calculated multiple times as multiple levels of organizations will be included and the AGG function will then calculate the depth of all of those.


Example:

If you have a structure like:

 

and you only want to see the schools, then the filter must only include the schools.

You can use custom properties to filter on properties. Referring to the example above, you can create a text property on organizations and give it the value "District", "School", or "Department" respectively. Then, to select all schools, define the Filtered list condition Text property contains "School".

 

Use * and + in the function where Query expressions are used to include sub levels of organizations.

Example:
This function is used:

agg('[100]',{100})

If schools are returned in Query expression 100, then only values for the schools are used in the function.

 

agg('[100]',*{100})

will also include values for departments


agg(' [100]',+{100})

will show the schools but only include the values for departments

 

To create an additional rule in a set click Add new rule.

 

To create another rule set click Add new rule set.

 

Audit

Advanced

ID

 

Access control

Previewing Query expressions

To preview the result of a Query expression based on its current configuration, select it from the Business Management Platform model,

select the Preview tab, and then click the Refresh button ().

The Result subtab displays result values and their applicable range for each function in the expression.

To view a report on the calculations behind the expression, select the Profile tab.

 

Version 5.1.2.0

This document contains confidential information. Not to be copied by third parties without written authorization.

© Copyright Corporater AS - All rights reserved.