1s 8 ckd calculated fields. Examples of using the ckd function - evaluate an expression. Data composition system expression language functions

Competent use of the data composition scheme (ACS) allows:

• 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:

1. 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);
2. 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 division result in the ACS field:

1. Calculate the average cost of the item;
2. All kinds of interest;
3. Calculations of average earnings, etc.

In order 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 rather new function CalculateExpression (). 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.

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. We start 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 Nomenclature grouping, you cannot use the expression CalculateExpression ("Sum (SumTurnover)", "TotalTotal")\u003e 1000. But such an expression can be used in a 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 the 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.

The layout composer, when generating an expression for the function EvaluateExpression, if the ordering expression contains fields that cannot be used in the grouping, replaces the EvaluateExpression function with NULL.

Parameters

<Выражение>

Type: String. The expression to evaluate.

<Группировка>

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:

Amount (Sales.SumTurnover) / Calculate (“Amount (Sales.SumTurnover)”, “TotalTotal”)

In this example, the result will be the ratio of the amount for the Sales field.SumTurnover of the grouping record to the amount of the same field in the entire layout;

<ОбластьВычисления>

Type: String. The parameter can take the following values:

• Total - the expression will be evaluated for all grouping records.
• Hierarchy - the expression will be evaluated for the parent hierarchical record, if there is one, and for the entire grouping, if there is no parent hierarchical record.
• Grouping - the expression will be evaluated for the current grouping grouping record.
• GroupingNonResource - when evaluating 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 GroupNoResource value for group records that are not grouped by resource, the function is calculated in the same way as it would be calculated if the parameter value was equal to 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 GroupNoResource parameter. For the rest of the resources, the regular resource expressions are grouped by resource.

<Начало>

Type: String. 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:

<Конец>

Type: String. Indicates to which record to continue the fragment in which to evaluate the aggregate functions of the expression. The value can be one of the following:

• First (First). You need to get the first grouping record. After the word in parentheses, you can specify an expression, 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 First (4), then it is considered that there are no records.

• Last (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) - getting the third record from the end of the grouping.

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. 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 the next record is received for the grouping total, it is considered that there is no record.

• Current (Current). You need to get the current record.

When retrieving for the grouping total, the first record is obtained.

• BoundaryValue. The need to get a record at the specified value. After the word LimitingValue in parentheses, 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 a BoundingValue (DateTime (2010, 1, 15)), then you will receive a record with the date 01.02. 2010.

<Сортировка>

Type: String. 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 the keyword Ascending (for ascending order), Descending (for descending order), and AutoSorting (for ordering the referenced fields by the fields by which you want to order the referenced object). The word Autoorder can be used with both Ascending and Descending.

<ИерархическаяСортировка>

Type: String. Same as Sorting. Used to arrange hierarchical records. If not specified, the layout composer generates the ordering according to the ordering specified in the Sort parameter.

<ОбработкаОдинаковыхЗначенийПорядка>

Type: String. Specifies the rule for determining the previous or next record in case there are several records with the same ordering value:

• Separately means that a sequence of ordered records is used to define the previous and next records. Default value.
• Together means 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

If the parameter value is Separate, then:

The previous entry to entry 3 will be entry 2.

§ if the calculated fragment is defined as Current, Current (respectively, the Start and End parameters), then for record 2 this fragment will consist of one record 2. Expression Calculate Expression (“Sum (Value)”, Current, Current) 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, the Start and End parameters), then for record 2 this fragment will consist of records 2 and 3. Expression Calculate Expression (“Sum (Value)”, Current, Current) will be equal to 50.

When specifying the parameter value equal to Together, in the Start and End parameters you cannot specify an offset for the First, Last, Previous, Next positions.

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 group 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 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, it is checked 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 symbols “; “. If a table of values \u200b\u200bis passed as a parameter, the function returns a string containing string representations of all rows of the table of values, and the cell representations of each row are separated by “; “, And strings - with a line feed character. If any element has an empty string representation, then the string is displayed instead of its representation<Пустое значение>.

EvaluateExpression - a rather complex ACS function to understand, and examples of application in the reference information are rather scarce. This article contains examples that are sure to be useful to every developer:

1. cumulative total in the grouping;
2. the running total in the crosstab;
3. getting the previous value;
4. output of PM in one line.

1. Getting the indicator on a cumulative total

Let's get the number of goods on a cumulative basis at the grouping level. To do this, create a calculated field (see Figure 1).
On the "Resources" tab, set the function for the calculated field:
Calculate Expression ("Sum (AmountTurnover)", "First", "Current")
which will sum up the number of products from the first record to the current one (see Figure 2).

If you want to get the number of goods on a cumulative basis at the level of detailed records, then the function CalculateExpression is set for the calculated field on the tab "Calculated fields" (see Figure 3).
Depending on the level of obtaining the cumulative total, we create a grouping (see Figure 4): at the resource level - grouping by goods, at the DZ level - grouping of detailed records.

Figure 4. Groupings of the report with a cumulative total

2. Getting the value of the indicator from the previous line

Let's get the currency rate for the date and the previous date. To do this, create a calculated field and write the following expression in the expression field (see Figure 5):
Evaluate Expression (Course, Previous, Previous)
which will take the previous value of the currency rate for the current line, the last parameter of the function restricts data retrieval.
Since we are working at the level of detailed records, go directly to the "Settings" tab and create a grouping - detailed records.

3. Getting an indicator on a cumulative total in a cross-tab

Let's get the number of goods on a cumulative basis in the context of the period. To do this, we create a calculated field (see Figure 1). On the "Resources" tab for a calculated field, we will specify the following expression (see Figure 6):
Calculate Expression ("Sum (NumberTurnover)", "Period", "First", "Current")
which, at the grouping level, will calculate the quantity of goods in the interval from the first line to the current one in the context of the period for each item.
On the "Settings" tab, create a table grouped by item in a row and grouped by period in a column (see Figure 7).

4. Output of tabular section data in one line

Methods for outputting tabular section data in one line, including the method using the EvaluateExpression function, are discussed in the article

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...":

Type of document - external report:

In the form of setting the report, write the name " Lesson 3"and press the button" Open data composition schema":

Leave the default schema name and click the " Done":

Add 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 reopen 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". Column " Title"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 " 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 point" Report"when we dragged him there. take off checkbox next to this field to remove its output.