1s SKD aggregate functions array. Functions of the expression language of the data composition system. Changing the order of columns
In this short note I want to show how you can summarize values at various levels of grouping in a report using a data composition system.
As shown in the image, only at the “Item Groups” grouping level, the “Order” resource is calculated, it displays how much needs to be ordered for the current item group based on certain conditions:
This value can only be calculated for this level groupings because, above or below, there are no values to calculate. For example, at the level of detailed records, there is no data on the maximum quantity in a group, because this data is valid only for the group as a whole, and not for its individual components.
Accordingly, now it is necessary to calculate the totals for the above groupings (“Warehouses”, “Warehouse Types”) and the overall total.
To do this, use the function CalculateExpressionWithGroupArray:
EVALUATE EXPRESSIONWITHGROUPARRAY (EVALEXPRESSIONWITHGROUPARRAY)
Syntax:
EvaluateExpressionWithGroupArray(,)
Description:
The function returns an array, each element of which contains the result of evaluating an expression for grouping by the specified field.
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.
The layout builder, when generating expressions for the output of a custom field whose expression contains only the CalculateArrayWithGroupArray() function, generates the output expression so that the output information is 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)),Sum(DataSet.AmountTurnover)",,"DataSet.Account"),"2")))
Options:
Type: String. The expression to be evaluated. String, for example, Amount(AmountTurnover).
Type: String. Grouping field expressions – expressions of grouping fields, separated by commas. For example, Contractor, Party.
Type: String. An expression describing the selection applied to detail records. The expression does not support the use of aggregate functions. For example,DeletionFlag = False.
Type: String. An expression describing the selection applied to group records. For example, Amount(AmountTurnover) > &Parameter1.
Example:
Maximum(CalculateExpressionWithGroupArray("Amount(AmountTurnover)", "Counterparty"));
A detailed description of the function syntax can be found at http://its.1c.ru/db/v837doc#bookmark:dev:TI000000582
Now, for the calculation, we duplicate the “Order” field, with different values “Calculate by...”, using the following expressions, note that in each higher level the values of the levels below the groupings are used.
As a result, we get the following construction:
Let's create a report, add a basic data layout scheme, add a set of data of the "query" type to the access control system and generate a simple request (see Figure 1).
| Figure 1. Data set query |
Our calculated field will be used as a resource, so on the "Resources" tab we set the grouping expression: ConnectRows(Array(Products.Nomenclature),""). We also indicate that this resource must be calculated using the Link field (see Figure 3).
Result of the report:
The downside is the use of grouping; in our case, grouping is performed by the Link attribute, otherwise it will not work. The presence of a grouping imposes some restrictions on setting up data output (see Figure 4).
2) Calling your own function from a common module.
A simple and clear way, the only difficulty is to find a suitable common module or create a new one to write your own function :). Using the passed link to the document "Goods Receipt", the function will receive rows from the tabular part of the document and generate a row with information about the received goods. Let's start implementation.Let's create a report, add a basic data layout scheme, add a set of data of the "query" type to the access control system and generate a simple request (see Figure 6).
 |
| Figure 6. Data set query |
On the “Calculated Fields” tab, add a calculated field called TabularPart and in the Expression column we indicate a function call from the general module (see Figure 2):
_DemoWorking WITH Reports.Get List of Products for the Report (Link)
Let's go to the "Settings" tab and create a setting for data output - this will be a detailed record (see Figure 8).
Result of the report:
Download sample report
What you need to know when using this method:
1) The data composition engine expression may contain calls to functions of global common configuration modules. For example:
AbbreviatedName(Link, Date, Number)
2) When generating a report programmatically, the use of the functions of common modules is allowed only if the corresponding parameter of the data composition processor is specified (4th parameter):
CompositionProcessor = newDataCompositionProcessor;
LayoutProcessor.Initialize(LayoutLayout, DecryptionData, True);
3) Functions of common modules cannot be used in custom field expressions.
4) If an exception is thrown when generating a report indicating a function of a common module, check the module execution context (see Figure 9).
Description of the functions used
ConnectRows(Value, ItemSeparator, ColumnSeparator)- designed to combine strings into one line.- Value - expressions that need to be combined into one line. If it is an Array, then the elements of the array will be combined into a string. If it is a ValueTable, then all columns and rows of the table will be combined into a row;
- Element Separator - A string containing text to be used as a separator between array elements and value table rows. Default – line feed character;
- Column Separators - a string containing text to be used as a separator between columns of the value table. Default "; ".
Get List of Products for Report (DocumentLink)- the function generates a line with a list of incoming goods.
// Options:
// DocumentLink - DocumentLink.Goods Receipt - Document "Goods Receipt".
// Return value:
// String - a string with a list of products.
Function Get List of Products for Report (DocumentLink) Export
List of Products = "";
For Each Line PM From DocumentLink.Products Cycle
List of Products = List of Products + Line TC.Nomenclature;
List of Products = List of Products + Symbols.PS;
EndCycle;
Return List of Products;
EndFunction // Get List of Products for 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)
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 the group name is used empty line, 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 in this example the result will be the ratio of the amount for the “Sales.SumTurnover” field of the grouping record to the amount of the same field in the entire layout. - 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)
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 last record goes beyond the grouping, 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 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;
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".
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 Groups- selection applied to group records. For example: "Amount(AmountTurnover) > &Parameter1".
Maximum(CalculateExpressionWithGroupArray("Amount(AmountTurnover)", "Counterparty"));
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, 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 in this parameter an aggregate function is used, an error will occur when composing the data;
- Selection of Groups- selection applied to group records. For example: "Amount(AmountTurnover) > &Parameter1".
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- format string is set in accordance with format string 1C:Enterprise.
Format(Consumable Invoices.Doc Amount, "NPV=2")
9. BeginOfPeriod
Syntax:
StartPeriod(Date, PeriodType)
Options :
- date(Date of). Specified date;
- Period Type
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.
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.
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.
DATEDIFFERENCE(DATETIME(2002, 10, 12, 10, 15, 34),
DATETIME(2002, 10, 14, 9, 18, 06), "DAY")
Result: 2
13. Substring - this function is intended 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.
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.
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.
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
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.
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.
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.
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.
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.
DAY OF THE WEEK(Expense.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.
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.
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.
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. Besides primitive types given line may contain the table name. In this case, an attempt will be made to express a reference to the specified table.
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.
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.
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)
Options :
- Base(Number). The basis of the operation of exponentiation.
- Index(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)
Options :
- Expression(Number). Original number;
- Bit depth(Number). The number of decimal places to round to.
Syntax:
Object(Expression)
Parameter :
- Expression(Number). A 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 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.
Proper use of a data composition scheme (DCS) allows you to:
- significantly reduce the time required to develop a report;
- get rid of the need to create controlled form processor;
- get a beautiful result with the opportunity additional settings 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:
- 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);
- 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.
Some 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:
- Calculate the average cost of the item;
- All kinds of interest;
- Calculations of average earnings, etc.
To avoid problems, in these cases it is advisable to enter a test for division by 0 into the calculated field.
This can be done using the construction “Choice When….Then… Otherwise… End”.
Finally, a few words about enough new feature 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.
Good day, dear readers of our blog! Today I want to tell you, using a real example, how
use the ACS functions Array and JoinStrings. In an article about
there was a little mention of how to work with
on the bookmark
, has also already been explained as
using the "Resources" tab. Today we will once again pay attention to these
bookmarks in light of our topic.
Appearance of an example 1C report using two ACS functions Array And ConnectRows next:
Statement of the problem for using the ACS functions Array and JoinStrings
Task: You need to insert all sales numbers for one product into one cell.
Something like: nomenclature | quantity | 001, 002, etc. |
We will use a request to the accumulation register “Realization of TMZ”.
CHOOSE
Implementation of TMZ. Registrar. Number,
Implementation of TMZ. Nomenclature,
Implementation of TMZ. Registrar,
Implementation of TMZ. QuantityTurnover
FROM
Accumulation Register. Implementation of TMZ. Revolutions
(, , Registrar, ) HOW TO Implement TMZ
The accumulation register “Realization of TMZ” exists both in the Accounting configuration and in the Trade Enterprise Management (UTP) configuration. Find out how to configure the USP in the “Nomenclature” directory element. Therefore, after downloading, you can run this report in both configurations to check its functionality.
Implementation of the task using the SKD Array and ConnectRows functions
The same steps, but with some additions, were used to create
.
Description of the JoinStrings function
JoinStrings
Used to concatenate strings into one string.
Syntax:
ConnectRows(Value, ItemSeparator, ColumnSeparator)
Options:
- Meaning— expressions that need to be combined into one line.
If it is an Array, then the elements of the array will be combined into a string.
If it is a ValueTable, then all columns and rows of the table will be combined into a row; - Element Separator- a string containing the text to be used as
separator between array elements and value table rows. Default – line feed character; - Column Separators-a string containing the text to be used as a delimiter
between the columns of the table of values. Default ";".
