MPCData formulas

The MPCData formula provides these information about the data slice represented in each cell:
  • Name of the dataset
  • Unique name of each dimensio
  • Member descriptions or the cell references of the dimension members
Member descriptions are used by default. For XMLA Relational data sources, your administrator can use member names instead of descriptions.

The dataset used in the example is Product Sales BW250.

The unique name for each dimension is enclosed by both brackets and quotes. The dimension is followed by the cell reference to the member (with no quotes) or the actual member description (enclosed in quotes).

A dimension member is represented by an absolute or relative cell reference, such as $B$1, when the dimension was inserted onto the worksheet as either an on-grid or off-grid dimension.

If an off-grid dimension was not inserted in the worksheet, then the formula shows the member description in quotes instead of a cell reference. For example, the Measures dimension was not inserted on the worksheet; therefore, the member description value is displayed in quotes within the MPCData formula. Here is a formula using Insert Block. 

=MPCData("Product Sales BW250","[Unit]",$B$1,"[Measures]","Value","[Version]","Actual","[Period]",E$2,"[Schedule]",$A5)

There are differences in the appearance of the MPCData formula, depending on whether you insert a block or a value for the same data slice. When you insert a value, the MPCData formula does not include cell references, and all members are enclosed in brackets as well as quotes. For example: 

=MPCData("Product Sales BW250","[Period]","[September 2009]","[Unit]","[New York]","[Schedule]","[Total Sales]","[Measures]","[Value]","[Version]","[Actual]")

In the event a dataset includes 15-29 dimensions and the data source is read-only, Insert Value inserts a CBData formula instead of an MPCData formula to show a value. These cells are read-only.