Consolidate Records With Totals

Consolidates records with totals. One dimension, total-dimension or calculation ('Sum', 'Min', 'Max'...) must be set.

Permissions

Command Kind	Needed User Kind
Read	All

Parameters

Name	Type	Description	Required	Default	Values
Table	string	Name of the table ([Table]).	yes
AddName	boolean	If 'True' the item name is displayed in the output, separated by an equal sign (name=format). The default value is 'True'. This is only used if a format has been set. If no format has been set the item name is displayed. This is used for inputs because only the item name is unique and can be used for inputs. E.g. the 'Set Records' or 'Update Records' command.	no	true
Average	array of string	Consolidates the average of a field ([Field].[Caption].[Division]). Caption is the alternative column header text. Division divides number fields. For example [Cost].[Cost Per Unit].[1000] displays the number values in thousand. [Default] and [None] are not changing the output.	no
CalcDouble	array of string	Calculated number field. It is not a table field ([Field].[Caption]). Caption is the alternative column header text. Division divides number fields. For example [Dev].[Deviation].[100].	no
CalcString	array of string	Calculated text field. It is not a table field ([Field].[Caption].[Division]). Caption is the alternative column header text. For example [Com].[Comment].	no
Count	array of string	Consolidates the count of records ([Field].[Caption].[Division]). Caption is the alternative column header text. Division divides number fields. For example [Cost].[Cost Per Unit].[1000] displays the number values in thousand. [Default] and [None] are not changing the output.	no
CountItems	array of string	Consolidates the counts of dimension items ([Field].[Caption].[Division]). Caption is the alternative column header text. Division divides number fields. For example [Cost].[Cost Per Unit].[1000] displays the number values in thousand. [Default] and [None] are not changing the output.	no
Dimension	array of string	Name of the dimension ([Dimension].[Caption].[Format]). Caption is the alternative column header text. The format can be composed of the name and the captions. For example [Customer].[Customer ID and Company Name].[Name - Caption1].	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
FilterField	array of string	Filters by field ([Field].[Operator].[Value]). The operators are (long and short): Contains (CO), Ends (EN), Equal (EQ), Greater (GR), GreaterEqual (GE), Less (LS), LessEqual (LE), NotContains (NC), Starts (ST) and Unequal/NotEqual (UE/NE)	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
FilterTreeFactor	array of string	Filters by tree sum ([Dimension].[Tree].[Sum]). The factor of the items and sums are used to calculate the values.	no
GrandtotalPosition	string	Position of the grandtotal: Top, Bottom or None. The default Value is 'Top'. This parameter is independent from 'TotalPosition'.	no	Top	Top, Bottom, None
GroupAverage	array of string	Same as 'Average' but with date offset, date calc and filters ([Field].[Caption].[Division],[DateOffset].[DateCalc],[Dimension].[Item],[Dimension].[Tree].[Sum]). The date offset is an offset for date filters. For example a value of -12 changes the month from [2022-12] to [2021-12]. Date calc aggregates date values: [None]=no aggregation, [Year]=jan. till dec., [YearToDate]=jan. till now, [EndOfYear]=dec., [Rolling]=12 months back till now, [OffsetYear]=whole offset year of the database, [OffsetYearToDate]=offset month of the database till now., [EndOfOffsetYear]=last offset month of the database. Filters can be used with dimension items ([Dimension].[Item]) and tree sums ([Dimension].[Tree].[Sum]).	no
GroupCount	array of string	Same as 'Count' but with date offset, date calc and filters ([Field].[Caption].[Division],[DateOffset].[DateCalc],[Dimension].[Item],[Dimension].[Tree].[Sum]). The date offset is an offset for date filters. For example a value of -12 changes the month from [2022-12] to [2021-12]. Date calc aggregates date values: [None]=no aggregation, [Year]=jan. till dec., [YearToDate]=jan. till now, [EndOfYear]=dec., [Rolling]=12 months back till now, [OffsetYear]=whole offset year of the database, [OffsetYearToDate]=offset month of the database till now., [EndOfOffsetYear]=last offset month of the database. Filters can be used with dimension items ([Dimension].[Item]) and tree sums ([Dimension].[Tree].[Sum]).	no
GroupCountItems	array of string	Same as 'CountItems' but with date offset, date calc and filters ([Field].[Caption].[Division],[DateOffset].[DateCalc],[Dimension].[Item],[Dimension].[Tree].[Sum]). The date offset is an offset for date filters. For example a value of -12 changes the month from [2022-12] to [2021-12]. Date calc aggregates date values: [None]=no aggregation, [Year]=jan. till dec., [YearToDate]=jan. till now, [EndOfYear]=dec., [Rolling]=12 months back till now, [OffsetYear]=whole offset year of the database, [OffsetYearToDate]=offset month of the database till now., [EndOfOffsetYear]=last offset month of the database. Filters can be used with dimension items ([Dimension].[Item]) and tree sums ([Dimension].[Tree].[Sum]).	no
GroupMax	array of string	Same as 'Max' but with date offset, date calc and filters ([Field].[Caption].[Division],[DateOffset].[DateCalc],[Dimension].[Item],[Dimension].[Tree].[Sum]). The date offset is an offset for date filters. For example a value of -12 changes the month from [2022-12] to [2021-12]. Date calc aggregates date values: [None]=no aggregation, [Year]=jan. till dec., [YearToDate]=jan. till now, [EndOfYear]=dec., [Rolling]=12 months back till now, [OffsetYear]=whole offset year of the database, [OffsetYearToDate]=offset month of the database till now., [EndOfOffsetYear]=last offset month of the database. Filters can be used with dimension items ([Dimension].[Item]) and tree sums ([Dimension].[Tree].[Sum]).	no
GroupMin	array of string	Same as 'Min' but with date offset, date calc and filters ([Field].[Caption].[Division],[DateOffset].[DateCalc],[Dimension].[Item],[Dimension].[Tree].[Sum]). The date offset is an offset for date filters. For example a value of -12 changes the month from [2022-12] to [2021-12]. Date calc aggregates date values: [None]=no aggregation, [Year]=jan. till dec., [YearToDate]=jan. till now, [EndOfYear]=dec., [Rolling]=12 months back till now, [OffsetYear]=whole offset year of the database, [OffsetYearToDate]=offset month of the database till now., [EndOfOffsetYear]=last offset month of the database. Filters can be used with dimension items ([Dimension].[Item]) and tree sums ([Dimension].[Tree].[Sum]).	no
GroupsToFilters	boolean	Adds the filters of the group parameters (GroupAverage, GroupCount, GroupCountItems, GroupMax, GroupMin and GroupSum) to the filter of the table (Filter, FilterTree...). The default value is 'False'.	no	false
GroupSum	array of string	Same as 'Sum' but with date offset, date calc and filters ([Field].[Caption].[Division],[DateOffset].[DateCalc],[Dimension].[Item],[Dimension].[Tree].[Sum]). The date offset is an offset for date filters. For example a value of -12 changes the month from [2022-12] to [2021-12]. Date calc aggregates date values: [None]=no aggregation, [Year]=jan. till dec., [YearToDate]=jan. till now, [EndOfYear]=dec., [Rolling]=12 months back till now, [OffsetYear]=whole offset year of the database, [OffsetYearToDate]=offset month of the database till now., [EndOfOffsetYear]=last offset month of the database. Filters can be used with dimension items ([Dimension].[Item]) and tree sums ([Dimension].[Tree].[Sum]).	no
Max	array of string	Consolidates the max of a field ([Field].[Caption].[Division]). Caption is the alternative column header text. Division divides number fields. For example [Cost].[Cost Per Unit].[1000] displays the number values in thousand. [Default] and [None] are not changing the output.	no
MaxRecords	integer	Maximum number of records to be returned. The default value is 500000.	no	500000
Min	array of string	Consolidates the min of a field ([Field].[Caption].[Division]). Caption is the alternative column header text. Division divides number fields. For example [Cost].[Cost Per Unit].[1000] displays the number values in thousand. [Default] and [None] are not changing the output.	no
NameWhenEmpty	boolean	Different formats can be used in the dimension parameter (e.g. [Caption1]). Maybe the format returns an empty value. If 'NameWhenEmpty' is 'True' the item name is returned instead of an empty value. The default value is 'True'.	no	true
NullFactor	boolean	Shows items with factor '0'. The default value is 'True'.	no	true
Nulls	boolean	If 'True' empty records are shown that is number fields that are '0' and text fields that are ''. The default value is 'True'.	no	true
Parallel	boolean	If 'True' multiple threads are used. The default value is 'True'.	no	true
Sort	array of string	Sorts the records by a field ([Field].[Ascending]): Ascending (Asc) or Descending (Desc).	no
Subtable	string	Name of the subtable ([Subtable]). A subtable contains additional filters used by the table.	no
Sum	array of string	Consolidates the sum of a field ([Field].[Caption].[Division]). Caption is the alternative column header text. Division divides number fields. For example [Cost].[Cost Per Unit].[1000] displays the number values in thousand. [Default] and [None] are not changing the output.	no
Tablecalculation	string	Name of the tablecalculation ([Tablecalculation]).	no
TotalDimension	array of string	Name of the dimension for the total ([Dimension]).	no
TotalOnly	boolean	If 'True' only totals are shown. The default value is 'False'.	no	false
TotalPosition	string	Position of the totals: Top or Bottom. The default value is 'Top'.	no	Top	Top, Bottom
TotalTextFormat	string	Text format for the field 'TotalText': All, AllWithDimension, Last or LastWithDimension. The default value is 'All'. 'All' displays all items of the total separated by a comma. 'AllWithDimension' displays the all items and dimension names of the total separated by a comma. 'Last' displays the last item of the total. 'LastWithDimension' displays the last item and dimension name of the total.	no	All	All, AllWithDimension, Last, LastWithDimension
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

Name	Type	Description

Code

CONSOLIDATE RECORDS WITH TOTALS
TABLE=[string, Needed]
ADDNAME=[boolean, default=true]
AVERAGE=[string, Multiple]
CALCDOUBLE=[string, Multiple]
CALCSTRING=[string, Multiple]
COUNT=[string, Multiple]
COUNTITEMS=[string, Multiple]
DIMENSION=[string, Multiple]
FILTER=[string, Multiple]
FILTERATTRIBUTE=[string, Multiple]
FILTERFIELD=[string, Multiple]
FILTERRANGE=[string, Multiple]
FILTERTREE=[string, Multiple]
FILTERTREEFACTOR=[string, Multiple]
GRANDTOTALPOSITION=[string, default=Top, values=[Top,Bottom,None]]
GROUPAVERAGE=[string, Multiple]
GROUPCOUNT=[string, Multiple]
GROUPCOUNTITEMS=[string, Multiple]
GROUPMAX=[string, Multiple]
GROUPMIN=[string, Multiple]
GROUPSTOFILTERS=[boolean, default=false]
GROUPSUM=[string, Multiple]
MAX=[string, Multiple]
MAXRECORDS=[integer, default=500000]
MIN=[string, Multiple]
NAMEWHENEMPTY=[boolean, default=true]
NULLFACTOR=[boolean, default=true]
NULLS=[boolean, default=true]
PARALLEL=[boolean, default=true]
SORT=[string, Multiple]
SUBTABLE=[string]
SUM=[string, Multiple]
TABLECALCULATION=[string]
TOTALDIMENSION=[string, Multiple]
TOTALONLY=[boolean, default=false]
TOTALPOSITION=[string, default=Top, values=[Top,Bottom]]
TOTALTEXTFORMAT=[string, default=All, values=[All,AllWithDimension,Last,LastWithDimension]]
VISUALSCRIPTQUERY=[VisualScriptGroupParam, Multiple]