Examples of using the scd function - calculate an expression. Functions of the expression language of the data composition system 1C 8.3 reports calculation in custom fields

We examined in some detail the settings of reports implemented on the basis of the access control system. Now let's look at the more subtle and detailed settings report options. The window for “advanced” settings of the report option is called by the command “More” - “Other” - “Change report option”.

The window for changing the report version is divided into two parts:

1. Report structure.

2. Report settings.

The report option structure section is similar to the “Structure” tab of the standard report settings. The purpose and configuration of groupings is discussed in detail in Part 1 of the article.

The report variant structure table, in addition to the actual column with groupings, contains several additional columns:

The report option settings section gives the user ample opportunities to configure the report to suit their needs. It almost completely coincides with standard settings report discussed in part 1. Let's look at all the tabs of the section and note the differences.

The settings section consists of the following tabs:

1. Parameters. Contains ACS parameters available to the user.

The SKD parameter is a value used to obtain report data. This can be a condition value for selecting or checking data, as well as an auxiliary value.

The parameter table is presented in the “Parameter” - “Value” format. If necessary, you can change the parameter values. Clicking the “Custom Settings Element Properties” button opens the custom settings of the element.

In this window, you can select whether the element will be included in user settings (that is, visible to the user when setting up a report), set the element's presentation and editing mode ( quick access in the report header, normal in the report settings and unavailable).

Custom settings item properties also have groupable fields, margins, selections, and conditional appearance elements.

2. Custom fields. Contains fields that are generated by the user himself based on the data selected by the report.

The user can add two types of fields:

• New selection field...
• New expression field...

Selection fields allow you to calculate a value based on a given condition. The selection field editing window contains a field title and a table in which the selection, value and presentation of the field are specified. Selection is a condition, depending on which the desired value will be substituted.

For example, let's calculate an estimate of the number of sales. We will assume that if less than 10 units of a product are sold, we sold little, and if more than 10 units sold, we sold a lot. To do this, we will set 2 values ​​of the calculated field: the first will be with the selection “Number of goods Less than or equal to “10””, the second with the selection “Number of goods Greater than “10””.

Expression fields allow you to calculate a value using arbitrary algorithms. They can use the functions of the query language and the built-in 1C programming language. The expression field editing window contains two fields for expressions of detailed and summary records. Summary records are groupings configured in the “Report Structure” area and must use aggregate functions(“Amount”, “Minimum”, “Maximum”, “Quantity”).

For example, let's calculate the average discount percentage. The average discount percentage is calculated using the formula: [Sales amount without discount] - [Sales amount with discount] / [Sales amount without discount]. It is important to remember that the sales amount without a discount may be zero, so we use the SELECT operator to check. We get the following expressions:

· For detailed entries:

Choice

When [Sales amount without discount] = 0

Then 0

Otherwise [Sales amount without discount] - [Sales amount with discount] / [Sales amount without discount]

End

· For summary records:

Choice

When Amount([Sales amount without discount]) = 0

Then 0

Otherwise Sum([Sales amount without discount]) - Sum([Sales amount with discount]) / Sum([Sales amount without discount])

End

As mentioned earlier, in the expression of total records we use the aggregate function “Sum”.

3. Groupable fields. Contains fields by which the results of the report variant will be grouped. Grouped fields are configured separately for each grouping, but you can set general grouped fields for a report option if you select the root “Report” in the structure tree. You can add a field from the report result, a custom field, or select an auto field, then the system will select the fields automatically. This tab also allows you to change the order of grouped fields.

4. Fields. Contains the fields that will be output as a result of the report variant. Fields are configured separately for each grouping, but you can set common fields for a report option if you select the root “Report” in the structure tree. You can add a field from the report result, a custom field, or select an auto field, then the system will select the fields automatically. This tab also allows you to change the order of the fields.

Fields can be grouped to logically highlight any part of the report or to specify a special arrangement of columns. When adding a group, the “Location” column becomes active and allows you to select one of the location options:

• Auto - the system places fields automatically;
• Horizontal - fields are positioned horizontally;
• Vertical - fields are arranged vertically;
• IN separate column- fields are located in different columns;
• Together - the fields are located in one column.

5. Selection. Contains selections used in the report variant. Setting up selections was discussed in detail in part 1 of this article. Filters are configured separately for each grouping, but you can set general filters for a report option if you select the root “Report” in the structure tree.

6. Sorting. Contains the sort fields used in the report variant. Setting up sort fields was discussed in detail in Part 1 of this article. Sorting is configured separately for each grouping, but you can set general sorting fields for a report option if you select the root “Report” in the structure tree.

7. Conditional registration. Contains conditional design elements used in the report variant. Setting up conditional appearance was discussed in detail in Part 1 of this article. Conditional appearance is configured separately for each grouping, but you can set general elements of conditional appearance for a report option if you select the root “Report” in the structure tree.

8. Additional settings. Contains additional settings preparation of the report. Allows you to select a common appearance report, location of fields, groupings, details, resources, totals, set chart settings, control the display of the title, parameters and selection, determine the position of resources and fix the header and grouping columns of the report version.

In conclusion, I would like to note that report settings can not only be saved as a report option, but also uploaded to a file (menu “More” - “Save settings”). To download, you must select “Load settings” and select the saved file. Thus, we can transfer report variant settings between different databases that have the same configuration.

Based on this, we can summarize that the user can not only independently customize the report to suit his needs, but also save his settings and use them in the future if necessary.

Login to the site as a student

Login as a student to access school materials

Data composition system 1C 8.3 for beginners: counting the results (resources)

The purpose of this lesson will be:

• Write a report that displays a list of products (Food directory), their calorie content and taste.
• Group products by color.
• Learn about summarizing (resources) and calculated fields.

Create a new report

As in previous lessons, we open the database " Deli"in the configurator and create a new report through the menu" File"->"New...":

Document type - external report:

In the report setup form, write the name " Lesson3" and press the button " Open data composition diagram":

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

Adding a request through the constructor

On the tab " Dataset" click green plus sign and select " Add Dataset - Query":

Instead of writing the request text manually, we run it again query constructor:

On the "tab" Tables"drag the table" Food" from the first column to the second:

Select from the table " Food"fields that we will request. To do this, drag and drop the fields" Name", "Taste", "Color" 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:

Creating report presentation settings

Go to the tab " Settings" and click on magic wand to call settings designer:

Select the report type " List..." and press the button " Next":

Drag from the left column to the right the fields that will be displayed in the list and click " Next":

Drag from the left column to the right field " Color" - it will happen grouping lines in the report. Click " OK":

And here is the result of the designer’s work. Hierarchy of our report:

• the report as a whole
• grouping "Color"
• detailed entries - lines with food names

Save the report (button diskette) And without closing We will immediately open the configurator in user mode. It turned out like this:

Changing the order of columns

But let's let's change the order columns (up and down arrows) so that it looks like the figure below:

Let's save the report and open it again in user mode:

Great, that's much better.

Let's sum up the calorie content

It would be nice to summarize the calorie content of foods by group. To see the sum of the calorie content of all products, say, white or yellow. Or find out the total calorie content of all products in the database.

For this purpose, there is a mechanism for calculating resources.

Go to the tab " 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 field, select the expression from the drop-down list " Amount(Calorie)", since the total will be the sum of all elements included in the total:

We save and generate a report:

We now have results for each of the groups and for the report as a whole.

Let's sum it up (average) in terms of calories

Now let's make it appear in another column average calorie content of products by groups and in the report as a whole.

You cannot touch the existing “Calorie” column - the total is already displayed in it, so let's create 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 press green plus sign:

In the column " Data path"we write the name of the new field ( seamlessly, no 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" 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 - you need to either call again settings designer("magic wand") or add this field manually.

Let's do it second way. To do this, go to the tab " Settings", select " Report"(after all, we want to add the field as a whole to the report), select the tab at the bottom" 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 ​​are the values ​​of the "Calorie" field. Great!

To do this, we will again use the mechanism already familiar to us 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(AverageCalorie)":

We save and generate a report:

We see that for the groups, that is, for each color, and for the report as a whole, the average value was calculated absolutely correctly. But they are present extra entries for individual products (not groups) that I would like to remove from the report.

Do you know why they appeared (values ​​not by group)? Because when we added the field " Average Calorie Content"in the report settings, in the second step we selected the entire report and this new field ended up in the element " Detailed records".

Let's fix the error. To do this, go back to the tab " Settings", select " Detailed entries" first from above (step 2) and then " Detailed entries"from below (step 3), go to the bookmark" Selected fields" and we will see in its right column the element " Auto".

Element " Auto" - this is not one field. These are several fields that fall here automatically based on higher-level settings.

To see what these fields are, click on the element " Auto" right button and select " Expand":

Element " Auto" expanded into the following fields:

And here is our field" Average Calorie Content"which came here from the point " Report" when we dragged him there. Just let's remove check the box next to this field to remove its output.

One of the most convenient and unique development tools in 1C is the data composition system (DCS). Few in any information systems developers can create reports without writing any code. This mechanism was developed to simplify and speed up the development of reporting forms and give more opportunities to users in working with the output data. The latter is very highly valued by advanced users, who thanks to this can independently customize the report to their own needs, without waiting for the actions of developers.

Creating a report in 1C via SKD

The process of developing a report using ACS can be divided into the following stages:

1. Creating a request. You can write a request manually or do without program code using a convenient interface;
2. Setting up the report. Select fields, totals, groupings, parameters, report design;
3. After this, all we have to do is connect the resulting report to the configuration in any available way.

Despite the ability of users to customize reports on the access control system, they must be created through the configurator.

Let's look at an example of creating an external report on an access control system:

Now we go to 1C, open our report to make sure the actions taken are correct. All data is reflected, groupings can be collapsed and expanded. As you can see, the access control system allows you to receive full-fledged reports without writing code, with the exception of non-standard requirements. Considering that most reports have a similar structure, knowledge of the access control system will significantly reduce the time for developing these objects.

This mechanism became very popular due to the support wide possibilities when generating a report. Moreover, not only developers, but also ordinary users can use them.

ACS capabilities

There are situations when we have made a report, and then a user comes and asks to make a small modification. For example, instead of product names, display article numbers. SKD allows users to make such modifications independently using the “More” - “Change option...” button.

The window that opens is similar to the settings window in the report in the configurator, and it also has similar functions. To solve the task, the user must go to the “Fields” tab and change the “Nomenclature” field. This edit field opens by double-clicking and the “Select...” button becomes available.

The window that opens gives us the opportunity to select any value that will appear in the “Nomenclature” field. Some fields have a plus sign on the left - the developer has placed links in these fields, which means we can see their details. We open “Nomenclature” and see the article that we need. Select it and select it.

The window for changing report options contains many useful features data composition systems. For example, the user can independently change the order of groupings, add selection, or apply conditional design. We complete the editing and generate a report - as you can see, the entire product range is now displayed in the form of articles.

The SKD 1C:Enterprise 8.3 mechanism also has expanded functionality for developers. When developing the report, we used only 2 tabs - “Data sets” and “Settings”, but there are many more of them in the ACS. To use all the functions of the data composition system, you need to understand what each of the tabs is for:

1. Data sets – all queries involved in generating the report are listed here;
2. Data set connections – used to build connections between different queries from the first tab;
3. Calculated fields – a list of added fields not from the query. Most often used in cases where, based on the values ​​of several fields, you need to get 1 value from the request;
4. Resources. In 1C, this is the name for the fields for which you need to know the results. Resources support various arithmetic operations - sum, quantity, maximum and others;
5. Options. They are used if to generate a report it is necessary for the user to enter certain data - date, divisions or nomenclature, for example;
6. Layouts. Designed for cases where users want to see a uniquely designed report. You can create a separate place for signatures or a new top part of the report - all this can be done here;
7. Nested diagrams. They are needed when your report must contain data from other reports;
8. Settings. The section declares the fields to be displayed, groupings, and configures the appearance of the report.

1. Calculate (Eval)- is intended to evaluate an expression in the context of some grouping. This function is used for compatibility with previous versions platforms. It is recommended to use the CalculateExpression function instead.

Syntax:
Calculate(Expression, Grouping, CalculationType)

Parameters:

• Expression(Line). Contains a calculated expression;
• Grouping(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 the grouping name, the calculation will be performed in the context of the current grouping. If the GrandTotal string is used as the group 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 the same name.
  For example:
  Sum(Sales.SumTurnover) / Calculate("Sum(Sales.SumTurnover)", "Total").
  In this example, the result will be the ratio of the amount for the "Sales.AmountTurnover" field of the grouping record to the amount of the same field in the entire layout.
• Calculation type(Line). If this parameter is set to "TotalTotal", the expression will be calculated for all grouping records. If the value of the parameter is "Grouping", the values ​​will be calculated for the current grouping group record.

Syntax:
CalculateExpression(Expression, Grouping, CalculationType, Start, End, Sort, HierarchicalSort, ProcessingIdenticalOrderValues)

Parameters:

• Expression(Line). Contains a calculated expression;
• Grouping(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 the grouping name, the calculation will be performed in the context of the current grouping. If the GrandTotal string is used as the group 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;
• Calculation type(Line). If this parameter is set to "TotalTotal", the expression will be calculated for all grouping records. If the value of the parameter is "Grouping", the values ​​will be calculated for the current grouping group record. If the parameter is set to "Non-Resource Grouping", then when calculating the function for a group record by resource, the expression will be evaluated for the first group record of the original grouping. When evaluating the CalculateExpression function with the value "GroupingNonResource" for group records that are not groupings by resource, the function is evaluated in the same way as it would be evaluated with the value of the "Grouping" parameter. The data composition layout builder, when generating a data composition layout when outputting a field - a resource by which grouping is performed, to the layout, outputs to the layout an expression calculated using the CalculateExpression function with the specified "GroupingNon-Resource" parameter. For other resources grouped by resource, normal resource expressions are returned. If the parameter is set to "Hierarchy", then the expression must be evaluated for the parent hierarchical record, if there is one, and for the entire grouping, if there is no parent hierarchical record. The layout builder, when generating an expression for the % in Hierarchy Group field, generates an expression that contains the relationship of the resource expression to the CalculateExpression function for the resource expression being calculated for the current grouping with calculation type Hierarchy.
• Start. Indicates from which record the fragment should begin, in which aggregate expression functions should be calculated, and from which record to obtain field values ​​outside of aggregate functions. A string containing one of:
  • "First" It is necessary to obtain the first grouping record. After the word in brackets, 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 greater than zero. For example, First(3) – receiving 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" You need to get the last grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset from the end of the grouping. The resulting value must be an integer greater than zero. For example, Last(3) – receiving the third record from the end of the group. If the last 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 Last(4), then it is considered that there are no records.
  • "Previous" You need to get the previous grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset back from the current grouping record. For example, Previous(2) – getting the previous from the previous record. If the previous record is outside the grouping (for example, the second grouping record requires getting Previous(3)), then the first grouping record is obtained. When receiving the previous record for the grouping total, the first record is obtained.
  • "Next" You need to get the next grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset forward from the current grouping entry. For example, Next(2) – getting the next from the next record. If the next record goes beyond the grouping, then it is considered that there are no records. For example, if there are 3 entries and the third entry receives Next, then it is considered that there are no entries. When the next record is received for the grouping total, it is considered that there is no record.
  • "Current". You need to get the current record. When retrieving for a grouping total, the first record is obtained.
  • "BoundaryValue". The need to obtain a record by the specified value. After the word LimitingValue in parentheses, you need to indicate the expression with the value of which you want to start the fragment, the first ordering field. The first record whose ordering field value is greater than or equal to the specified value will be returned as the record. For example, if the Period field is used as the ordering field, and it has the values ​​01/01/2010, 02/01/2010, 03/01/2010, and you want to get the LimitingValue(DateTime(2010, 1, 15)), then a record with the date 02/01 will be obtained. 2010.
• End. Indicates to which record the fragment should be continued, in which the aggregate expression should be calculated. A string containing one of:
  • "First"
  • "Last"
  • "Previous"
  • "Next"
  • "Current".
  • "BoundaryValue".
• Sorting. A string that lists the expressions, separated by commas, in the direction of which the sequence should be ordered. If not specified, then the ordering is performed in the same way as for the grouping for which the expression is evaluated. After each expression you can specify keyword Asc, for ordering in ascending order, Descending, for ordering in descending order, Auto Order, for ordering reference fields by the fields by which you want to order the object being referenced. The word Auto Order can be used with both the word Ascending and the word Descending.
• HierarchicalSorting. Similar to sorting. Used to organize hierarchical records. If not specified, the layout compositor generates the ordering according to the ordering specified in the Sort parameter.
• Processing Same Values ​​of Order. A string containing one of:
  • "Together" means that a sequence of ordered records is used to determine the previous and next records;
  • "Separately" means that the previous and next records are determined based on the values ​​of the ordering expressions;
  For example, if the resulting sequence is ordered by date:
  1. January 01, 2001 Ivanov M. 10
  2. January 02, 2001 Petrov S. 20
  3. January 02, 2001 Sidorov R. 30
  4. January 03, 2001 Petrov S. 40
  When using processing of identical values ​​of the order "Separately", the previous one for record 3 will be record 2, and when using "Together" - record 1. And the fragment for the current record for record 2 for "Separately" will be record 2, and for "Together" - records 2 and 3. Thus, the total for the current record for “Separately” will be 20, and for “Together” - 50. When “Together” is specified in the Start and End parameters, you cannot specify an offset for the positions “First”, “Last”, "Previous", "Next". The default value is "Separate".

This example calculates the value of the current hierarchy:
Choice
When Level() > 0
Then EvaluateExpression("Reference", "Hierarchy")
Otherwise Null
End

Notes:
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 that grouping. For example, in selecting the Nomenclature grouping, you cannot use the expression CalculateExpression("Sum(SumTurnover)", "TotalTotal") > 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 "GrossTotal"), it is assumed that there are no records for calculating detailed data and calculating aggregate functions. When generating an expression for the CalculateExpression function, the layout compositor, if the ordering expression contains fields that cannot be used in grouping, replaces the CalculateExpression function with NULL.

3. Evaluate Expression With Group Array (EvalExpression With Group Array) - the function returns an array, each element of which contains the result of calculating an expression for grouping by the specified field.

Syntax:
CalculateExpressionWithGroupArray (Expression, GroupFieldExpressions, SelectRecords, SelectGroups)

Parameters:

• Expression(String) - the expression to be evaluated. For example, "Amount(AmountTurnover)";
• FieldExpressionsGroups
• Selection of Records
• Selection of Groups- selection applied to group records. For example: "Amount(AmountTurnover) > &Parameter1".

The layout builder, when generating expressions to display a custom field whose expression contains only the CalculateArrayWithGroup function, generates the output expression in such a way that the display data and the data are ordered.
For example, for a custom field with the expression:
CalculateExpressionWithGroupArray("Amount(AmountTurnover)", "Counterparty")
The layout builder will generate the following expression for output:
ConnectRows(Array(Order(CalculateExpressionWithGroupingValueTable("View(Sum(DataSet.AmountTurnover)), Amount(DataSet.AmountTurnover)", "DataSet.Account"), "2")))

4. Calculate Expression With GroupValueTable (EvalExpressionWithGroupValueTable) - the function returns a table of values, each element of which contains the result of calculating an expression for grouping by the specified field.

Syntax:
CalculateExpressionWithGroupValueTable(Expression, GroupFieldExpressions, SelectRecords, SelectGroups)

Parameters:

• Expression(String) - the expression to be evaluated. A line can contain multiple expressions separated by commas. After each expression there may be an optional keyword AS and the name of the value table column. For example: "Counterparty, Amount (AmountTurnover) As Sales Volume."
• FieldExpressionsGroups- expressions of grouping fields, separated by commas. For example, "Counterparty, Party";
• Selection of Records- an expression applied to detail records. For example, "Deletion Flag = False." If this parameter uses an aggregate function, an error will occur when composing the data;
• Selection of Groups- selection applied to group records. For example: "Amount(AmountTurnover) > &Parameter1".

The result of this function will be a table of values ​​with columns Counterparty and Sales Volume, which will contain counterparties with their sales volumes.
The layout builder, when generating a layout, converts function parameters into terms of data layout layout fields. For example, the Account field will be converted to DataSet.Account.
For example, a custom field with the expression:
CalculateExpressionWithGroupValueTable("Account, Amount(AmountTurnover)", "Account")
The layout builder will generate the following expression for output:
ConnectRows(GetPart(Order(CalculateExpressionWithGroupingValueTable("DataSet.Account, DataSet.AccountRepresentation, Sum(DataSet.AmountTurnover), View(DataSet.AmountTurnover), DataSet.OrderingField", "DataSet.Account"), "5, 1, 3" ), "2, 4"))

5. Level - the function is designed to obtain the current recording level.

Syntax:
Level()

Example:
Level()

6. SequenceNumber - get the next serial number.

Syntax:
NumberByOrder()

Example:
NumberByOrder()

7. SequenceNumberInGrouping - returns the next sequence number in the current grouping.

Example:
NumberByOrderInGroup()

8. Format - get a formatted string of the passed value.

Syntax:
Format(Value, FormatString)

Parameters:

• Meaning- the expression that needs to be formatted;
• FormatString- the format string is set in accordance with the 1C:Enterprise format string.

9. BeginOfPeriod

Syntax:
StartPeriod(Date, PeriodType)

Parameters:

• Date(Date). Specified date;
• Period Type

10. EndOfPeriod - the function is designed to select a specific date from a given date.

Syntax:
EndPeriod(Date, PeriodType)

Parameters:

• Date(Date). Specified date;
• Period Type(Line). Contains one of the following values: Minute; Hour; Day; Week; Month; Quarter; Year; Decade; Half year.

11. AddKDate (DateAdd) - the function is designed to add a certain value to the date.

Syntax:
AddToDate(Expression, IncrementType, Magnitude)

Parameters:

• Expression(Date). Original date;
• TypeMagnification(Line). Contains one of the following values: Minute; Hour; Day; Week; Month; Quarter; Year; Decade; Half year.
• Magnitude(Number). By how much the date needs to be increased, the fractional part is ignored.

12. DateDifference - the function is designed to get the difference between two dates.

Syntax:
DifferenceDate(Expression1, Expression2, DifferenceType)

Parameters:

• Expression1(Date). Subtracted date;
• Expression2(Date). Original date;
• TypeDifference(Line). Contains one of the values: Second; Minute; Hour; Day; Month; Quarter; Year.

13. Substring - this function is intended to extract a substring from a string.

Syntax:
Substring(String, Position, Length)

Parameters:

• Line(Line). The string from which the substring is extracted;
• Position(Number). The position of the character from which the substring to be extracted from the string begins;
• Length(Number). Length of the allocated substring.

14. StringLength - the function is designed to determine the length of a string.

Syntax:
StringLength(String)

Parameter :

• Line(Line). A string whose length is specified.

15. Year- this function is designed to extract the year from a Date type value.

Syntax:
Year(Date)

Parameter :

• Date(Date). The date by which the year is determined.

16. Quarter - this function is designed to extract the quarter number from a Date type value. The quarter number normally ranges from 1 to 4.

Syntax:
Quarter(Date)

Parameter :

• Date(Date). The date by which the quarter is determined

17. Month - this function is designed to extract the month number from a Date type value. The month number normally ranges from 1 to 12.

Syntax:
Month(Date)

Parameter :

• Date(Date). The date by which the month is determined.

18. Day of the Year (DayOfYear) - this function is designed to obtain the day of the year from a Date type value. The day of the year normally ranges from 1 to 365 (366).

Syntax:
Day of the Year(Date)

Parameter :

• Date(Date). The date by which the day of the year is determined.

19. Day- this function is designed to obtain the day of the month from a Date type value. The day of the month normally ranges from 1 to 31.

Syntax:
Day(Date)

Parameter :

• Date(Date). The date by which the day of the month is determined.

20. Week - this function is designed to obtain the week number of the year from a Date type value. The weeks of the year are numbered starting from 1.

Syntax:
Week(Date)

Parameter :

• Date(Date). The date by which week numbers are determined.

21. WeekDay - this function is designed to obtain the day of the week from a Date type value. The normal day of the week ranges from 1 (Monday) to 7 (Sunday).

Syntax:
Day of the Week(Date)

Parameter :

• Date(Date). The date by which the day of the week is determined.

22. Hour- this function is designed to obtain the hour of day from a Date type value. The hour of the day ranges from 0 to 23.

Syntax:
Hour(Date)

Parameter :

• Date(Date). The date by which the hour of the day is determined.

23. Minute - this function is designed to obtain the minute of the hour from a Date type value. The minute of the hour ranges from 0 to 59.

Syntax:
Minute(Date)

Parameter :

• Date(Date). The date by which the minute of the hour is determined.

24. Second - this function is designed to obtain the second of a minute from a Date type value. The second of a minute ranges from 0 to 59.

Syntax:
Second(Date)

Parameter :

• Date(Date). The date by which the seconds of the minute are determined.

25. Cast - this function is designed to extract a type from an expression that may contain a compound type. If the expression contains a type other than the required type, NULL will be returned.

Syntax:
Express(Expression, TypeIndication)

Parameters:

• Expression- expression to be converted;
• Type Indication(Line). Contains a type string. For example, "Number", "String", etc. In addition to primitive types, this line may contain the name of the table. In this case, an attempt will be made to express a reference to the specified table.

26. IsNull (IsNull) - 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.

Syntax:
IsNull(Expression1, Expression2)

Parameters:

• Expression1- value to be checked;
• Expression2- return value if Expression1 is NULL.

27.ACos- calculates arc cosine in radians.

Syntax:
ACos(Expression)

Parameter :

• Expression(Number). The cosine value (in the range -1 ... 1) by which the angle is determined.

Syntax:
ASin(Expression)

Parameter :

• Expression(Number). The sine value (in the range -1 ... 1) by which the angle is determined.

Syntax:
ATan(Expression)

Parameter :

• Expression(Number). The tangent value by which the angle is determined.

Syntax:
Cos(Expression)

Parameter :

• Expression

Syntax:
Exp(Expression)

Parameter :

• Expression(Number). The meaning of the degree.

Syntax:
Log(Expression)

Parameter :

• Expression

Syntax:
Log10(Expression)

Parameter :

• Expression(Number). The original number is greater than 0.

Syntax:
Pow(Base, Indicator)

Parameters:

• Base(Number). The basis of the operation of exponentiation.
• Indicator(Number). Exponent.

Syntax:
Sin(Expression)

Parameter :

• Expression(Number). Specified in radians.

Syntax:
Sqrt(Expression)

Parameter :

• Expression(Number). Non-negative number.

Syntax:
Tan(Expression)

Parameter :

• Expression(Number). The sine value by which the angle is determined.

Syntax:
Env(Expression, Bit depth)

Parameters:

• Expression(Number). Original number;
• Bit depth(Number). The number of decimal places to round to.

Syntax:
Object(Expression)

Parameter :

• Expression(Number). Fractional number.

A data composition engine expression may contain calls to functions of global common configuration modules. No additional syntax is required to call such functions.

Example:
AbbreviatedName(Documents.Link, Documents.Date, Documents.Number)

In this example, the "AbbreviatedName" function will be called from the general configuration module.
Note that the use of common module functions is only permitted if the appropriate data composition processor parameter is specified.
Additionally, functions of common modules cannot be used in custom field expressions.

41. Resentation - this function returns a string representation of the passed value of a non-primitive type. For values ​​of a primitive type, returns the value itself.

<Пустое значение>".

Example:
Presentation(Counterparty)

42. String - this function converts the passed value into a string.

If an array or a table of values ​​is used as a parameter, the function returns a string containing a string representation of all the elements of the array, separated by the characters "; ". If any element has an empty string representation, then the string "<Пустое значение>".

Example:
Row(SalesDate)

43. ValueIsFilled

For NULL values, Undefined always returns False.
For Boolean values, it always returns True.
For other types, returns True if the value differs from the default value for of this type.

Example:
ValueFilled(DeliveryDate)

44. LevelInGroup - this function gets the current recording level relative to the grouping.

Can be used to obtain the nesting level of a record in a hierarchical grouping.

Example:
LevelInGroup()

45. ValueType

Syntax:
ValueType(Expression)

Parameter :

• Expression(Line). String value type.

In light of the upcoming release of 8.2.14, I will try to describe some new functions of the data composition system.

Open the data layout diagram, preferably in an external report, to make editing easier.

We add a dataset of the query type and write, either manually or using the query designer, a simple query:

1. Set up a request in the access control system.

2. Set up calculated fields in the access control system

3. Configure the data layout on the settings tab

4. Launch 1C Enterprise 8.2.14. Open the report. We form, we receive.

Description of the new functions themselves:

1. CurrentDate()

Returns the system date. When composing a layout layout, in all expressions that are present in the layout, the CurrentDate() function is replaced with the value of the current date.

2. COMPUTEEXPRESSION()

Syntax:

CalculateExpression(,)

Description:

The function is designed to evaluate 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 that grouping. For example, in selecting the Nomenclature grouping, you cannot use the expression CalculateExpression("Sum(SumTurnover)", "TotalTotal") > 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 GrandTotal), it is assumed that there are no records for calculating detailed data and calculating aggregate functions.

Layout linker when generating a function expression CalculateExpression, if the ordering expression contains fields that cannot be used in grouping, replaces the function CalculateExpression on NULL.

Options

Type: Line. The expression to be evaluated.

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 the grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal string is used as the group 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 the same name.

For example:

Sum(Sales.SumTurnover)/Calculate("Sum(Sales.SumTurnover)", "Total")

In this example, the result will be the ratio of the sum by field Sales.AmountTurnover grouping records to the sum of the same field in the entire layout;

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

· Grand total— the expression will be calculated 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 group grouping record.

· GroupingNonResource— 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 a function CalculateExpression() with meaning GroupingNonResource for group records that are not grouped 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 data composition layout builder, when generating a data composition layout when outputting a resource field by which grouping is performed to the layout, places an expression in the layout that is calculated using the function CalculateExpression() , indicating the parameter GroupingNonResource. For other resources, the usual resource expressions are placed in the resource grouping.

Type: Line. Indicates from which record the fragment should begin, in which aggregate expression functions should be calculated, and from which record to obtain field values ​​outside of aggregate functions. The value can be one of the following:

· First

· Last (Last)

· Previous

· Next (Next)

· Current

· LimitingValue(BoundaryValue) LimitingValue

Type: Line. Indicates to which record the fragment should be continued, in which the aggregate functions of the expression should be calculated. The value can be one of the following:

· First. It is necessary to obtain the first grouping record. After the word in brackets, 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 greater than zero. For example, First(3) - receiving 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 last grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset from the end of the grouping. The resulting value must be an integer greater than zero. For example, Last(3) - receiving the third record from the end of the group.

If the last 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 Last(4), then it is considered that there are no records.

· Previous. You need to get the previous grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset back from the current grouping record. For example, Previous(2) - getting the previous from the previous record.

If the previous record goes beyond the grouping (for example, for the second grouping record you need to get Previous(3)), then the first grouping record is obtained.

When retrieving the previous record for a grouping total, it is considered that the first record is obtained.

· Next (Next). You need to get the next grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset forward from the current grouping entry. For example, Next(2) - getting next from the next record.

If the next record goes beyond the grouping, then it is considered that there are no records. For example, if there are 3 entries and the third entry receives Next() , then it is considered that there are no entries.

When the next record is received for the grouping total, it is considered that there is no record.

· Current. You need to get the current record.

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

· LimitingValue(BoundaryValue). The need to obtain a record by the specified value. After the word LimitingValue in brackets you need to indicate the expression with the value of which you want to start the fragment, the first ordering field.

The first record whose ordering field value is greater than or equal to the specified value will be returned as the record. For example, if the Period field is used as the ordering field, and it has the values ​​01/01/2010, 02/01/2010, 03/01/2010, and you want to get LimitingValue(DateTime(2010, 1, 15)), then a record with the date 02/01/2010 will be received.

Type: Line. Lists expressions, separated by commas, that describe the ordering rules. If not specified, then 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(for ordering in ascending order), Descending(for ordering in descending order) and Auto-order(to order reference fields by the fields by which you want to order the referenced object). Word Auto-order can be used as with the word Age, so with the word Descending.

Type: Line. Same as parameter Sorting. Used to organize hierarchical records. If not specified, the layout builder generates the ordering according to the ordering specified in the parameter Sorting.

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 determine the previous and next records. Default value.

· Together indicates that the previous and next records are determined based on the values ​​of the ordering expressions.

For example, if the resulting sequence is ordered by date:

Separately, That:

§ the previous entry to entry 3 will be entry 2.

Current, Current(accordingly, parameters Start And 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, That:

§ the previous entry to entry 3 will be entry 1.

§ if the calculation fragment is defined as Current, Current(accordingly, parameters Start And End), then for record 2 this fragment will consist of records 2 and 3. Expression CalculateExpression("Sum(Value)", Current, Current) will be equal to 50.

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

CalculateExpression("Sum(SumTurnover)", "First", "Current")

If you want to get the grouping value in the previous line, you can use the following expression:

CalculateExpression("Rate", "Previous")

CalculateExpressionWithGroupArray(,) -

The function returns an array, each element of which contains the result of evaluating an expression for grouping by the specified field.

CalculateExpressionWithGroupValueTable(,) -

The function returns a table of values, each row of which contains the result of evaluating expressions for grouping by the specified field

ValueFilled() - Returns True if the value is other than the default value of this type, other than NULL, other than an empty reference, other than Undefined. Boolean values ​​are checked for NULL values. Strings are checked for the absence of non-whitespace characters

Format(, ) - Receive a 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 a string. Parameter is a string 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 ​​is passed as a parameter, the function returns a string containing string representations of all rows of the table of values, with the cell representations of each row separated by ";" characters, and the rows by a newline character. If an element's string representation is empty, then a string is displayed instead of its representation.