Appearance
Trc_Db_SetTableValue
Gets a sets value of a database table.
Function Parameters
Parameter 1
Table of the connected database. E.g. Sales.
Parameter 2
Value field of the table. E.g. Amount.
Parameter 3
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 4
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 5..n
Filter value pairs. The first value contains the dimension, the second value contains an item of the dimension. E.g. Region and North.
It is also possible to use the filters of a workspace. In this case the first value contains the keyword <<Filter>>. The second value is a pair of to values, separated by an equal sign. The first part describes the workspace and the second part contains the name of the filter.
E.g. this sample uses the filter FilterYear of the Sales workspace.
"<<Filter>>";"Sales=FilterYear"
Samples
Builds the amount sum of the region North.
=TRC_DB_SETTABLEVALUE("Sales";"Amount";"SUM";"TARGETVALUE";"Region";"North")
Builds the amount sum of the region North and the year values set in the FilterYear filter of the workspace Sales.
=TRC_DB_SETTABLEVALUE("Sales";"Amount";"SUM";"TARGETVALUE";"Region";"North";"<<Filter>>";"Sales=FilterYear")
Builds the turnover sum of the regions North and South.
=TRC_DB_SETTABLEVALUE("Sales";"TurnoverTotal";"SUM";"TARGETVALUE";"Region";"North";"Region";"South")
Builds the turonover max of the region North with key accounter Bob.
=TRC_DB_SETTABLEVALUE("Sales";"TurnoverTotal";"MAX";"TARGETVALUE";"Region";"North";"KeyAccounter";"Bob")
References are used in the filters of cell C5.
Assistant Window
The assistant window can be used to generate a funtion.
1. Table
Selects a table of the connected database.
2. Value
A value field must be selected.
3. Filter Items
It is possible to retrieve only parts of the data from the table. Therefore filter items are used. This page shows the dimensions of the selected table.
Selected
If a dimension is enabled, it is used to filter the data of the table.
Item
Item of the dimension, to filter the table data.
4. Workspace Filter
It is also possible to use the filters of a workspace. In the left area, the user must select a workspace. In the right area the filters of the selected workspace are displayed.
5. Aggregation
Not only sums can be loaded, but also min, max, average or count.
6. Input
Not only sums can be loaded, but also min, max, average or count.
7. Finish
After finish the function is placed in the selected cell of the worksheet.
=TRC_DB_SETTABLEVALUE("Sales";"Amount";"SUM";"TARGETVALUE";"Region";"North";"<<Filter>>";"Sales=FilterYear")