Appearance
Trc_Ws_SetConsValue
Gets and sets a value of a workspace consolidation. The workspace consolidation is used to write an input to the database.
Function Parameters
Parameter 1
Workspace. E.g. Sales.
Parameter 2
Consolidation of the selected workspace. E.g. SalesMonth.
Parameter 3
Value field. E.g. Amount.
Parameter 4
Aggregation of the records. E.g. SUM.
| Aggregation | Description |
|---|---|
| Average | Builds the avererage of the records. |
| Count | Counts the records. |
| Max | Builds the max of the records. |
| Min | Builds the min of the records. |
| Sum | Builds the sum of the records. |
Parameter 5
Determines how the input is processed.
| Input | Description |
|---|---|
| Record | Sets or edits a record. An item for each dimension in the table must be set. |
| Distribution | The input value is evenly distributed across the data. |
| DistributionCreateItems | Same as Distribution, but non-existent records are created. For security reasons it is necessary to specify an item or tree item for each dimension in the table. |
| EqualValue | The input value is written to each record. |
| EqualValueCreateItems | Same as EqualValue, but non-existent records are created. For security reasons it is necessary to specify an item or tree item for each dimension in the table. |
| Factor | Changes the data records based on a factor. E.g. an input field of 2 doubles the values. |
| Percent | Changes the data records based on a percent value. E.g. an input field of 200 doubles the values. |
| TargetValue | Weighted distribution. Distributes the input value based on the current record values. |
Parameter 6..n
Filter value pairs. The first value contains the dimension, the second value contains an item of the dimension. E.g. Region and North.
Samples
Builds the amount sum of the region North. The input value is weighted distributed to the record values.
=TRC_WS_SETCONSVALUE("Sales";"SalesMonth";"Amount";"SUM";"TARGETVALUE";"Region";"North")
Builds the turnover sum of the regions North and South. The input value must be a üervent value. E.g. an input value of 200 doubles the record values.
=TRC_WS_SETCONSVALUE("Sales";"SalesMonth";"TurnoverTotal";"SUM";"PERCENT";"Region";"North";"Region";"South")
References are used in the filters of cell C5.
Assistant Window
The assistant window can be used to generate a funtion.
1. Consolidate
In the left area, the user must select a workspace. In the right area the consolidations of the selected workspace are displayed.
2. Value
A value field must be selected.
3. Filter Items
It is possible to retrieve only parts of the data from the workspace. Therefore filter items are used. This page shows the dimensions of the selected consolidation.
Selected
If a dimension is enabled, it is used to filter the data of the workspace.
Item
Item of the dimension, to filter the workspace data.
4. Aggregation
Not only sums can be loaded, but also min, max, average or count.
5. Input
Determines how the inputs are processed.
6. Finish
After finish the function is placed in the selected cell of the worksheet.
=TRC_WS_SETCONSVALUE("Sales";"SalesMonth";"Amount";"SUM";"TARGETVALUE";"Region";"North")