Spread Records

Changes or creates records.

Permissions

Command Kind	Needed User Kind
Write	All

Parameters

Name	Type	Description	Required	Default	Values
Table	string	Name of the table ([Table]).	yes
Add	boolean	If 'True', the new value is added to an existing one. If 'False', the new value overwrites an existing one. The default value is 'False'.	no	false
Create	boolean	If 'True' new records are created. The default value is 'False'.	no	false
DateOffset	integer	Moves the date dimension items by the offset. The default value is '0'. For example the month '2022-02' with an offset of 5 becomes '2022-07'.	no	0
Distribution	array of string	Name of a distribution ([Distribution]). It is used as a filter.	no
Factor	array of string	A number value is changed via a factor ([NumberField].[Factor]). For example a factor of 1.25 adds 25% to the value.	no
Filter	array of string	Filters by dimension item ([Dimension].[Item]) or tree sum ([Dimension].[Tree].[Sum]). The tree sum format is equal to 'FilterTree'.	no
FilterAttribute	array of string	Filters by attribute ([Dimension].[Attribute].[Value]).	no
FilterRange	array of string	Filters by an item range ([Dimension].[StartItem].[EndItem]).	no
FilterTree	array of string	Filters by tree sum ([Dimension].[Tree].[Sum]).	no
NeedAllDimensions	boolean	If 'True' all dimensions must be filtered. If 'False' and a dimension is not filtered and 'Create' is 'True', all items of the dimension are used to create new records. The default value is 'True'.	no	true
Percent	array of string	A number value is changed via a percent ([NumberField].[Factor]). For example a percent of 125 adds 25% to the value.	no
Subtable	string	Name of the subtable ([Subtable]). A subtable contains additional filters used by the table.	no
Tablecalculation	string	Name of the tablecalculation ([Tablecalculation]).	no
Target	array of string	A number value is changed via a target value ([NumberField].[Target]). For example a target value of 1000 adjusts the values to their proportion to 1000.	no
Value	array of string	Sets the text value of the variable.	no
VisualScriptQuery	array of VisualScriptGroupParam	Defines the visual script	no

VisualScriptGroupParam

Visual script parameter that defines the calculation of a group (name of the calculated field).

Property	Type	Description	Required	Default	Values
Group	string	Starts a new group (name of the calculated field) in the visual script.	no
Infos	string	User defined informational text about the group.	no
Text	string	Visual items in text format (expression tree). E.g. Multiplication(Field("Amount"),Field("Price")).	no
VisualScriptItems	array of VisualScriptItemParam	Visual items in json format (expression tree).	no

VisualScriptItemParam

An item returns a fixed value or a calculated value. A calculated value can use several inputs.

Property	Type	Description	Required	Default	Values
Id	string	Unique ID of the item. It is used by other items to reference the item as input.	yes
Item	string	Starts a new item (name of the function) in the group.	yes		Abs, Addition, And, Between, Case, Ceil, CompareText, Concat, ConcatWithSpace, Contains, Copy, CumulatedIndex, CumulatedSum, DatabaseVariable, Date, DaysBetween, Delete, Division, Equal, Field, FieldMax, FieldMin, FieldSum, FillSum, Filter, Floor, FormatFloat, Frac, Greater, GreaterEqual, Guid, IfThenElse, In, IncDay, IncMonth, IncWeek, IncYear, InDateFilter, Int, Invert, IsKpi, IsSum, Length, Level, Lookup, Lower, LowerCase, LowerEqual, Max, Min, Mod, Multiplication, New, NewLine, Not, NullDiv, Number, NumberChars, Or, Percent, PercentDeviation, Pos, Records, RegEx, Replace, Round, RoundTo, Subtraction, Sum, Text, Trunc, Unequal, UpperCase, Value, VarToDate, VarToFloat, VarToStr, Week
Input	array of string	Values from other items (referenced via 'ItemId') needed for the calculations.	yes
Color	string	Color of the item in the editor.	no
X	integer	Horizontal position (pixels) of the item in the editor. The default value is '0'.	no
Y	integer	Vertical position (pixels) of the item in the editor. The default value is '0'.	no

Items

Within each item property the following functions, which are clustered into several groups, can be used.

Group - Conversion

Function	Description	Inputs	Targets
FormatFloat	Formats a number.	- Number to be formated. - Format: '0' is the digit placeholder, '#' is the optional digit placeholder, '.' is the decimal separator and ',' is the thousand separator. E.g. '#,###,##0.00'	All
NumberChars	Deletes all characters that are not a number ('0' to '9').	- Text to be converted.	All
RegEx	Converts a text to perl regex.	- Text. - RegEx in Perl format. - Options: 'Anchored', 'CaseLess', 'Extended', 'MultiLine', 'NoAutoCapture', 'SingleLine' or 'UnGreedy'. The default options are: MultiLine, SingleLine and Extended.	All
VarToDate	Converts a text to date.	- Text to be converted. - Text format. The default format is 'YYYY-MM-DD'. - Default text that is used when 'Input1' is empty.	All
VarToFloat	Converts a text to number.	- Text to be converted. The decimal separator is '.'.	All
VarToStr	Converts a value to text.	- Value to be converted.	All

Group - Database

Function	Description	Inputs	Targets
DatabaseVariable	Loads a database variable.	- Name of the database variable. The database variables can be viewed with the 'SHOW VARIABLES' command.	All

Group - Date and Time

Function	Description	Inputs	Targets
Date	Returns the actual date in the 'YYYY-MM-DD' format.		All
DaysBetween	Returns the number days between the two specified dates. The date must be specified as 'YYYY-MM-DD' or 'YYYYMMDD'.	- First date. - Second date.	All
IncDay	Adds days to a date. The date must be specified as 'YYYY-MM-DD' or 'YYYYMMDD'.	- Date - Number of days to be added.	All
IncMonth	Adds months to a date. The date must be specified as 'YYYY-MM-DD' or 'YYYYMMDD'.	- Date - Number of months to be added.	All
IncWeek	Adds weeks to a date. The date must be specified as 'YYYY-MM-DD' or 'YYYYMMDD'.	- Date - Number of months to be added.	All
IncYear	Adds years to a date. The date must be specified as 'YYYY-MM-DD' or 'YYYYMMDD'.	- Date - Number of years to be added.	All
Week	Returns the week of a date. The date must be specified as 'YYYY-MM-DD' or 'YYYYMMDD'.	- Date from which the week is to be determined.	All

Group - Logic

Function	Description	Inputs	Targets
And	Returns 'True' if one of the two inputs is 'True'. Otherwise 'False' is returned.	- First boolean to be checked. - Second boolean to be checked.	All
Equal	Checks if two values are equal.	- First value to be checked. - Second value to be checked.	All
Greater	Checks if one number is greater than the other.	- Number. - Number to be checked.	All
GreaterEqual	Checks if one number is greater or equal than the other.	- Number. - Number to be checked.	All
IfThenElse	Checks a condition. if the condition is 'True' the second input is returned. If 'False' the third input is returned.	- Condition. Must return 'True' or 'False'. - Result when condition is 'True'. - Result when condition is 'False'.	All
Lower	Checks if one number is lower than the other.	- Number. - Number to be checked.	All
LowerEqual	Checks if one number is lower or equal than the other.	- Number. - Number to be checked.	All
Not	Negates an expression.	- Boolean to be inverted.	All
Or	Returns 'True' if both of the tow inputs are 'True'. Otherwise 'False' is returned.	- First boolean to be checked. - Second boolean to be checked.	All
Unequal	Checks if two values are unequal.	- First value to be checked. - Second value to be checked.	All

Group - Math

Function	Description	Inputs	Targets
Abs	Returns the absolute value. The decimal places are deleted.	- Number to be converted.	All
Addition	Adds two numbers.	- First number to be added. - Second number to be added.	All
Between	Checks if a value is within a range.	- Number to be checked. - Lower bound. - Upper bound.	All
Ceil	Rounds a number up toward positive infinity.	- Number to be converted.	All
Division	Divides two numbers.	- Number. - Number to divide by.	All
Floor	Rounds variables toward negative infinity.	- Number to be converted.	All
Frac	Returns the fractional part of a real number.	- Number to be converted.	All
Int	Returns the integer part of a number.	- Number to be converted.	All
Invert	Multiplies a number by '-1'.	- Number to be converted.	All
Max	Maximum of two numbers.	- First number to be checked. - Second number to be checked.	All
Min	Minimum of two numbers.	- First number to be checked. - Second number to be checked.	All
Mod	Returns the remainder of a division, after the first number is divided by the second number.	- Number. - Number to divide by.	All
Multiplication	Multiplies two numbers.	- Number. - Number to multiply by.	All
NullDiv	Divides two numbers. If a division by null occurs, the third input is used.	- Number. - Number to divide by. - Result when 'Input 2' is null.	All
Number	Fixed number.		All
Percent	Calculates the percentage of the first number in relation to the second number. If the second number is '0', the third input is returned.	- Number to calculate the percentage of. - Reference number (the total or base). - Result when 'Input 2' is null.	All
PercentDeviation	Calculates the percentage deviation of the first number in relation to the second number. If the second number is '0', the third input is returned.	- Number for which the percentage variance should be calculated. - Reference number (the total or base). - Result when 'Input 2' is null.	All
Round	Rounds a number to an integer value.	- Number to be rounded.	All
RoundTo	Rounds a number to the specified digit.	- Number to be rounded. - Number of digits.	All
Subtraction	Subtracts two numbers.	- Number. - Number to be subtracted.	All
Sum	Adds up to three numbers.	- First number to be added. - Second number to be added. - Third number to be added.	All
Trunc	Truncates a number to an integer.	- Number to be converted.	All

Group - Query

Function	Description	Inputs	Targets
CumulatedIndex	Index of the record, starting with one.		Select Tablecalculation
CumulatedSum	The values from the first to the current record are added.		Select Tablecalculation
Field	Get the value of a field from the query.		All
FieldMax	Maximum value of a field from the query.	- Name of the field.	Select Tablecalculation
FieldMin	Minimum value of a field from the query.	- Name of the field.	Select Tablecalculation
FieldSum	Summary value of a field from the query.	- Name of the field.	Select Tablecalculation
Filter	This command is used by the client. At client side input four contains the name of a filter. When the query is send to the server, the client must replace the filter name with the filtered items.	- Reserved, not used by now. - Reserved, not used by now. - Reserved, not used by now. - Filter items.	Select Tablecalculation
InDateFilter	Checks if the query contains a date filter.	- Name of the date filter to be checked.	Select Tablecalculation
Lookup	Loads a value from another table.	- Additional filter. - Reserved, not used by now. - Reserved, not used by now. - Name of the lookup table. - Name of the lookup field. - Calculation: 'Average', 'Count', 'Max', 'Min' or 'Sum'. - Date offset. E.g. the lookup uses the month dimension and date offset of '-12'. The lookup will calculate the values from last year. - Dimension in the query whose elements are to be used in the lookup. E.g. [Month],[Product] - Fixed items that should be used in the lookup. E.g. [Year].[2020],[Region].[North] - This input is used by the client. At client side it contains the names of filters. When the query is send to the server, the client must replace the names with the filter items. - Date calculation: 'None', 'Year', 'YearToDate', 'EndOfYear', 'Rolling', 'OffsetYear', 'OffsetYearToDate' or 'EndOfOffsetYear'. - An attribute can be used as a filter. A dimension in the query contains an attribute. The attribute name must be the same name as the filter dimension in the lookup table. The attribute value contains the item name to be filtered. E.g. [Customer].[Product],[Customer].[Region]. - An text field in the query can be used as a filter. The text field name must be the same name as the filter dimension in the lookup table. The value of the text field contains the item name to be filtered.	Select Step Tablecalculation
New	The command is used in tables. It indicates that the calculated record is a new one.		Table
Records	Returns the number of records of the query.		Select Tablecalculation
Value	Returns the original value of the calculated field.		All

Group - System

Function	Description	Inputs	Targets
Guid	Generates a unique ID. E.g. '{1988C7BF-55AD-4764-8CB4-47F0BF797094}'.		All

Group - Text

Function	Description	Inputs	Targets
Case	Searches in a list of text pairs for the first text. If this is found, the second text is returned.	- Text to be checked. - Result when not found. - Input 3 to N: Text pairs separated by '='.	All
CompareText	Compares two texts. If the first text is less than the second, a value less than zero is returned. If the first text is greater than the second, a value greater than zero is returned. If both texts are equal, null is returned.	- First text to be checked. - Second text to be checked.	All
Concat	Concatenates up to three texts.	- First text to be concatenated. - Second text to be concatenated. - Third text to be concatenated.	All
ConcatWithSpace	Concatenates up to three texts. The texts are separated by spaces.	- First text to be concatenated. - Second text to be concatenated. - Third text to be concatenated.	All
Contains	Checks the second text is included in the first text.	- Text to be found. - Text to search in.	All
Copy	Returns a subtext of a text.	- Text from which a part is to be copied. - Start position. - Number of chars.	All
Delete	Removes a subtext from a text.	- Text from which a part is to be deleted. - Start position. - Number of chars.	All
In	Checks a text is included in a list.	- Text to be checked. - Input 2 to N: Text list.	All
Length	Returns the number of chars of a text.	- Text from which the length is to be calculated.	All
LowerCase	Converts the chars of a text to lower case.	- Text to be converted.	All
NewLine	Returns a line feed char (#10).		All
Pos	Locates a subtext in a text. If the subtext is not found, '0' is returned.	- Subtext. - Text.	All
Replace	Replaces all occurrences of a subtext in a text.	- Text. - Old text. - New text.	All
Text	Fixed text.		All
UpperCase	Converts the chars of a text to upper case.	- Text to be converted.	All

Group - Tree

Function	Description	Inputs	Targets
FillSum	The command is used in totals and trees. Normally a visual script calculates items, totals and sums. 'FillSum' indicates that totals and sums should not be calculated by the visual script. Instead, sums are cumulated.	- In this input the result of the calculation should be placed.	Select Tablecalculation
IsKpi	The command is used in trees. It indicates that the calculated row is a sum with kpi.		Select Tablecalculation
IsSum	The command is used in formulas, totals and trees. It indicates that the calculated row is a sum.		Select Tablecalculation
Level	The command is used in trees. It returns the level of the calculated row. Starting with one.		Select Tablecalculation

Targets

Name	Description
All	It is used in all targets (select, step, table and tablecalculation).
Select	It is used in queries ('SHOW RECORDS', 'CONSOLIDATE RECORDS', 'CONSOLIDATE TREE', 'UPDATE RECORDS'...).
Step	It is used in calculation steps ('SET STEP VISUAL').
Table	It is used in tables ('SET TABLE VISUAL').
Tablecalculation	It is used in tablecalculations ('SET TABLECALCULATION VISUAL').

Result

No fields

Code

SPREAD RECORDS
TABLE=[string, Needed]
ADD=[boolean, default=false]
CREATE=[boolean, default=false]
DATEOFFSET=[integer, default=0]
DISTRIBUTION=[string, Multiple]
FACTOR=[string, Multiple]
FILTER=[string, Multiple]
FILTERATTRIBUTE=[string, Multiple]
FILTERRANGE=[string, Multiple]
FILTERTREE=[string, Multiple]
NEEDALLDIMENSIONS=[boolean, default=true]
PERCENT=[string, Multiple]
SUBTABLE=[string]
TABLECALCULATION=[string]
TARGET=[string, Multiple]
VALUE=[string, Multiple]
VISUALSCRIPTQUERY=[VisualScriptGroupParam, Multiple]