Formulas

OLAP Server 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.

With Microsoft Excel formulas and functions you perform calculations on data.

Example of the structure of an OLAP Server 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("DATABASE_ALIAS","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 Server 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 Server formula comprises references to a database alias, cube and dimension elements. See Example of the structure of an OLAP Server formula.

You can edit an OLAP Server 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("Best Practices OLAP","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]")