Functions of data composition system expression language

Eval
EvalExpression
EvalExpressionWithGroupArray
EvalExpressionWithGroupValueTable
Level
SerialNumber
GroupSerialNumber
Format
BeginOfPeriod
EndOfPeriod
DateAdd
DateDiff
Substring
StringLength
Year
Quarter
Month
DayOfYear
Day
Week
WeekDay
Hour
Minute
Second
Cast
IsNull
Shared Module Functions
Presentation
String
ValueIsFilled
ACos
ASin 
ATan
Cos
Exp
Log
Log10
Pow
Sin
Sqrt
Tan
Round
Int
Common modules functions
Presentation
String
ValueIsFilled
LevelInGroup

Evaluate

The Eval function is used to evaluate expressions within a certain grouping. The function is used for compatibility with the previous platform versions. We recommend that you use the EvalExpression function instead.

Syntax:

Eval(Expression, Grouping, CalculationType)

Parameters:

Total(Sales.SumTurnover) / Eval("Total(Sales.SumTurnover)", "Overall")

In this example, the result will be the ratio of the total by the Sales.SumTurnover field of the grouping record to the total of the same field in the entire composition.

• CalculationType. String type. If this parameter is Overall, the expression will be evaluated for all the records of the grouping. When the parameter value is Grouping, the values will be calculated for the current group record of the grouping.

EvalExpression 

The EvalExpression function is used to evaluate expressions within a certain grouping. The function takes into account the grouping filter but ignores hierarchical groupings.
The function cannot be applied to a grouping in a group filter of this grouping.

Syntax:

EvalExpression(Expression, Grouping, CalculationType, Begin, End, Sorting, HierarchicalSorting, ProcessingSimilarOrderValues)

Parameters:

Total(Sales.SumTurnover) / Evaluate("Total(Sales.SumTurnover)", "Overall")

In this example, the result will be the ratio of the total of the Sales.SumTurnover field of the grouping record to the total of the same field in the entire composition.

• CalculationType. String type. If this parameter is Overall, the expression will be evaluated for all the records of the grouping.
  When the parameter value is Grouping, the values will be calculated for the current group record of the grouping.
  If the parameter is GroupingNotResource, when the function is calculated for a group record by resources, the expression will be calculated for the first group record of the original grouping.
  When the EvalExpression function is calculated with the GroupingNotResource value for group records that are not groups by resources, the function is calculated in the same manner as it is calculated with Grouping for the parameter value.
  When the data composition template composer generates a data composition template and outputs the resource field to group by to the template, an expression is output to the template that is calculated using EvalExpression with the GroupingNotResource parameter. For the remaining resources, standard resource expressions are output to the group by resource.
  

  If the parameter is Hierarchy, the expression should be calculated for the parent hierarchical record, if any, or for the entire group if there is no parent hierarchical record. The template composer generates an expression for the "% in hierarchy group" field that contains a relation of the resource expression to the EvalExpression function for the resource expression calculated for the current grouping with the Hierarchy calculation type.

• Begin. Specifies which record should be used to begin the portion where aggregate expression functions should be calculated and what record should be used to retrieve field values outside of aggregate functions. A string that contains one of the following:
  • "First". The first record of the group should be retrieved. The word may be followed by an expression in brackets with the result used as a shift from the group beginning. The resulting value should be a positive integer.
    Example: First(3) means that the third value from the group beginning is retrieved.
    If the first record is outside of the group, it is considered that there are no records.
    For example, when there are 3 records and you need to retrieve First(4), it is assumed that there are no records.
  • "Last". The last record of the group should be retrieved. The word may be followed by an expression in brackets with the result used as a shift from the group end. The resulting value should be a positive integer.
    Example: Last(3) means that the third value from the group end is retrieved.
    If the last record is outside of the group, it is considered that there are no records.
    For example, when there are 3 records and you need to retrieve Last(4), it is assumed that there are no records.
  • "Previous". The previous record of the group should be retrieved. The word may be followed by an expression in brackets with the result used as a shift back from the current record of the group.
    Example: Previous(2) retrieves the record that is located prior to the previous one.
    If the previous record is outside of the group (e.g., for the second group record you need to get Previous(3)), the first group record is retrieved.
    When the previous record for a group total is retrieved, the first record is retrieved.
  • "Next". The next record of the group should be retrieved. The word may be followed by an expression in brackets with the result used as a shift forward from the current record of the group.
    Example: Next(2) retrieves the record that follows the next one.
    If the next record is outside of the group, it is considered that there are no records.
    For example, when there are 3 records and you need to retrieve Next for the third record, it is assumed that there are no records.
    When the next record for a group total is retrieved, it is considered that there are no records.
  • "Current". The current record should be retrieved.
    When you are retrieving for a group total, the first record is retrieved.
  • "BoundaryValue". A record should be retrieved based on a specified value. The BoundaryValue keyword may be followed by an expression in brackets with its value used to begin the portion of the first sorting field.
    The first record that has its sorting field equal to or greater than the specified value will be retrieved as a record.
    For example, if Period is used as a sorting field, its values are 01/01/2010, 02/01/2010, 03/01/2010, and you need to retrieve BoundaryValue(DateTime(2010, 1, 15)), the record with the date 02/01/2010 will be retrieved.
• End. Specifies the record that should be the last one in the portion to be used to calculate an aggregate expression. A string that contains one of the following:
  • "First". The first record of the group should be retrieved. The word may be followed by an expression in brackets with the result used as a shift from the group beginning. The resulting value should be a positive integer. Example: First(3) means that the third value from the group beginning is retrieved.
    If the first record is outside of the group, it is considered that there are no records. For example, when there are 3 records and you need to retrieve First(4), it is assumed that there are no records.
  • "Last". The last record of the group should be retrieved. The word may be followed by an expression in brackets with the result used as a shift from the group end. The resulting value should be a positive integer. Example: Last(3) means that the third value from the group end is retrieved.
    If the last record is outside of the group, it is considered that there are no records. For example, when there are 3 records and you need to retrieve Last(4), it is assumed that there are no records.
  • "Previous". The previous record of the group should be retrieved. The word may be followed by an expression in brackets with the result used as a shift back from the current record of the group. Example: Previous(2) retrieves the record that is located prior to the previous one.
    If the previous record is outside of the group (e.g., for the second group record you need to get Previous(3)), it is considered that there are no records.
    When the previous record for a group total is retrieved, the last record is retrieved.
  • "Next". The next record of the group should be retrieved. The word may be followed by an expression in brackets with the result used as a shift forward from the current record of the group. Example: Next(2) retrieves the record that follows the next one.
    If the next record is outside of the group, the last record is retrieved. For example, when there are 3 records and you need to retrieve Next for the third record, the third record is retrieved.
    When the next record for a group total is retrieved, it is considered that there are no records.
  • "Current". The current record should be retrieved.
    When you are retrieving for a group total, the first record is retrieved.
  • "BoundaryValue". A record should be retrieved based on a specified value. The BoundaryValue keyword may be followed by an expression in brackets with its value used to begin the portion of the first sorting field.
    The last record that has its sorting field equal to or less than the specified value will be retrieved as a record. For example, if Period is used as a sorting field, its values are 01/01/2010, 02/01/2010, 03/01/2010, and you need to retrieve BoundaryValue(DateTime(2010, 1, 15)), the record with the date 01/01/2010 will be retrieved.
• Sorting. A string listing comma-separated expressions that should be used as directions to sort the sequence. If missing, sorting is carried out in the same manner as used for a group the expression is calculated for. Every expression may be followed by Asc to sort in ascending order, Desc to sort in descending order, AutoOrder to sort reference fields by the fields that should be used to sort the referenced object by. AutoOrder may be used with both Asc and Desc keywords.
• HierarchicalSorting. Same as Sorting. Used to sort hierarchical records. If missing, the template composer generates a sorting based on the value in the Sorting parameter.
• ProcessingSimilarOrderValues. A string that contains one of the following:
  • "Together" - means that a sequence of sorted records is used to determine the previous and next records.
  • "Separately" - means that the previous and next records are defined based on the sorting expression values.

For example, if a retrieved sequence is sorted by date:

1. January 01, 2001 Johnson M. 10
2. January 02, 2001 Peterson S. 20
3. January 02, 2001 Smith R. 30
4. January 03, 2001 Peterson S. 40

Record 2 will serve as the previous one for record 3 when "Separately" is used to process similar values, or record 1 when "Together" is used. Record 2 will serve as a portion for the current record for record 2 when "Separately" is used, and records 2 and 3 when "Together" is used. Hence, the total for the current record will be 20 for "Separately", or 50 for "Together".

When "Together" is specified, the Begin and End parameters cannot contain shifts for the "First", "Last", "Previous", "Next" positions.

The default is "Separately".

Notes:

The function takes into account filters of groups but ignores hierarchical filters.

The function cannot be applied to a group in a group filter for this group. For example, in a filter of the Products group you should not use the expression EvalExpression("Total(SumTurnover)", , "Overall") > 1000. But this expression can be used in a hierarchical filter.

If the end record is located before the beginning one, it is considered that there are no records to calculate detailed data and aggregate functions.

When you calculate interval expressions for overalls (the Grouping parameter is "Overall"), it is considered that there are no records to calculate detailed data and aggregate functions.

When the template composer generates an expression for the EvalExpression and the sorting expression contains fields that can be used in a grouping, the EvalExpression function is replaced with NULL.

EvalExpressionWithGroupArray

Syntax:

EvalExpressionWithGroupArray (Expression, GroupFieldsExpressions, RecordsFilter, GroupFilter)

Parameters:

• Expression - the expression to be calculated. A sA string type. For example, Total(SumTurnover);
• GroupFieldsExpressions - comma-separated expressions of the group fields. Example: "Contractor, Batch".
• RecordsFilter - an expression applied to detailed records. Example: "DeletionMark = False". If this parameter includes an aggregate function, an error is generated during data composition.
• GroupFilter - a filter applied to group records. Example: "Total(SumTurnover) > &Parameter1".

The function retrieves an array with every item containing the result of calculating the expression for the group by the specified field.

Example:

Maximum(EvalExpressionWithGroupArray ("Total(SumTurnover)", "Contractor")).

When the template composer generates a template, it converts function parameters into data composition template field terms. For example, the Contractor field will be converted into DataSet.Contractor.

When the template composer generates expressions to output a custom field with an expression containing only EvalExpressionWithGroupArray, it will generate the required expression so that the data display presentations are sorted.

For example, for a custom field with the expression:

EvalExpressionWithGroupArray("Total(SumTurnover)", "Contractor")

the template composer will generate the following expression to be output:

JoinStrings(Array(Sort(EvalExpressionWithGroupValueTable ("Presentation(Total(DataSet.SumTurnover)), Total(DataSet.SumTurnover)", "DataSet.Contractor"), "2")))

EvalExpressionWithGroupValueTable

Syntax:

EvalExpressionWithGroupValueTable (Expression, GroupFieldsExpressions, RecordsFilter, GroupFilter)

Parameters:

Parameters:

• Expression - the expression to be calculated. A string type. The string may list multiple comma-separated expressions. Each expression may be followed by an optional AS keyword and a name of a value table column. Example: "Contractor, Total(SumTurnover) AS SalesVolume".
• GroupFieldsExpressions - comma-separated expressions of the group fields. Example: "Contractor, Batch".
• RecordsFilter - an expression applied to detailed records. Example: "DeletionMark = False". If this parameter includes an aggregate function, an error is generated during data composition.
• GroupFilter - a filter applied to group records. Example: "Total(SumTurnover) > &Parameter1".

The function retrieves a table of values with every item containing the result of calculating the expression for the group by the specified field.

Example:

EvalExpressionWithGroupValueTable ("Contractor AS Contractor, Total(SumTurnover) AS SalesVolume", "Contractor")

The result of this function is a table of values that has columns named Contractor and SalesVolume that will hold contractors with their respective volumes of sales.

When the template composer generates a template, it converts function parameters into data composition template field terms. For example, the Contractor field will be converted into DataSet.Contractor.

For example, for a custom field with the expression:

EvalExpressionWithGroupValueTable ("Contractor, Total(SumTurnover)", "Contractor")

the template composer will generate the following expression to be output:

JoinStrings(GetPart(Sort(EvalExpressionWithGroupValueTable ("DataSet.Contractor, dataSet.ContractorPresentation, Total(DataSet.SumTurnover), Presentation(DataSet.SumTurnover), DataSet.SortingField", "DataSet.Contractor"), "5, 1, 3"), "2, 4"))

Level

This function is intended to retrieve the current record level.

Example:

Level()

SequenceNumber ()

This function retrieves the next sequence number.

Example:

SequenceNumber()

SequenceNumberInGrouping ()

This function returns the next sequence number in the current grouping.

Example:

SequenceNumberInGrouping()

Format

This function retrieves a formatted string of the transferred value.

Syntax:

Format(Value, FormatString)

Parameters:

• Value - the expression to be formatted.
• FormatString - the format string defined in compliance with the 1C:Enterprise format string.

Example:

Format(Invoices.SumDoc, "NFD=2")

BeginOfPeriod

This function is intended to extract a certain date from a given date.

Syntax:

BeginOfPeriod(Date, PeriodType)

Parameters:

• Date - Date type. Specified date.
• PeriodType - String type. Contains one of the following values:
  • Minute
  • Hour
  • Day
  • Week
  • Month
  • Quarter
  • Year
  • TenDays
  • Halfyear

Example:

BeginOfPeriod(DateTime(2002, 10, 12, 10, 15, 34), "Month")

Result:

01.10.2002 0:00:00

EndOfPeriod

This function is intended to extract a certain date from a given date.

Syntax:

EndOfPeriod(Date, PeriodType)

Parameters:

• Date - Date type. Specified date.
• PeriodType - String type. Contains one of the following values:
  • Minute
  • Hour
  • Day
  • Week
  • Month
  • Quarter
  • Year
  • TenDays
  • Halfyear

Example:

EndOfPeriod(DateTime(2002, 10, 12, 10, 15, 34), "Week")

Result:

13.10.2002 23:59:59

DateAdd

This function is used to add a value to a date.

Syntax:

DateAdd(Expression, IncrementType, Size )

Parameters:

• Expression - Date type. Source date.
• IncrementType - String type. Contains one of the following values:
  • Minute
  • Hour
  • Day
  • Week
  • Month
  • Quarter
  • Year
  • TenDays
  • Halfyear
• Size - specifies the value of the date increment. Number type. Fractions are ignored.

Example:

DateAdd(DateTime(2002, 10, 12, 10, 15, 34), "Month", 1)

Result:

12.11.2002 10:15:34

DateDifference

This function is intended to retrieve the difference between two dates.

Syntax:

DateDifference(Expression1, Expression2, DifferenceType)

Parameters:

• Expression1 - Date type. Deducted date.
• Expression2 - Date type. Source date.
• DifferenceType - String type. Contains one of the following values:
  • Second
  • Minute
  • Hour
  • Day
  • Month
  • Quarter
  • Year

Example:

DATEDIFFERENCE(DATETIME(2002, 10, 12, 10, 15, 34),
   DATETIME(2002, 10, 14, 9, 18, 06), "DAY")

Result:

2

Substring

This function extracts a substring from a string.

Syntax:

Substring(String, Position, Length)

Parameters:

• String - String type. The string a substring should be extracted from.
• Position - Number type. The position of the character where the substring begins.
• Length - Number type. The length of the extracted substring.

Example:

SUBSTRING(Contractors.Address, 1, 4)

StringLength

This function defines the string length.

Syntax:

StringLength(String)

Parameter:

• String - String type. The string to determine the length for.

Example:

String(Contractors.Address)

Year

This function extracts a year from a Date value.

Syntax:

Year(Date)

Parameter:

• Date - Date type. The date to determine a year for.

Example:

YEAR(Invoice.Date)

Quarter

This function extracts a quarter number from a Date value. The quarter number is normally in the range from 1 to 4.

Syntax:

Quarter(Date)

Parameter

• Date - Date type. The date to determine a quarter for.

Example:

QUARTER(Invoice.Date)

Month

This function is intended to obtain a month number from a Date value. The month number is normally in the range from 1 to 12.

Syntax:

Month(Date)

Parameter:

• Date - Date type. The date to determine a month for.

Example:

MONTH(Invoice.Date)

DayOfYear

This function retrieves the day of the year from a Date value. The day of the year is normally in the range from 1 to 365 (366).

Syntax:

DayOfYear(Date)

Parameter

• Date - Date type. The date to determine a day for.

Example:

DAYOFYEAR(Invoice.Date)

Day

This function retrieves the day of the month from a Date value. The day of the month is normally in the range from 1 to 31.

Syntax:

Day(Date)

Parameter

• Date - Date type. The date to determine a day of the month for.

Example:

DAY(Invoice.Date)

Week

This function retrieves the week number in a year from a Date value. Weeks are numbered starting from 1.

Syntax:

Week(Date)

Parameter

• Date - Date type. The date to determine a week number for.

Example:

WEEK(Invoice.Date)

WeekDay

This function retrieves the day of the week from a Date value. The day of the week is normally in the range from 1 (Monday) to 7 (Sunday).

Syntax:

WeekDay(Date)

Parameter

• Date - Date type. The date for which a day in a week number should be determined.

Example:

WEEKDAY(Invoice.Date)

Hour

This function retrieves the hour (in 24-hour format) from a Date value. An hour number is in the range from 0 to 23.

Syntax:

Hour(Date)

Parameter

• Date - Date type. The date for which an hour in the day should be determined.

Example:

HOUR(Invoice.Date)

Minute

This function retrieves the minute number from a Date value. A minute in an hour is in the range from 0 to 59.

Syntax:

Minute(Date)

Parameter

• Date - Date type. The date a minute in an hour should be determined for.

Example:

MINUTE(Invoice.Date)

Second

This function retrieves the second in a minute from a Date value. A second number is in the range from 0 to 59.

Syntax:

Second(Date)

Parameter

• Date - Date type. The date a second in a minute should be determined for.

Example:

SECOND(Invoice.Date)

Choice

This function extracts a type from an expression that can contain a compound type. If the expression contains a type other than the required one, the NULL is returned.

Syntax:

 

Choice(Expression, TypeSpecification)

 

Parameters:

• Expression - transformed expression.
• TypeSpecification - String type. Contains a type string. For example, Number, String, etc. In addition to primitive types, the string can also contain a table name. In this case the function attempts to cast to the table reference.

Example:

Choice(Data.Attribute1, "Number(10,3)")

IsNull (Epression1, Expression2)

Parameters:

• Expression1 - value to validate;
• Expression2 - returned value if Expression1 value is NULL.

Example:

ISNULL(Total(Sales.SumTurnover), 0)

ACos 

Calculates arccosine in radians.

Syntax:

ACos(Expression)

Parameter:

• Expression - Number type. Cosine value (from -1 to 1) by which the angle is defined.

ASin 

Calculates arcsine in radians.

Syntax:

ASin(Expression)

Parameter:

• Expression - Number type. Sine value (from -1 to 1) by which the angle is defined.

ATan 

Calculates arctangent in radians.

Syntax:

ATan(Expression)

Parameter:

• Expression  - Number type. Tangent value by which the angle is defined.

Cos 

Calculates cosine.

Syntax:

Cos(Expression)

Parameter:

• Expression -  Number type. Is specified in radians.

Exp 

Calculates the exponential function.

Syntax:

Exp(Expression)

Parameter:

• Expression - Number type. Value of power.

Log 

Calculates Napierian logarithm.

Syntax:

Log(Expression)

Parameter:

• Expression - Number type. Source number, greater  than 0.

Log10 

Calculates Briggs logarithm for X parameter 10.

Syntax:

Log10(Expression)

Parameter:

• Expression -  Number type. Source number, greater  than 0.

Pow 

Exponentiates <X> to the degree of <Y>.

Syntax:

Pow(Base, Factor)

Parameters:

• Base - Number type. Base of exponentiation operation.
• Factor - Number type. Exponent.

Sin 

Calculates sine.

Syntax:

Sin(Expression)

Parameter:

• Expression -  Number type. Is specified in radians.

Sqrt 

Calculates square root of expression.

Syntax:

Sqrt(Expression)

Parameter:

• Expression - Number type. Nonnegative number.

Tan

Calculates tangent.

Syntax:

Tan(Expression)

Parameter:

• Expression - Number. Value of sine by which the angle is determined.

Round

Rounds source number to the required digit capacity. Rounding mode is standard (1.5 as 2).

Syntax:

Round(Expression, DigitCapacity)

Parameters:

• Expression -  Number type.  Source number;
• DigitCapacity -  Number type. Number of decimal places in fractional parts, to which the number is rounded..

Int

Removes the fractional part.

Syntax:

Int(Expression)

Parameter:

• Expression -  Number type. Fractional number.

Shared Module Functions

The data composition mechanism expression can contain function calls in global shared modules of the configuration. You do not need to use additional syntax to call these functions.

Example:

BriefDescription(Documents.Ref, Documents.Date, Documents.Number)

This example will call the "BriefDescription" function from a shared module of the configuration.

Note that you can only use shared module functions if you specify the corresponding data composition processor parameter.

Moreover, shared module functions cannot be used in custom field expressions.

Presentation

This function retrieves the string presentation of the non-primitive type value delivered. The value itself is returned for primitive values.
If an array or a table of values is used as a parameter, the function returns a string containing string presentations of all the array items separated by "; ". If an item has an empty string presentation, a string reading "<Empty value>ouping;" is displayed instead of its presentation.

Example:

Presentation(Contractor)

String

This function transforms a passed value into a string.
If an array or a table of values is used as a parameter, the function returns a string containing string presentations of all the array items separated by "; ". If an item has an empty string presentation, a string reading "<Empty value>ouping;" is displayed instead of its presentation.

Example:

String(SaleDate)

ValueIsFilled

For NULL and Undefined values False is always returned.
For the Boolean values True is always returned.
True is returned for other types if the value does not match the default value for this type.

Example:

ValueIsFilled(DeliveryDate)

LevelInGroup

This function gets the current level of the record in relation to the grouping.
It can be used to retrieve the nesting level for a record in a hierarchy grouping.

Example:

LevelInFroup()

 

ValueType

Syntax:

ValueType(Expression)

Parameter:

• Expression - String type. String value of type.

Returns a value of Type type containing a value type of the function parameter .

 

See also:
                       Data Composition System Expression Language
                       Bilingual Presentation of Data Composition System Keywords