Splashing allows you to enter a value into a consolidated cell. What happens to the data in the base cells depends on which splashing command or parameter is used. In this article, we will explain the # parameter.
To split a value among the subsequent base elements, start the entry with #, and then enter the number.
We enter #250 for "Desktop L"/"Feb" and #120 for "Desktop Pro"/"Feb" in the new created cube Marketing (all cube cells are 0 without Germany/Destop L/2015=175):
The entered values are split (broken down) and distributed across all levels below the current one. If no values existed previously, the new value is split equally among all subordinate base elements below the current level. You can check that by scrolling through the regions.
The base values for "Germany":
The values of D7 and D8 can be found for all other countries as well.
Now enter the following data: 50 for "France" and "Desktop L" in January. All other countries have value "0" in January.
Now when you view the results for "Europe", the view appears as follows:
If a value exists in at least one of the base elements, the total sum is split in line with the shares of the existing values. In this case the value 225 in "Europe" is the result of 175 in "Germany" and 50 in "France". All other countries have the value 0.
When you enter a new value, this value is split among the base elements below that in precisely the weighting of the previous values. If a value exists in one of the related base elements, then related base elements with the value 0 are not changed.
For example, if you change the total amount for Europe from 225 to 100 (44.4%) at "Desktop L", the value for Germany changes to 44.4% of 175, this is 77.78 (don’t forget to enter the value as #100).
Detailed view for "Germany":
Respectively, it would be 22.22 instead of 50 for France. If no values existed previously, the new value is split equally among all subordinate base elements.
Notes:
- If the view contains an element whose consolidation factors have added the result 0 (e.g., variance with Actual 1 and Budget -1), and the base cells are still empty, then these cells cannot be splashed with #, because Jedox cannot compute a clear distribution rule. Attempting to do this will result in an error message.
-
In a hierarchy that has an element with consolidation factor 0, such as this:
Qtr1
- Jan (factor 1)
- Feb (factor 1)
- Mar (factor 0)
splashing on the top element modifies the value on the element that is aggregated with factor 0. It increases the existing value by the same factor by which the aggregated value is changed. -
In Jedox Spreadsheets, for the splashing command # there is a "safety net" to prevent users from over-populating a cube accidentally. When a # splash command affects more than a defined number of base cells, the user will receive a corresponding warning.
This number has the default value 1,000,000 and can be set with the configuration option "splash-threshold" for the In-Memory DB (OLAP). For the following warning the "splash threshold" was set to 1.000 :
The button Cancel will return to the spreadsheet cell and nothing happens.
The button OK starts the Planning Assistant and suggests the "Like" splashing.
In the Planning Assistant all other splashing commands can also be specified, including the original # splash command. Here, however, the number of affected cells is displayed immediately.
Variations of splash parameter #
Splash parameter ##
Use this parameter to change the previous absolute value:
- ##1000 or ##+1000: adds 1000 to the previous value
- ##-1000: subtracts 1000 from the previous value
Splash parameter # … %
Use this parameter to set a percentage of the current value.
- # 20% replaces the previous value with 20% of itself (e.g. 100 becomes 20)
- ## 20% adds 20 % to the previous value (e.g. 100 becomes 120)
Note: Making an In-Memory DB input without splashing (on a base cell) results in the percentage’s being treated as it normally would be. An input of "10%" on a base cell would store the actual value 0.1 in the cube.
Splash parameter #00
This parameter can splash zero values explicitly. It can be used on cubes where the zero value storage is enabled.
Splash parameter !#
Use this parameter to overwrites the specified value on populated base elements.
splash parameter !!#
Use this parameter to add the specified value only to the populated base elements.
Note that negative values are subtracted from the populated base elements.
Updated November 4, 2024