CELLGET

Gets values of a cube (ODBO and XMLA-compliant databases only) based on the elements you specify. It is similar to an ROC formula but is dynamic. That is, you do not have to specify all the elements of a dimension (the default element is used for the dimensions which you do not specify). This is particularly relevant to report templates. When creating a report from a template, users can select the cube and dimensions to use. The designer of a template can specify, for example, that users must select a dimension which contains products, and a dimension which contains time periods, but cannot specify the actual dimensions to be selected. So, any formulas which the template designer inserts and which reference those dimensions, must be generic. That is, they must not reference particular elements because those elements will not necessarily be available in the dimensions that a user selects.

Also, the order in which you specify the dimensions is not important. You can reference a range of cells in a CELLGET formula, instead of referencing each one individually.

CELLGET supports the selection of multiple elements from a single dimension.

Syntax

=CELLGET(Database Alias,Cube,"Slice element dimension 1",..., "Slice element dimension N")

Example

=CELLGET( "Best Practices OLAP", "Analysis", "[Period].[All Years].[2002]",
"[Product].[Car Tires All Season]")

You can use CELLGET to reference elements from multiple dimensions individually or as a range. So, if element names were in the referenced cells, these two examples would produce the same result:

=CELLGET("Best Practices OLAP", "Analysis",B4,B5,B6,B7)

=CELLGET("Best Practices OLAP","Analysis",B4:B7)

When you change the value of the cell that contains the CELLGET formula, the value is written back to the database.

You can use a CELLGET formula to reference a cell within a hyperblock.

Multiselect

CELLGET supports multiselect. You can replace any of the elements in a CELLGET formula with references to valid multiselect XML (MLS-XML). The references can be to cells or to variables which contain MLS-XML.

In this example, the elements 2002 and 2003 are stored as MLS-XML in a report variable:

=CELLGET("Best Practices OLAP","ANALYSIS","[CHANNEL].[All Channels]","[MEASURE].[Gross Margin]",
"[POS].[All POS]","[PRODUCT].[All Tires]","[REGION].[All Regions].[Europe]",
ReportVariables.rv_multi.Text,"[VALTYPE].[Variance]")

You can also use a range to specify multiple elements of a dimension:

In this example, the 2002 and 2003 elements are specified in a range.

=CELLGET("Best Practices OLAP","ANALYSIS","[CHANNEL].[All Channels]","[MEASURE].[Gross Margin]"
,"[POS].[all pos]","[Product].[all tires]","[region].[all regions].[europe]",
"[Period].[all years].[2002]::[period].[all years].[2003]","[valtype].[variance]")

The function returns the sum of the values of the individual elements.