Log in as a student
Log in as a student to access school materials
Data composition system 1C 8.3 for beginners: consider the results (resources)
The purpose of this tutorial will be:
- Write a report that displays a list of foods (Food directory), their calorie content and taste.
- Group products by color.
- Learn about summarizing (resources) and calculated fields.
Creating a new report
As in the previous lessons, open the base " Deli"in the configurator and create a new report via the menu" File"->"New...":
Document type - external report:
In the report settings form, write the name " Lesson 3"and press the button" Open data composition schema":
Leave the default schema name and click the " Done":
Adding a request through the constructor
On the " Data set"push greenplus sign and select item " Add dataset - query":
Instead of writing the request text manually, we run again query constructor:
On the " Tables"drag the table" Food"from the first column to the second:
Selecting from the table " Food"the fields that we will request. To do this, drag the fields" Name", "Taste", "Colour"and" Calorie content"from the second column to the third:
It turned out like this:
Press the button " OK"- the request text was generated automatically:
We form the settings for the presentation of the report
Go to the tab " Settings"and click on magic wand, to call settings constructor:
Choosing the type of report " List... "and press the button" Further":
Drag from the left column to the right fields that will be displayed in the list and click " Further":
Drag from the left column to the right margin " Colour"- it will be grouping lines in the report. Click " OK":
And here is the result of the constructor's work. The hierarchy of our report:
- report as a whole
- grouping "Color"
- detailed records - lines with food names
Save the report (button diskette) and without closing of the configurator, we will immediately open it in user mode. It turned out like this:
Change the order of columns
But let's change the order columns (up and down arrows) so that it looks like the picture below:
Let's save the report and re-open it in user mode:
Great, so much better.
Summing up (amount) by calorie content
It would be nice to display the total calorie content of foods by group. To see the sum of the calories of all foods, say, white or yellow. Or find out the total calorie content of all products in the database.
For this there is a mechanism for calculating resources.
Go to the " Resources"and drag the field" Calorie content"(we are going to summarize it) from the left column to the right.
In this case, in the expression field, select from the drop-down list " Amount (Calories)", since the total will be the sum of all the elements included in the total:
We save and generate a report:
We now have totals for each of the groups and for the report as a whole.
Summing up (average) by calorie content
Now let's make it so that one more column displays average calorie content of products by groups and in general according to the report.
You cannot touch the already existing "Calorie" column - the total sum is already displayed in it, therefore let's get another field, which will be an exact copy of the "Calorie" field.
To create such a "virtual" field, we will use the mechanism calculated fields.
Go to the tab " Calculated fields"and click greenplus sign:
In a collumn " Data path"write the name of the new field ( together, without spaces). Let it be called " Average calorie content"and in the column" Expression"we write the name of an existing field, on the basis of which the new field will be calculated. We write there" Calorie content". Speaker " Heading"will be filled in automatically.
We have added a new field (" Average calorie content"), but it will not appear in the report by itself - either you need to call settings constructor ("magic wand") or add this field manually.
Let's do second way. To do this, go to the " Settings", choose" Report"(after all, we want to add the field as a whole to the report), select the tab below" Selected fields"and drag the field" Average calorie content"from the left column to the right:
It turned out like this:
We save and generate a report:
The field has appeared and we see that its values \u200b\u200bare the values \u200b\u200bof the "Calorie" field. Excellent!
To do this, we will again use the already familiar mechanism resources (summarizing). Go to the tab " Resources"and drag the field" Average calorie content"from the left column to the right:
Moreover, in the column " Expression"choose" Average (Average Calorie)":
We save and generate a report:
We see that for groups, that is, for each color, and for the report as a whole, the average value was calculated quite correctly. But there are extra entries for individual products (not for groups) that we would like to remove from the report.
Do you know why they appeared (values \u200b\u200bnot by group)? Because when we added the field " Average calorie content"in the report settings, in the second step we selected the whole report as a whole and this new field hit the item " Detailed records".
Let's fix the error. To do this, let's go back to the " Settings", select" Detailed records"first from above (step 2), and then" Detailed records"below (step 3), go to the tab" Selected fields"and we will see an element in its right column" Auto".
Element " Auto"is not one field. These are several fields that get here automatically based on higher settings.
To see what these fields are - click on the " Auto" right button and select the item " Expand":
Element " Auto"expanded into the following fields:
And here is our field " Average calorie content"which got here from the item" Report"when we dragged him there. take off checkbox next to this field to remove its output.
In this short note I want to show how you can summarize values \u200b\u200bat different levels of grouping in a report using the data composition system.
As shown in the image, only at the "Item groups" grouping level, the "Order" resource is calculated, it displays how much you need to order for the current item group based on certain conditions:
This value can only be calculated at this grouping level, since, above or below, there are no values \u200b\u200bto calculate. For example, at the level of detailed records, there is no data on the maximum number in a group, because this data is only true for the group as a whole, and not for its individual components.
Accordingly, now it is necessary to calculate the totals for the above groupings ("Warehouses", "Types of Warehouses") and the grand total.
To do this, use the function EvaluateGroupExpressionArray:
CALCULATE EXPRESSION BY ARRAY GROUP (EVALEXPRESSIONWITHGROUPARRAY)
Syntax:
EvaluateGroupExpressionArray (,)
Description:
The function returns an array, each element of which contains the result of evaluating the expression to be grouped by the specified field.
The layout composer, when generating the layout, converts the function parameters to the terms of the data composition layout fields. For example, the Account field will be converted to Dataset.Contractor.
When generating expressions for displaying a custom field, the expression of which contains only the CalculateArrayWithGroupArray () function, the layout composer generates the displayed expression so that the displayed information is ordered. For example, for a custom field with an expression:
CalculateExpressionGroupedArray ("Sum (SumTurnover)", "Counterparty")
The layout composer will generate the following expression for output:
Connect Rows (Array (Array (EvaluateExpressionWith GroupedValuesTable ("Representation (Sum (Dataset.SumTurnover)), Sum (Dataset.SumTurnover)", "Dataset.Contractor"), "2")))
Options:
Type: String. The expression to evaluate. A string, for example, Amount (AmountTurnover).
Type: String. Grouping field expressions are grouping field expressions, separated by commas. For example, Counterparty, Party.
Type: String. An expression describing the selection applied to detail records. The expression does not support the use of aggregate functions. For example, Deletion Mark \u003d False.
Type: String. An expression describing the filtering applied to group records. For example, Sum (AmountTurnover)\u003e & Parameter1.
Example:
Maximum (CalculateExpressionWithGroupArray ("Sum (SumTurnover)", "Counterparty"));
A detailed description of the function syntax can be found at http://its.1c.ru/db/v837doc#bookmark:dev:TI000000582
Now for the calculation, we will duplicate the "Order" field, with different values \u200b\u200b"Calculate by ...", using the following expressions, note that in each higher level, the values \u200b\u200bof the levels below the groupings are used.
As a result, we get the following construction:
In light of the upcoming release of 8.2.14, I will try to describe some of the new functions of the data composition system.
Open the data composition schema, preferably in an external report, to make it easier to edit.
We add a dataset of the query type and write, either manually or using the query constructor, a simple query:
1. Set up a request in the ACS.
2. Setting up calculated fields in the ACS
3. We configure the data composition on the settings tab
4. Launch 1C Enterprise 8.2.14. We open the report. We form, we get.
Description of the new functions themselves:
1. The current date()
Returns the system date. When the layout is linked, in all expressions that are present in the layout, the CurrentDate () function is replaced with the value of the current date.
2. CALCULATE EXPRESSION ()
Syntax:
Evaluate Expression (,)
Description:
The function is intended for evaluating an expression in the context of some grouping.
The function takes into account the selection of groupings, but does not take into account hierarchical selections.
The function cannot be applied to a grouping in the group selection of this grouping. For example, in the selection of the grouping Nomenclature, you cannot use the expression CalculateExpression ("Sum (SumTurnover)", "TotalTotal")\u003e 1000... But such an expression can be used in hierarchical selection.
If the end record precedes the start record, then it is considered that there are no records for calculating detailed data and calculating aggregate functions.
When calculating interval expressions for a grand total (the Grouping parameter is set to General Total), it is assumed that there are no records for calculating detailed data and calculating aggregate functions.
Layout linker when generating function expression EvaluateExpression, in case the ordering expression contains fields that cannot be used in the grouping, replaces the function EvaluateExpression on NULL.
Options
A type: Line... The expression to evaluate.
A type: Line... Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as a grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal string is used as the grouping name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent grouping with that name.
For instance:
Amount (Sales.Turnover Amount) / Calculate ("Amount (Sales.Turnover Amount)", "TotalTotal")
In this example, the result is the ratio of the sum over the field Sales.AmountTurnover grouping records to the sum of the same field in the entire layout;
A type: Line... The parameter can take the following values:
· Grand total - the expression will be evaluated for all grouping records.
· Hierarchy - the expression will be evaluated for the parent hierarchical record, if any, and for the entire grouping, if there is no parent hierarchical record.
· Grouping - the expression will be calculated for the current group record of the grouping.
· Non-Resource Grouping - when calculating a function for a group record by resources, the expression will be calculated for the first group record of the original grouping.
When calculating the function EvaluateExpression () with the meaning Non-Resource Grouping for group records that are not groupings by resources, the function is calculated in the same way as it would be calculated if the parameter value was equal to the value Grouping.
The composer of the data composition template, when generating the data composition template, when outputting the resource-field by which the grouping is performed to the template, puts an expression calculated using the function EvaluateExpression () , specifying the parameter Non-Resource Grouping... For the rest of the resources, the regular resource expressions are grouped by resource.
A type: Line... Indicates with which record to start the fragment, in which to calculate the aggregate functions of the expression and from which record to get the values \u200b\u200bof fields outside the aggregate functions. The value can be one of the following:
· First
· Last
· Previous (Previous)
· Next (Next)
· Current
· Limiting Value (BoundaryValue) Limiting Value
A type: Line... Indicates to which record to continue the fragment in which the aggregate functions of the expression are evaluated. The value can be one of the following:
· First... You need to get the first grouping record. An expression can be specified after the word in brackets, the result of which will be used as an offset from the beginning of the grouping. The resulting value must be an integer number greater than zero. For example, First (3) - getting the third record from the beginning of the grouping.
If the first record is outside the grouping, then it is considered that there are no records. For example, if there are 3 records, and you want to get the First (4), then it is considered that there are no records.
· Last... You need to get the latest grouping record. An expression can be specified after the word in brackets, the result of which will be used as an offset from the end of the grouping. The resulting value must be an integer number greater than zero. For example, Last (3) - Get the third record from the end of the group.
If the last record is outside the grouping limits, then it is considered that there are no records. For example, if there are 3 records, and you want to get the Last (4), then it is considered that there are no records.
· Previous (Previous)... You need to get the previous grouping record. An expression can be specified after the word in brackets, the result of which will be used as an offset back from the current grouping record. For example, Previous (2) - get the previous one from the previous record.
If the previous record goes beyond the grouping (for example, for the second grouping record you want to get Previous (3), then the first grouping record is obtained.
When retrieving the previous record for the grouping total, the first record is considered.
· Next (Next)... You need to get the next grouping record. An expression can be specified after the word in brackets, the result of which will be used as an offset forward from the current grouping record. For example, Next (2) - get next from next record.
If the next record goes out of the grouping, then it is considered that there are no records. For example, if there are 3 records and Next () is received for the third record, then there are no records.
When you get the next record for the grouping total, it is considered that there is no record.
· Current... You need to get the current record.
When retrieving for the grouping total, the first record is obtained.
· Limiting Value (BoundaryValue)... The need to get a record at the specified value. After the word Limiting Value in brackets you need to specify an expression with the value of which you want to start a fragment, the first ordering field.
As a record, the first record will be obtained, the value of the ordering field of which is greater than or equal to the specified value. For example, if the Period field is used as the ordering field, and it has the values \u200b\u200b01.01.2010, 01.02.2010, 01.03.2010, and you want to get ConstrainingValue (DateTime (2010, 1, 15)), then you will receive a record with the date 02/01/2010.
A type: Line... Lists expressions, separated by commas, that describe the ordering rules. If not specified, the ordering is performed in the same way as for the grouping for which the expression is evaluated. After each expression, you can specify a keyword Age (to sort in ascending order), Descending (for descending order) and Auto-ordering (to order the referenced fields by the fields by which to order the referenced object). Word Auto-ordering can be used as with a word Age, so with the word Descending.
A type: Line... Similar to the parameter Sorting... Used to arrange hierarchical records. If not specified, the layout composer generates the ordering according to the ordering specified in the parameter Sorting.
A type: Line... Specifies the rule for determining the previous or next record in case there are several records with the same ordering value:
· Separately indicates that a sequence of ordered records is used to define the previous and next records. Default value.
· Together indicates that the previous and next records are determined based on the values \u200b\u200bof the ordering expressions.
For example, if the resulting sequence is ordered by date:
| № | date | Full name | Value |
| 1 | 01 January 2001 |
Ivanov M. |
10 |
| 2 | 02 January 2001 | Petrov S. | 20 |
| 3 | 03 January 2001 | Sidorov R. | 30 |
| 4 | 04 January 2001 | Petrov S. | 40 |
Separately, then:
The previous entry to entry 3 will be entry 2.
Current, Current (respectively, parameters Start and the end), then for record 2 this fragment will consist of one record 2. The expression will be equal to 20.
If the parameter value is Together, then:
The previous entry to entry 3 will be entry 1.
§ if the calculated fragment is defined as Current, Current (respectively, parameters Start and the end), then for record 2 this fragment will consist of records 2 and 3. Expression CalculateExpression ("Sum (Value)", Current, Current) will be 50.
When specified, the parameter value is Together, in parameters Start and the end you cannot specify an offset for positions First, Last, Previous, Next.
Calculate Expression ("Sum (SumTurnover)", "First", "Current")
If you want to get the grouping value in the previous line, you can use the following expression:
Evaluate Expression (Course, Previous)
List new functions:EvaluateExpressionGroupedArray(,) -
The function returns an array, each element of which contains the result of evaluating the expression to be grouped by the specified field.
EvaluateExpressionGroupedValuesTable(,) -
The function returns a table of values, each row of which contains the result of evaluating expressions to group by the specified field
ValueFilled() - Returns True if the value is different from the default value of the given type, other than NULL, other than an empty reference, other than Undefined. For Boolean values, a null check is performed. For strings, a check is made for the absence of non-whitespace characters
Format(,) - Get the formatted string of the passed value. The format string is set in accordance with the format string of the 1C: Enterprise system.
Substring(,,) - This function is designed to extract a substring from a string.
Line Length() - The function is designed to determine the length of the string. Parameter is a string type expression
Line() - If an array is passed as a parameter, the function returns a string containing string representations of all array elements separated by ";" characters. If a table of values \u200b\u200bis passed as a parameter, the function returns a string containing the string representations of all rows of the table of values, and the cell representations of each row are separated by ";" characters, and the strings are separated by a line feed. If any element has an empty string representation, then a string is displayed instead of its representation.
Smart use of the data composition scheme (ACS) allows you to:
- significantly reduce the time required to develop a report;
- get rid of the need to create a managed form handler;
- get a beautiful result with the possibility of additional customization by the user.
But not all developers make the most of the circuit's capabilities, since not all of its settings are obvious and intuitive. In particular, many people know that in 1C there are calculated fields in the ACS, however, they do not fully represent the area of \u200b\u200btheir use and methods of working with them.
What is a calculated field
In most cases, a query acts as a data source in a layout schema. In principle, within the query itself, you can already use various formulas, constructions and expressions. A natural question arises, why do we need duplicate functionality?
The fact is that the ACS is something more than just displaying the result of the query execution, and this is perfectly visible from the form for creating a schema (Fig. 1).
Calculated fields allow you to perform certain actions with the generated data set:
- Output to a specific cell an array of data received by a query, combining several lines into one;
- Access the export functions of the common module;
- Execute various expressions available for the layout language and use the EvaluateExpression special functions.
Let's follow this list and go.
An array of values \u200b\u200bin one cell
Let's simulate a situation when it is necessary to get in a separate cell all the numbers of receipts for a counterparty:
thus, we have created an additional calculated field in our scheme;
As you can see from the above example, there are no difficulties in adding and processing calculated fields. We used two functions: Array () and JoinStrings ().
A few words about the latter. In addition to the first parameter indicating the identifier of the array, values \u200b\u200bor values, two more can be set in it:
- Element Separator - indicates which character will separate one element of an array or one row of a table of values \u200b\u200bfrom another (in our case, we omitted this parameter and a line break was assigned by default);
- Column Separator is a character used to separate the columns of a table of values \u200b\u200b(semicolon is used by default).
Accessing the Export Functions of a Common Module
The functions of the general module can act as a data source for filling in a calculated field.
A few important points:
- The function must be exportable;
- If a function is located in a common module with the "Global" flag set, it is called directly by name, otherwise the function should be called according to the "Common module name" scheme. The name of the called function.
As an example of use, we will take the same request to the receipt documents and display it in a separate column. We will not describe the request itself, we will go directly to the calculated fields:
Thus, we see that almost any data handler can be initialized from the ACS, which greatly expands the possibilities of using the schema.
Link Language Expressions
Quite often in the developer's work, a situation arises when it is necessary to display the result of division in the ACS field:
- Calculate the average cost of the item;
- All kinds of interest;
- Calculations of average earnings, etc.
To avoid problems, in these cases it is advisable to enter a division by 0 check in the calculated field.
This can be done using the "Choice When .... Then ... Otherwise ... End" construction.
At the end, a few words about the fairly new function EvaluateExpression (). With its help, in particular, you can calculate the deviations in value between the current and the previous line, cumulative balance, etc.
Suppose, you can get the Amount of the Document from the previous line of our query by specifying the value Calculate Expression ("Amount of Document", "PreviousSum") in the "Expression" field.
Data Composition System Expression Language
The Expression Language of the Data Composition System is designed to write expressions used in various parts of the system.
Expressions are used in the following subsystems:
- data composition schema - for describing calculated fields, total fields, relationship expressions, etc.
- data composition settings - for describing custom field expressions;
- data composition template - for describing data set relationship expressions, describing template parameters, etc.
Literals
Expressions can contain literals. Literals of the following types are possible:
- Line;
- Number;
- Date;
- Boolean.
Line
A string literal is written in "" "characters, for example:
"String literal"
If you need to use the "” "character inside a string literal, you should use two such characters.
For instance:
“Literal“ “in quotes“ ““
Number
The number is written without spaces, in decimal format. The fractional part is separated with the symbol "." For instance:
10.5 200
date
The date literal is written using the DATETIME key literal. After this keyword, in brackets, separated by commas, the year, month, day, hours, minutes, seconds are listed. The time is optional.
For instance:
DATE TIME (1975, 1, 06) - January 6, 1975 DATE (2006, 12, 2, 23, 56, 57) - December 2, 2006, 23 hours 56 minutes 57 seconds, 23 hours 56 minutes 57 seconds
Boolean
Boolean values \u200b\u200bcan be written using the literals True, False.
Value
To specify literals of other types (system enumerations, predefined data), use the Value keyword, followed by the literal name in parentheses.
Value (Account Type. Active)
Operations on numbers
Unary -
This operation is intended to reverse the sign of a number. For instance:
Sales.Number
Unary +
This operation does not perform any action on the number. For instance:
Sales.Number
Binary -
This operation is designed to calculate the difference between two numbers. For instance:
Balances & Turnovers.Initial Balance - Balances & Turnovers.Final Balance Balances & Turnovers.Initial Balance - 100 400 - 357
Binary +
This operation is designed to calculate the sum of two numbers. For instance:
Balances & Turnovers.Starting Balance + Balances & Turnovers.Turnover Balances & Turnovers.Initial Balance + 100 400 + 357
Composition
This operation is designed to calculate the product of two numbers. For instance:
Nomenclature.Price * 1.2 2 * 3.14
Division
This operation is intended to obtain the result of dividing one operand by another. For instance:
Nomenclature.Price / 1.2 2 / 3.14
Remainder of the division
This operation is intended to obtain the remainder of dividing one operand by another. For instance:
Nomenclature Price% 1.2 2% 3.14
Operations on strings
Concatenation (Binary +)
This operation is intended to concatenate two strings. For instance:
Nomenclature.Article + ":" + Nomenclature.Name
Like
This operation checks if the string matches the passed pattern.
The value of the LIKE operator is TRUE if the value<Выражения> matches the pattern, and FALSE otherwise.
The following characters in<Строке_шаблона> make sense other than just the next line character:
- % - percentage: a sequence containing zero or more arbitrary characters;
- _ - underscore: one arbitrary character;
- […] - one or more characters in square brackets: one character, any of those listed inside square brackets. The enumeration may contain ranges, for example, a-z, meaning an arbitrary character included in the range, including the ends of the range;
- [^ ...] - in square brackets a negation icon followed by one or more characters: any character, except those listed after the negation icon;
Any other symbol means itself and does not carry any additional meaning. If one of the listed characters must be recorded as oneself, then it must be preceded by<Спецсимвол>specified after the keyword ESCAPE.
For example, the template
"% ABC [abcg] \\ _ abc%" SPECIAL SYMBOL "\\"
means a substring consisting of a sequence of characters: the letter A; letters B; letters B; one digit; one of the letters a, b, c or d; underscore character; letters a; letters b; letters c. Moreover, this sequence can be located starting from an arbitrary position in the line.
Comparison operations
Equally
This operation is designed to compare two operands for equality. For instance:
Sales.Contractor \u003d Sales.NomenclatureMainSupplier
Not equal
This operation is intended to compare two operands for inequality. For instance:
Sales, contractor<> Sales.NomenclatureMainSupplier
Less
This operation is intended to check that the first operand is less than the second. For instance:
SalesCurrent.Amount< ПродажиПрошлые.Сумма
More
This operation is intended to check that the first operand is greater than the second. For instance:
SalesCurrent.Total\u003e SalesPast.Amount
Less or equal
This operation is designed to check that the first operand is less than or equal to the second. For instance:
SalesCurrent.Amount<= ПродажиПрошлые.Сумма
More or equal
This operation is designed to check that the first operand is greater than or equal to the second. For instance:
SalesCurrent.Amount\u003e \u003d SalesPast.Amount
Operation B
This operation checks for the presence of a value in the passed list of values. The result of the operation will be True if the value is found, or False otherwise. For instance:
Item B (& Item1, & Item2)
The operation of checking the existence of a value in a dataset
The operation checks for the existence of a value in the specified data set. The validation dataset must contain one field. For instance:
Sales. Counterparty To Counterparties
The operation of checking a value for NULL
This operation returns True if the value is NULL. For instance:
Sales.Contractor IS NULL
Operation of checking a value for NULL inequality
This operation returns True if the value is not NULL. For instance:
Sales.Contractor IS NOT NULL
Logical operations
Logical operations take Boolean expressions as operands.
Operation NOT
The operation does NOT return True if its operand is False, and False if its operand is True. For instance:
NOT Document.Consignee \u003d Document.Shipper
Operation AND
The AND operation returns True if both operands are True, and False if one of the operands is False. For instance:
Document.Consignee \u003d Document.Shipper AND Document.Consignee \u003d & Counterparty
OR operation
The OR operation returns True if one of the operands is True, and False if both operands are False. For instance:
Document.Consignee \u003d Document.Carrier OR Document.Consignee \u003d & Counterparty
Aggregate functions
Aggregate functions perform some action on a dataset.
Amount
The Sum aggregate function calculates the sum of the expression values \u200b\u200bpassed to it as an argument for all detail records. For instance:
Amount (Sales.AmountTurnover)
amount
The Count function calculates the number of non-null values. For instance:
Quantity (Sales. Contractor)
Number of different
This function calculates the number of distinct values. For instance:
Quantity (Various Sales. Contractor)
Maximum
The function gets the maximum value. For instance:
Maximum (Balances.Quantity)
Minimum
The function gets the minimum value. For instance:
Minimum (Balances.Quantity)
The average
The function gets the average for non-NULL values. For instance:
Average (Balances.Amount)
Other operations
Operation SELECT
Operation Select is designed to select one of several values \u200b\u200bwhen certain conditions are met. For instance:
Choice When Amount\u003e 1000 Then Amount Else 0 End
Rules for comparing two values
If the types of the compared values \u200b\u200bdiffer from each other, then the relationship between the values \u200b\u200bis determined based on the priority of the types:
- NULL (lowest);
- Boolean;
- Number;
- Date;
- Line;
- Reference types
The relationship between the different reference types is determined based on the reference numbers of the tables corresponding to the type.
If the data types match, then the values \u200b\u200bare compared according to the following rules:
- type Boolean has TRUE value greater than FALSE value;
- the Number type has the usual comparison rules for numbers;
- the Date type has earlier dates less than later ones;
- for the String type - string comparisons in accordance with the established national characteristics of the database;
- reference types are compared based on their values \u200b\u200b(record numbers, etc.).
Working with NULL
Any operation in which the value of one of the operands is NULL will result in NULL.
There are exceptions:
- the AND operation will return NULL only if none of the operands is False;
- the OR operation will return NULL only if none of the operands is True.
Operation Priorities
Operations have the following priorities (the first line has the lowest priority):
- B IS NULL IS NOT NULL;
- =, <>, <=, <, >=, >;
- Binary +, Binary -;
- *, /, %;
- Unary +, Unary -.
Data composition system expression language functions
Calculate
The Calculate function is designed to evaluate an expression in the context of some grouping. The function has the following parameters:
- Expression. Type String. Contains a calculated expression;
- Grouping. Type String. Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as a grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal string is used as the grouping name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent grouping with that name. For instance:
In this example, the result will be the ratio of the amount for the Sales.SumTurnover field of the grouping record to the amount of the same field in the entire layout.
Level
The function is intended to get the current recording level.
Level()
NumberOrder
Get the next sequence number.
NumberOrder ()
NumberOrderInGroup
Returns the next sequential number in the current grouping.
NumberOrderInGrouping ()
Format
Get the formatted string of the passed value.
The format string is set in accordance with the 1C: Enterprise format string.
Options:
- Value;
- Format string.
Format (Consignment Invoices.SumDoc, "NPT \u003d 2")
Beginning of period
Options:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
Beginning of Period (DateTime (2002, 10, 12, 10, 15, 34), "Month")
Result:
01.10.2002 0:00:00
End of Period
The function is designed to extract a specific date from a given date.
Options:
- Date. Type Date. Specified date;
- Period type. Type String. Contains one of the values:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
EndPeriod (DateTime (2002, 10, 12, 10, 15, 34), "Week")
Result:
13.10.2002 23:59:59
Add to Date
The function is intended for adding a certain value to the date.
Options:
- Magnification type. Type String. Contains one of the values:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
- Value - by how much the date should be increased. Type Number. The fractional part is ignored.
Add to Date (DateTime (2002, 10, 12, 10, 15, 34), "Month", 1)
Result:
12.11.2002 10:15:34
Date Difference
The function is designed to get the difference between two dates.
Options:
- Expression. Type Date. Original date;
- Expression. Type Date. Subtracted date;
- Difference type. Type String. Contains one of the values:
- Second;
- Minute;
- Day;
- Month;
- Quarter;
DIFFERENCE (DATE TIME (2002, 10, 12, 10, 15, 34), DATE TIME (2002, 10, 14, 9, 18, 06), "DAY")
Result:
Substring
This function is designed to extract a substring from a string.
Options:
- Line. Type String. The string from which to extract the substring;
- Position. Type Number. The position of the character from which the substring to be extracted from the string begins;
- Length. Type Number. The length of the substring to select.
SUBSTRATE (Contractors.Address, 1, 4)
Line Length
The function is designed to determine the length of a string.
Parameter:
- Line. Type String. The string to be specified in length.
String (Accounts.Address)
Year
This function is designed to extract a year from a value of the Date type.
Parameter:
- Date. Type Date. The date by which the year is determined.
YEAR (Consignment date)
Quarter
This function is intended to extract a quarter number from a value of the Date type. The quarter number is normally in the range from 1 to 4.
Parameter
- Date. Type Date. The date by which the quarter is determined
Month
This function is designed to extract the month number from the Date type value. The month number is normally in the range from 1 to 12.
- Date. Type Date. The date by which the month is determined.
Day of the Year
This function is designed to get the day of the year from a value of the Date type. The day of the year is normally in the range from 1 to 365 (366).
- Date. Type Date. The date by which the day of the year is determined.
Day
This function is intended to get the day of the month from a value of the Date type. The day of the month is normally in the range from 1 to 31.
- Date. Type Date. The date by which the day of the month is determined.
A week
This function is intended to get the week number of the year from a value of the Date type. The weeks of the year are numbered starting at 1.
- Date. Type Date. The date by which the week numbers are determined.
Day of the week
This function is intended to get the day of the week from a value of the Date type. The day of the week is normally between 1 (Monday) and 7 (Sunday).
- Date. Type Date. The date by which the day of the week is determined.
Hour
This function is intended to get the hour of the day from a value of the Date type. The hour of the day ranges from 0 to 23.
- Date. Type Date. The date by which the hour of the day is determined.
Minute
This function is designed to get the minute of the hour from a value of the Date type. The minute of the hour ranges from 0 to 59.
- Date. Type Date. The date by which the minute of the hour is determined.
Second
This function is intended to get the second of the minute from a value of the Date type. The second of a minute ranges from 0 to 59.
- Date. Type Date. The date by which the seconds of the minute are determined.
Express
This function is intended to extract a type from an expression that can contain a complex type. In case the expression contains a type other than the required type, a NULL value will be returned.
Options:
- The expression to be converted;
- Type indication. Type String. Contains a string of type. For example, "Number", "String", etc. In addition to primitive types, this string can contain the name of the table. In this case, an attempt will be made to express to a reference to the specified table.
Express (Data.Props1, "Number (10.3)")
YesNull
This function returns the value of the second parameter if the value of the first parameter is NULL.
Otherwise, the value of the first parameter will be returned.
YesNULL (Amount (Sales.SumTurnover), 0)
Common module functions
A data composition engine expression can contain calls to functions of global common configuration modules. No additional syntax is required to call such functions.
In this example, the function "Abbreviated Name" will be called from the general configuration module.
Note that the use of common module functions is allowed only when the corresponding data composition processor parameter is specified.
In addition, the functions of common modules cannot be used in custom field expressions.
