Data Composition System Expression Language (1Cv8). Functions of the expression language of the data composition system 1s SKD calculated fields of functions

CalculateExpression is a rather difficult ACS function to understand, and examples of application in the reference information are quite scarce. This article discusses examples that are sure to be useful to every developer:

1. cumulative total in grouping;
2. cumulative total in a cross-tab;
3. getting the previous value;
4. PM output in one line.

1. Obtaining an indicator on an accrual basis

Let's get the quantity of goods as a cumulative total 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:
CalculateExpression("Sum(QuantityTurnover)", "First", "Current")
which will sum the number of products from the first record to the current one (see Figure 2).

If the cumulative total quantity of an item needs to be obtained at the level of detailed records, then we set the CalculateExpression function for the calculated field on the “Calculated fields” tab (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 remote control level - grouping of detailed records.

Figure 4. Report groupings with cumulative totals

2. Getting the indicator value from the previous row

Let's get the exchange 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):
CalculateExpression("Rate", "Previous", "Previous")
which will take the previous value of the exchange rate for the current row, the last parameter of the function limits the receipt of data.
Since we are working at the level of detailed records, we immediately go to the “Settings” tab and create a grouping - detailed records.

3. Obtaining an indicator as a cumulative total in a cross-tab

Let's get the quantity of goods on an accrual basis by period. To do this, create a calculated field (see Figure 1). On the "Resources" tab, we specify the following expression for the calculated field (see Figure 6):
CalculateExpression("Sum(QuantityTurnover)", "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 with grouping by item in a row and grouping by period in a column (see Figure 7).

4. Outputting tabular data in one line

Methods for displaying tabular data in one line, including the method using the CalculateExpression function, are discussed in the article

Data layout diagram (1C SKD)- a convenient designer for creating complex reports in 1C:Enterprise software products that contribute to the development and tracking of production automation, allowing them to be made as flexible and beautiful as possible in a minimum of time. An additional advantage of the Data Composition Scheme (1C SKD) is the automatic generation of a controlled report form, and with further development of this area, it is an important factor when choosing a report development method. But due to the complexity of the structure of the Data Composition Scheme (1C SKD) and the huge number of settings, it often leads to longer development of the report than through the “output form designer”. Therefore, a 1C programmer needs to understand all the intricacies of the Data Composition Scheme (1C DCS) in order to further speed up the development time for generating reports.

Let's look at the first three tabs of the Data Composition Scheme (1C SKD) - data set, data set connections and calculated fields.

Data set in 1C SKD

The data set includes the ability to create three objects - a query, an object and a union, let's take a closer look at each of them:

This is a regular query that is generated using the Query Builder button. If the Autofill flag is set, then all selected details will automatically be included in the fields of the data set. It is also possible to customize the filling of fields in the request on the Data Composition tab, where there are three tabs:

Tables, here the tables are selected that will participate in the generation of the report, usually the default data is selected, since on the Tables and Fields tab we have already selected the documents, directories, registers we need...

Fields, here we select those objects that should be included in the report, the children flag indicates whether there will be accessible child elements for the object or not, it is logical that for string, numeric and similar data it will not be possible to set the flag to True.

Conditions, here we select those objects that can be used under conditions in the access control system.

Some of the work is done in the data composition scheme, and some of it is done programmatically; let’s look at a simple example:

First, we will create a layout diagram for the data layout of the document and call it SKD (for example: 1C SKD), in it we create a data set object, then we fill in the fields, for example, we have a document with a tabular part of goods with details - nomenclature, quantity and price.

Let's add three fields and fill in each column with the name of the details, the remaining columns will be filled in automatically:

Let's create a button on the document form and describe the mechanism of operation in controlled forms:

&OnClient

Procedure Print()

OurReport = PrintOnServer(); //call the function on the server

OurReport.Show(); //display the generated report

End of Procedure

&On server

Function PrintOnServer()

DocumentObject = FormAttributeValue(“Object”);

//we place the tabular part Products in a structure with the name ProductsSKD in the same way as we indicated in the SKD itself the name of the object containing the data

DataSet = new Structure;

DataSet.Insert(“ProductsSKD”, DocumentObject.Products);

//we get our layout and set the default settings so that all report output settings are taken from our layout

OurLayout = DocumentObject.GetLayout(“SKD”);

Settings = OurLayout.DefaultSettings;

//create a data layout layout with our settings

LayoutLinker = newDataLayoutLayoutLinker;

LayoutLayout = LayoutComposer.Execute(OurLayout, Settings);

//perform data composition with our data set

DataCompositionProcessor = newDataCompositionProcessor;

DataCompositionProcessor.Initialize(LayoutLayout, DataSet);

//We create a spreadsheet document and display our report in it

ReportDocument = New TabularDocument;

OutputProcessor = New OutputProcessorDataCompositionResultInTabularDocument;

OutputProcessor.SetDocument(ReportDocument);

OutputProcessor.Output(DataCompositionProcessor);

Return DocumentReport;

EndFunction

If you wish, you can get areas of any other layout and also display them in this report, for example, we have a standard layout for generating a payment order and the header is very well created in it, then, so as not to do unnecessary work, we’ll just get the layout first, display the header, then we will generate and display our report on the access control system.

ABOUT unification

We can place our queries and objects in it, but unlike a connection, it simply adds tables to each other, that is, if we connect two identical tables, we will end up with one, and when combined, it will double, let’s look at a simple example :

We have tables:

Upon communication we will receive:

And when combined:

Let's now consider filling out columns in data sets (we will skip some, since they are related to other tabs; we will return to them in future articles):

- field, indicate the general name of the attribute;

­­- path, indicate the name of the details by which we will contact it in the access control system, for example, in Calculated fields;

- title, indicate the name of the attribute that will be displayed in the report;

- field limitation, indicate the availability of this requisite;

- restriction of details, we indicate the availability of child elements, it is important that if the availability of details is indicated, then the field itself will be available, perhaps this mechanics will be changed in future releases;

- expression by which the field representation is calculated, it’s convenient to use when we need to change the output of details a little, for example, we need after the name nomenclature was displayed stock, where it is located, then fill in the following: Item + “is in the warehouse” + Warehouse. I repeat that access to the details is carried out through the name indicated in the column path;

- expression ordering, a convenient mechanism for setting up report ordering, where the condition can be set manually, similar to the previous point, but as practice shows, this mechanism often does not work as we would like, and I advise you to use standard sorting;

- value type, indicates the type of value of the attribute; this must be filled in if you use the following field;

- available values, works only when full value type, open the form and in the column Meaning we indicate the element that needs to be changed, according to the type, it can be predefined objects or numeric, for example, details have simple values, in presentation We indicate what we need to change to, an example of a Boolean type:

- decor– standard field format settings, similar to settings in managed forms, allow you to more accurately and beautifully customize the output of certain details.

Data set connections in 1C SKD

Here it is installed only left join, on a principle similar to connections in requests, in source of communication specify the main table for the connection, in receiver additional. IN expression source And expression receiver We indicate the details by which communication will take place. We'll look at the remaining columns in more detail when we look at the tab. Options. If there is no additional connection with parameters, then it is recommended to do the connection in the request, this will speed up the report.

Proper use of a data composition scheme (DCS) allows you to:

• significantly reduce the time required to develop a report;
• eliminate 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 scheme’s capabilities, since not all of its settings are obvious and intuitive. In particular, many people know that in 1C SKD there are calculated fields, however, they do not fully understand the scope of their use and methods of working with them.

What is a calculated field

In most cases, the data source in the layout diagram is a query. 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 access control system is something more than just displaying the result of a query, and this is clearly visible from the diagram creation form (Fig. 1).

Calculated fields allow you to perform certain actions with the generated data set:

• Output an array of data received by a request into a specific cell, combining several lines into one;
• Access the export functions of the general module;
• Execute the various expressions available for the layout language and use the special EvaluateExpression functions.

Let's go through this list.

Array of values ​​in one cell

Let's simulate a situation where it is necessary to receive all receipt document numbers for a counterparty in a separate cell:

Thus, we have created an additional calculation field in our scheme;

As you can see from the example above, there are no difficulties in adding and processing calculated fields. We used two functions: Array() and ConnectRows().

A few words about the latter. In addition to the first parameter indicating the identifier of the array, values ​​or value, two more can be set in it:

1. Element Separator – indicates which character will separate one array element or one row of a table of values ​​from another (in our case, we omitted this parameter and a line break was assigned by default);
2. Column Separator – a character used to separate columns of a table of values ​​(semicolon is used by default).

Accessing the export functions of a common module

Functions of a common module can act as a data source for filling a calculated field.

A few important points:

• The function must be exportable;
• If a function is located in a common module with the “Global” attribute set, it is called directly by name, otherwise the function must be called according to the “Shared module name” scheme. "Name of the function to be called."

As an example of use, we will take the same request for receipt documents and display it in a separate column. We will not describe the request itself; let’s move directly to the calculated fields:

Thus, we see that almost any data processor can be initialized from the access control system, which significantly expands the possibilities of using the scheme.

Layout language expressions

Quite often in a developer’s work a situation arises when it is necessary to display the result of division in the ACS field:

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

To avoid problems, in these cases it is advisable to enter a test for division by 0 into the calculated field.

This can be done using the construction “Choice When….Then… Otherwise… End”.

At the end, a few words about the fairly new function CalculateExpression(). With its help, in particular, you can calculate deviations in cost between the current and previous lines, cumulative balance, etc.

Let's say you can get the Document Sum from the previous line of our request by specifying the value Calculate Expression ("Document Sum", "Previous Sum") in the "Expression" field.

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

Syntax:
Calculate(Expression, Grouping, CalculationType)

Options :

• 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 group 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.

2. Evaluate Expression (EvalExpression) - is intended 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.

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

Options :

• 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 containing 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 record. 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 the keyword Ascending, for ordering in ascending order, Descending, for ordering in descending order, Auto-Ordering, for ordering the 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".

Example:
Obtaining the ratio of the amount for the "Sales.AmountTurnover" field of a grouping record to the amount of the same field in the entire layout:
Sum(Sales.SumTurnover) / CalculateExpression("Sum(Sales.SumTurnover)", "Total").

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)

Options :

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

Example:
Maximum(CalculateExpressionWithGroupArray("Amount(AmountTurnover)", "Counterparty"));

The layout builder, when generating expressions for the display of a custom field whose expression contains only the CalculateArrayWithGroup function, generates the display expression so that the data displayed by the views 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, GroupField Expressions, Records Selection, Group Selection)

Options :

• 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 Groupings- selection applied to group records. For example: "Amount(AmountTurnover) > &Parameter1".

Example:
Calculate Expression With Grouping Table of Values ​​("Account AS Counterparty, Amount (Amount Turnover) AS Sales Volume", "Account")

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 compositor, when generating a layout, converts function parameters into terms of data composition 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(CalculateExpressionWithGroupValueTable("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)

Options :

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

Example:
Format(Invoices.Doc Amount, "NPV=2")

9. BeginOfPeriod

Syntax:
StartPeriod(Date, PeriodType)

Options :

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

Example:
StartPeriod(DateTime(2002, 10, 12, 10, 15, 34), "Month")
Result: 10/01/2002 0:00:00

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

Syntax:
EndPeriod(Date, PeriodType)

Options :

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

Example:
EndPeriod(DateTime(2002, 10, 12, 10, 15, 34), "Week")
Result: 10/13/2002 23:59:59

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

Syntax:
AddToDate(Expression, IncrementType, Magnitude)

Options :

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

Example:
AddToDate(DateTime(2002, 10, 12, 10, 15, 34), "Month", 1)
Result: 11/12/2002 10:15:34

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

Syntax:
DifferenceDate(Expression1, Expression2, DifferenceType)

Options :

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

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

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

Syntax:
Substring(String, Position, Length)

Options :

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

Example:
SUBSTRING(Accounts.Address, 1, 4)

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.

Example:
Line(Counterparties.Address)

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

Syntax:
Year(Date)

Parameter :

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

Example:
YEAR(Expense.Date)

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 of). The date by which the quarter is determined

Example:
QUARTER(Expense.Date)

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 of). The date by which the month is determined.

Example:
MONTH(Expense.Date)

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 of). The date by which the day of the year is determined.

Example:
DAYYEAR(ExpenseAccount.Date)

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 of). The date by which the day of the month is determined.

Example:
DAY(Expense.Date)

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 of). The date by which week numbers are determined.

Example:
WEEK(Expense.Date)

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 of). The date by which the day of the week is determined.

Example:
DAY OF THE WEEK (Expense Invoice Date)

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 of). The date by which the hour of the day is determined.

Example:
HOUR(Expense.Date)

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 of). The date by which the minute of the hour is determined.

Example:
MINUTE(Expense.Date)

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 of). The date by which the seconds of the minute are determined.

Example:
SECOND(Expense.Date)

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)

Options :

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

Example:
Express(Data.Props1, "Number(10,3)")

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)

Options :

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

Example:
YesNULL(Amount(Sales.AmountTurnover), 0)

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.

28.ASin- calculates the arcsine in radians.

Syntax:
ASin(Expression)

Parameter :

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

29.ATan- calculates the arctangent in radians.

Syntax:
ATan(Expression)

Parameter :

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

30.Cos- calculates cosine.

Syntax:
Cos(Expression)

Parameter :

• Expression

31. Exp- raising the number e to a power.

Syntax:
Exp(Expression)

Parameter :

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

32.Log- calculates the natural logarithm.

Syntax:
Log(Expression)

Parameter :

• Expression

33.Log10- calculates the logarithm of X to base 10.

Syntax:
Log10(Expression)

Parameter :

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

34. Pow- exponentiation.

Syntax:
Pow(Base, Indicator)

Options :

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

35. Sin- calculates the sine.

Syntax:
Sin(Expression)

Parameter :

• Expression(Number). Specified in radians.

36. Sqrt- calculates the square root.

Syntax:
Sqrt(Expression)

Parameter :

• Expression(Number). Non-negative number.

37. Tan- calculates the tangent.

Syntax:
Tan(Expression)

Parameter :

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

38. Round- rounds the original number to the required bit depth. The rounding mode is standard (1.5 as 2).

Syntax:
Env(Expression, Bit depth)

Options :

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

39. Int- cuts off the fractional part of a number.

Syntax:
Object(Expression)

Parameter :

• Expression(Number). A fractional number.

40. Functions of common modules

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 the given 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.

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

Login to the site as a student

Log in 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 " Data set" 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 " Further":

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

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 a collumn " Data path"we write the name of the new field ( seamlessly, 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 " 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.