Methods of distributing values to consolidated cell values
Splasher commands, which write back values to consolidated cells, are prefixed with the hash (#) symbol. In use, the name of each splasher command is abbreviated to a single letter, such as C (Copy) or L (Like).
If a text value that is written to a cell begins with one of those letters, or with the #
symbol, or contains the full name of a splasher command, the value may be parsed,
incorrectly, as a splasher command. When this occurs, the cell displays
#REF!.
If the value to be written to a cell is always text, and never a date or number, you can avoid this issue by prefixing the value with two apostrophes. The first apostrophe ensures that the string is not interpreted as a number or as a date or time. The second apostrophe ensures that the string is not interpreted as a splasher command.
The issue occurs only with cell values, not with cell notes or attributes, for example. It occurs only if splashing is enabled.
Example
C2019-20 to a cell.
=CELLWRITE("C2019-20","DEPM","BPCCONFIG_CYCLE","...However, the C is interpreted as a COPY command and a
#REF! error is returned.
The addition of two apostrophes after the opening quotation marks instructs the parser that the value is not a splasher command and is not a number or date:
=CELLWRITE("''C2019-20","DEPM","BPCCONFIG_CYCLE","...Allocate and Alter
For each write-back method you can specify allocation or alteration of values.
Allocate replaces existing values. For example, if a cell contains 1 and you write 2 to it using Allocate, the cell value becomes 2.
Alter amends existing values by the value you enter. For example, if a cell contains 1 and you enter 2 using Alter, the cell value becomes 3.
Alter commands are prefixed with ampersand (&).
Write-back methods and commands
By default, write-back methods are additive - they increase existing values in a variety of ways. You can change syntax of each write-back method to subtract values and to specify values by a percentage.
In this section, the examples of syntax of Weighted, Equal, Base and Like allocation, [value] can be prefixed with the minus sign (-) to write negative values. They can also be followed by the percentage sign (%) to amend values by a percentage.
| Syntax | Explanation |
|---|---|
| &W20 | Adds 20 to the cell value |
| &W-20 | Subtracts 20 from the cell value |
| &W5% | Increases the cell value by 5% |
The plus (+) sign is implicit but you can make it explicit (for example,
&W+20). Each write-back method supports two or three syntaxes. For example,
#W[value]and #@[value] are equivalent. Examples which contain specific dimension
and element names are examples.
Weighted
The proportions of child values to each other are maintained when the value of their parent is increased or decreased.
Weighted allocation: #W[value]
If you use weighted allocation to write a value to a parent cell that is, for example, double the existing value, the value of each child also doubles.
Weighted alteration: &W[value]
If you use weighted alteration to write a value to a parent cell that is, for example, double the existing value, the value of each child is doubled and added to the existing value.
Example
The value of the parent cell is 9 and the value of each of its three children is 3. You write 18 to the parent cell with weighted alteration. The value of each child cell is doubled and added to the existing value. So, the value of each child cell becomes 9. The value of the parent cell becomes 27.
Equal
Values which you specify in parent cells are distributed equally to their children.
Equal allocation: #E[value]
A value which you enter in a parent cell replaces the existing value and is distributed equally amongst the children.
Example
A parent cell has three children. You write 12 to the parent cell with equal allocation. The value of each child cell becomes 4.
Equal alteration: &E[value]
A value which you enter in a parent cell is distributed equally amongst the child cells and added to the existing value.
Example
The value of the parent cell is 12. There are three child cells, each with a value of 4. You write 12 to the parent cell with equal alteration. 12 is distributed equally to each child and added to the existing value. The value of each child cell becomes 8. The value of the parent cell becomes 24.
Base
A value that you write to a parent cell is written to each child cell. The value of the parent cell is updated.
Base allocation: #B[value]
A value which you write to a parent cell is written to each child, replacing the existing value.
Example
A parent cell has three children. You write 2 to the parent cell with base allocation. The value of each child cell becomes 2. The value of the parent cell becomes 6.
Base alteration: &B[value]
A value that you write to a parent cell is written to each child and added to the existing value.
Example
A parent cell has three children. The value of each child cell is 2. Therefore, the value of the parent cell is 6. You write 2 to the parent cell with base alteration. 2 is written to each child cell and added to the existing value. The value of each child becomes 4. The value of the parent cell becomes 12.
COPY
You can copy values from one area of a cube to another: for example, the actual figures for one year to the budget for another year. In this example, the actual figures are the source and the budget is the target.
You write the Copy command in the target cell. In the command you specify only the dimensions and elements that distinguish the source area of the cube from the target area. If the elements belong unambiguously to a specific dimension you need only specify the elements.
Copy - allocation: #C[Tuple]
Copy - alter: &C[Tuple]
A tuple is the address of a specific area of the database. In this case, the tuple is the address of the data which you want to copy. It is recommended that you use unique element names to specify the tuple.
COPY with alter uses External Weighted Delta as the write-back method.
Examples
You want to copy the fourth quarter actuals for 2006 to the budget for the same quarter. Select the cell containing the budget figures for the quarter and specify:
#COPY [VALTYPE].[Variance].[Actual];[PERIOD].[All Years].[2006].[2006_Q4]
You want to copy the German third quarter actuals for 2006 to the French fourth quarter budget for 2006.
Select the cell containing the French fourth quarter budget and specify:
#COPY [REGION].[All Regions].[Europe].[Germany];[VALTYPE].[Variance].[Actual];[PERIOD].[All Years].[2006].[2006_Q3]
COPYFULL
COPYFULL with alter, uses External Weighted Delta.
Data elements
Microsoft Analysis Services supports data elements. A data element is a copy of a parent element but without the status of a parent. You can write-back to a data member without affecting the child elements.
Example
You have a hierarchy of staff in which the manager is the parent element. If you are planning the salary of the manager, you can write-back a value to the Manager data element instead of to the Manager element. In this way, the salaries of the other staff do not affect, and are not affected by, that of the manager.
LIKE
You can splash values to the children of a consolidated cell in the same proportions as the values of the children of another consolidated cell.
You write the Like command in the target cell. In the command you specify only the dimensions and elements that distinguish the source area of the database from the target area. If the elements belong unambiguously to a specific dimension you do not need only specify the elements.
In write-back commands, L indicates Like.
LIKE - allocation: #L[value][Tuple]
LIKE - alter: &L[value][Tuple]
A tuple is the address of a specific area of the database. In this case, the tuple is the address of the source area of the database.
Example
The fourth quarter sales figures for Spain in 2005 are 60. The figures for the individual months are October 10, November 20, and December 30.
To see what effect sales of 600 would have in the first quarter of 2006 in Italy (if distributed in the same proportions as the fourth quarter 2005 sales in Spain), select the cell containing the first quarter sales for Italy and enter:
#L 600 [REGION].[All
Regions].[Europe].[Spain];[PERIOD].[All Years].[2005].[2005_Q4]
The first quarter sales for Italy become 600. The figures for the individual months become January 100, February 200, and March 300.
#delete
To delete all values from a slice of the database, specify #delete in a cell which contains a write-back formula (for example, WBC).
LIKEFULL
LIKEFULL combines LIKE and COPYFULL. That is, it enables you to write-back a specified value, but using External Weighted as the write-back mode. The syntax for allocation is:
#LIKEFULL [value] [tuple]
For alter, the syntax is:
&LIKEFULL [value] [tuple]. External Weighted
Delta is used as the write-back method.