CELLGET
This function returns values of a cube, based on the elements you specify. The function can be used only with ODBO and XMLA-compliant databases. 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 that you do not specify.
This dynamism is particularly relevant to report templates. The designer of a template can specify, for example, that users must select a dimension containing measures, or a dimension containing time periods. But the designer cannot know the actual dimensions that are available to the user, or which the user will select. So, any formulas that the template designer inserts must not reference specific dimensions, hierarchies or elements. Each argument of a CELLGET formula can be supplied by a variable.
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("data_connection",
"cube"{,"slice_element"})
Example
=CELLGET( "BestPracticesOLAP", "Analysis", "[Period].[All Years].[2017]",
"[Product].[Car Tires All Season]")
You can use CELLGET to reference elements from multiple dimensions individually or as a range. So, if cells B4, B5, B6, and B7 each contain an element name, these two examples produce the same result:
=CELLGET("BestPracticesOLAP", "Analysis",B4,B5,B6,B7)=CELLGET("BestPracticesOLAP","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 2016 and 2017 are stored as MLS-XML in a report variable:
=CELLGET("BestPracticesOLAP","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 2016 and 2017 elements are specified in a range.
=CELLGET("BestPracticesOLAP","ANALYSIS","[CHANNEL].[All Channels]","[MEASURE].[Gross Margin]"
,"[POS].[all pos]","[Product].[all tires]","[region].[all regions].[europe]",
"[Period].[all years].[2016]::[period].[all years].[2017]","[valtype].[variance]")