Functions and formulas

OLAP formulas perform the key functions of Application Studio. Most importantly, they retrieve data and let you manipulate it at different levels of the database hierarchy, format it and write it back to the database.

Of the OLAP functions, these are list functions, which can be used in server and relational lists, and in slices:

  • GETKEYS
  • GETKEYSA
  • GETKEYVALUESA
  • GETPROPERTIESA
  • GETPROPERTY
  • GETPROPERTYVALUES
  • GETPROPERTYVALUESA
With Microsoft Excel formulas and functions you perform calculations on data.
Note: All examples of syntax are in English and use the comma (,) to separate the arguments of formulas. Depending on your locale, it might be necessary to use the semi colon (;) as the separator.

All examples reference either the Analysis or Sales cube of the database of the Samples application. Samples can be enabled only in on-premises environments. For your reference if you do not have access to Samples, "Structure of the Samples database" gives an overview of the structure.

Example of the structure of an OLAP formula

This is an example of a read-only cell (ROC) formula and begins with =ROC. A read write cell (RWC) formula begins =RWC. 
=ROC("data connection","cube","[CHANNEL].[All Channels].[Direct Sales]","[MEASURE].[Gross Margin]"
,"[POS].[All POS]",B6,"[REGION].[All Regions].[Europe]",C5,"[VALTYPE].[Variance].[Actual]")
Note: In the formula, and in the Edit Formula dialog, the dimensions are ordered alphabetically by unique name. In the Database Structure, the dimensions are in the same order as in the OLAP database.

The formula defines a slice of data. The definition is enclosed in parentheses. The arguments of the formula are separated by a list separator. The arguments include the unique names of the dimension elements. These are in square brackets, separated by periods. In this example, the cell references, B6 and C5, are the key output cells of two hyperblocks.

The list separator depends on the regional settings of the operating system (by default, this is ",").

Data ranges in formulas

An OLAP formula comprises references to a data connection, cube and dimension elements.

You can edit an OLAP formula to calculate the sum of a range of elements. To do this, separate the elements in the range with two colons (::).

This example calculates the sum of the values for 2006 to 2010. 

=ROC("BestPracticesOLAP","SALES","[PROFIT].[Gross Margin]",
"[TIME].[All Years].[2006]::[TIME].[All Years].[2010]","[VERSION].[Actual]",
"[CURRTYPE].[LC]","[LEVEL].[IFRS]","[UNIT].[G0000]","[INTERCO].[TotalPartner]",
"[PRODUCT].[All Tires]")
Related topics