Any trade organization for competent management needs to promptly receive information about the current state of goods, sales, profits. However, if you consider that organizations often work with a wide range of goods and big amount counterparties, the tool for setting up the analytical report plays an important role in obtaining the information you need... Let's take a closer look at the procedure for working with custom fields in the reports of a typical program (rev. 11).

The development of the 1C: Enterprise 8 platform offers users more and more opportunities. So, for example, in version 8.2, reports have become more flexible, allowing them to be customized to various requirements without the help of qualified programmers.

Change report settings

To view or change the report settings, go to the All Actions menu and select the Change Variant command. After that, a window with settings for an open report opens before the user.

In the upper part of the settings window, you can see the structure of the report, which is nothing more than a visual reflection of the report columns and groupings of its rows, which essentially makes it possible to present the order in which this report displays analytical data.

At the bottom of the settings window, the actual settings of information and fields are displayed, referring either to the report or to the elements of its structure (columns and groupings of rows).

Custom fields

In a programme 1C: Trade Management 8 edition 11 of the report settings provide users with more options in their formation.

In the window for editing the settings and structure of reports, we would like to draw special attention of users to the “Custom fields” tab, which allows you to create your own fields and add them to the report. It should be recalled that previously the user could only use those fields that were implemented by the developer.

Let's consider the use of this function using an example.

Suppose the head of a trading company that sells goods in Moscow and the regions wants to customize the Sales History report, wanting to display in it data on product sales in Moscow and the regions in general, as well as to partners. It is very easy to do this using custom fields.

Thus, an illustrative example shows that flexible settings for custom fields in a typical program 1C: Trade Management 8 provide the user with ample opportunities in the settings of reports, allowing them to quickly receive the necessary information in a convenient form.

Data Composition System Expression Language

The data composition system expression language is designed to write expressions used in various parts of the system.

Expressions are used in the following subsystems:

• data composition schema - for describing calculated fields, total fields, relationship expressions, etc.
• data composition settings - for describing custom field expressions;
• data composition template - for describing data set relationship expressions, describing template parameters, etc.

Literals

Expressions can contain literals. Literals of the following types are possible:

• Line;
• Number;
• Date;
• Boolean.

Line

A string literal is written in the characters “” ”, for example:

"String literal"

If you need to use the "” "character inside a string literal, you should use two such characters.

For example:

“Literal“ “in quotes“ ““

Number

The number is written without spaces, in decimal format. Fractional part separated by a "." For example:

10.5 200

date

The date literal is written using the DATETIME key literal. After this keyword, in brackets, separated by commas, the year, month, day, hours, minutes, seconds are listed. The time is optional.

For example:

DATE TIME (1975, 1, 06) - January 6, 1975 DATE (2006, 12, 2, 23, 56, 57) - December 2, 2006, 23 hours 56 minutes 57 seconds, 23 hours 56 minutes 57 seconds

Boolean

Boolean values ​​can be written using the literals True, False.

Meaning

To specify literals of other types (system enumerations, predefined data), the Value keyword is used, followed by the literal name in parentheses.

Value (Account Type. Active)

Operations on numbers

Unary -

This operation is intended to reverse the sign of a number. For example:

Sales.Quantity

Unary +

This operation does not perform any action on the number. For example:

Sales.Quantity

Binary -

This operation is designed to calculate the difference between two numbers. For example:

Balances & Turnovers.Initial Balance - Balances & Turnovers.Final Balance Balances & Turnovers.Initial Balance - 100 400 - 357

Binary +

This operation is designed to calculate the sum of two numbers. For example:

Balances & Turnovers.Initial Balance + Balances & Turnovers.Turnover Balances & Turnovers.Initial Balance + 100 400 + 357

Work

This operation is designed to calculate the product of two numbers. For example:

Nomenclature.Price * 1.2 2 * 3.14

Division

This operation is intended to obtain the result of dividing one operand by another. For example:

Nomenclature.Price / 1.2 2 / 3.14

Remainder of the division

This operation is intended to obtain the remainder of dividing one operand by another. For example:

Nomenclature Price% 1.2 2% 3.14

String operations

Concatenation (Binary +)

This operation is intended to concatenate two strings. For example:

Nomenclature.Article + “:” + Nomenclature.Name

Like

This operation checks if the string matches the passed pattern.

The value of the LIKE operator is TRUE if the value<Выражения>matches the pattern, and FALSE otherwise.

The following characters in<Строке_шаблона>make sense other than just the next line character:

• % - percentage: a sequence containing zero or more arbitrary characters;
• _ - underscore: one arbitrary character;
• […] - one or more characters in square brackets: one character, any of those listed inside square brackets. The enumeration can contain ranges, for example, a-z, meaning an arbitrary character included in the range, including the ends of the range;
• [^…] - in square brackets a negation icon followed by one or more characters: any character except those listed after the negation icon;

Any other symbol means itself and does not carry any additional meaning. If it is necessary to write one of the listed characters as oneself, then it must be preceded by<Спецсимвол>specified after the ESCAPE keyword.

For example, the template

“% ABC [abcg] \ _ abc%” SPECIAL SYMBOL “\”

means a substring consisting of a sequence of characters: the letter A; letters B; letters B; one digit; one of the letters a, b, c or d; underscore character; letters a; letters b; letters c. Moreover, this sequence can be located starting from an arbitrary position in the line.

Comparison operations

Equals

This operation is intended to compare two operands for equality. For example:

Sales.Contractor = Sales.NomenclatureMainSupplier

Not equal

This operation is intended to compare two operands for inequality. For example:

Sales, contractor<>Sales.NomenclatureMainSupplier

Smaller

This operation is intended to check that the first operand is less than the second. For example:

SalesCurrent.Amount< ПродажиПрошлые.Сумма

More

This operation is intended to check that the first operand is greater than the second. For example:

SalesCurrent.Total> SalesPast.Amount

Less than or equal to

This operation is designed to check that the first operand is less than or equal to the second. For example:

SalesCurrent.Amount<= ПродажиПрошлые.Сумма

More or equal

This operation is intended to check that the first operand is greater than or equal to the second. For example:

SalesCurrent.Amount> = SalesPast.Amount

Operation B

This operation checks for the presence of a value in the passed list of values. The result of the operation will be True if the value is found, or False otherwise. For example:

Item B (& Item1, & Item2)

The operation of checking the existence of a value in a dataset

The operation checks for the existence of a value in the specified data set. The validation dataset must contain one field. For example:

Sales. Counterparty To Counterparties

The operation of checking a value for NULL

This operation returns True if the value is NULL. For example:

Sales.Contractor IS NULL

The operation of checking a value for NULL inequality

This operation returns True if the value is not NULL. For example:

Sales.Contractor IS NOT NULL

Logical operations

Logical operations take Boolean expressions as operands.

Operation NOT

The operation does NOT return True if its operand is False, and False if its operand is True. For example:

NOT Document.Consignee = Document.Shipper

Operation AND

The AND operator returns True if both operands are True, and False if one of the operands is False. For example:

Document.Consignee = Document.Shipper AND Document.Consignee = & Counterparty

OR operation

The OR operation returns True if one of the operands is True, and False if both operands are False. For example:

Document.Consignee = Document.Carrier OR Document.Consignee = & Counterparty

Aggregate functions

Aggregate functions perform some action on a dataset.

Sum

The Sum aggregate function calculates the sum of the expression values ​​passed to it as an argument for all detail records. For example:

Amount (Sales.AmountTurnover)

Quantity

The Count function calculates the number of non-null values. For example:

Quantity (Sales. Contractor)

Number of different

This function calculates the number of distinct values. For example:

Quantity (Various Sales. Contractor)

Maximum

The function gets maximum value... For example:

Maximum (Balances.Quantity)

Minimum

The function gets the minimum value. For example:

Minimum (Balances.Quantity)

The average

The function gets the average for non-NULL values. For example:

Average (Balances.Amount)

Other operations

Operation SELECT

Operation Select is designed to select one of several values ​​when certain conditions are met. For example:

Choice When Amount> 1000 Then Amount Else 0 End

Rules for comparing two values

If the types of the compared values ​​differ from each other, then the relationship between the values ​​is determined based on the priority of the types:

• NULL (lowest);
• Boolean;
• Number;
• Date;
• Line;
• Reference types

The relationship between different reference types is determined based on the reference numbers of tables corresponding to a particular type.

If the data types match, then the values ​​are compared according to the following rules:

• Boolean type has TRUE value greater than FALSE value;
• the Number type has the usual comparison rules for numbers;
• the Date type has earlier dates less than later ones;
• for the String type - string comparisons in accordance with the established national characteristics of the database;
• reference types are compared based on their values ​​(record numbers, etc.).

Working with a NULL value

Any operation in which the value of one of the operands is NULL will result in NULL.

There are exceptions:

• the AND operation will return NULL only if none of the operands is False;
• the OR operation will return NULL only if none of the operands is True.

Operation Priorities

Operations have the following priorities (the first line has the lowest priority):

• B IS NULL IS NOT NULL;
• =, <>, <=, <, >=, >;
• Binary +, Binary -;
• *, /, %;
• Unary +, Unary -.

Data composition system expression language functions

Calculate

The Calculate function is designed to evaluate an expression in the context of some grouping. The function has the following parameters:

• Expression. Type String. Contains a calculated expression;
• Grouping. Type String. Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as a grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal line is used as the grouping name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent grouping with this name. For example:

Amount (Sales.SumTurnover) / Calculate ("Amount (Sales.SumTurnover)", "TotalTotal")

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

Level

The function is intended to get the current recording level.

Level()

NumberOrder

Get the next sequence number.

NumberOrder ()

NumberOrderInGroup

Returns the next sequential number in the current grouping.

NumberOrderInGrouping ()

Format

Get the formatted string of the passed value.

The format string is set in accordance with the 1C: Enterprise format string.

Options:

• Meaning;
• Format string.

Format (Invoices.SumDoc, "NPT = 2")

Beginning of period

Options:

• • Minute;
  • Day;
  • A week;
  • Month;
  • Quarter;
  • Decade;
  • Half a year.

Beginning of Period (DateTime (2002, 10, 12, 10, 15, 34), "Month")

Result:

01.10.2002 0:00:00

End of Period

The function is designed to extract a specific date from a given date.

Options:

• Date. Type Date. Specified date;
• Period type. Type String. Contains one of the values:
  • Minute;
  • Day;
  • A week;
  • Month;
  • Quarter;
  • Decade;
  • Half a year.

EndPeriod (DateTime (2002, 10, 12, 10, 15, 34), "Week")

Result:

13.10.2002 23:59:59

Add to Date

The function is intended for adding a certain value to the date.

Options:

• Magnification type. Type String. Contains one of the values:
  • Minute;
  • Day;
  • A week;
  • Month;
  • Quarter;
  • Decade;
  • Half a year.
• Value - by how much the date should be increased. Type Number. The fractional part is ignored.

Add to Date (DateTime (2002, 10, 12, 10, 15, 34), "Month", 1)

Result:

12.11.2002 10:15:34

Date Difference

The function is designed to get the difference between two dates.

Options:

• Expression. Type Date. Original date;
• Expression. Type Date. Subtracted date;
• Difference type. Type String. Contains one of the values:
  • Second;
  • Minute;
  • Day;
  • Month;
  • Quarter;

DIFFERENCE (DATE TIME (2002, 10, 12, 10, 15, 34), DATE TIME (2002, 10, 14, 9, 18, 06), "DAY")

Result:

Substring

This function is designed to extract a substring from a string.

Options:

• Line. Type String. The string from which to extract the substring;
• Position. Type Number. The position of the character from which the substring to be extracted from the string begins;
• Length. Type Number. The length of the substring to select.

SUBSTRATE (Contractors.Address, 1, 4)

Line Length

The function is designed to determine the length of a string.

Parameter:

• Line. Type String. The string to be specified in length.

String (Accounts.Address)

Year

This function is designed to extract a year from a value of the Date type.

Parameter:

• Date. Type Date. The date by which the year is determined.

YEAR (Invoice Date)

Quarter

This function is intended to extract a quarter number from a value of the Date type. The quarter number is normally in the range from 1 to 4.

Parameter

• Date. Type Date. The date by which the quarter is determined

QUARTER (Consignment Date)

Month

This function is intended to extract the number of the month from a value of the Date type. The month number is normally in the range from 1 to 12.

• Date. Type Date. The date by which the month is determined.

MONTH (Invoice Date)

Day of the Year

This function is intended to get the day of the year from a value of the Date type. The day of the year is normally in the range from 1 to 365 (366).

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

DAY OF THE YEAR (Invoice Date)

Day

This function is intended to get the day of the month from a value of the Date type. The day of the month is normally in the range from 1 to 31.

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

DAY (Invoice Date)

A week

This function is intended to get the number of the week of the year from a value of the Date type. The weeks of the year are numbered starting at 1.

• Date. Type Date. The date by which the week numbers are determined.

WEEK (Invoice Date)

Day of the week

This function is intended to get the day of the week from a value of the Date type. The day of the week is normally between 1 (Monday) and 7 (Sunday).

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

DAY OF THE WEEK (Invoice Date)

Hour

This function is intended to get the hour of the day from a value of the Date type. The hour of the day ranges from 0 to 23.

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

HOUR (Invoice Date)

Minute

This function is intended to get the minute of the hour from a value of the Date type. The minute of the hour ranges from 0 to 59.

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

MINUTE (Invoice Date)

Second

This function is intended to get the second of the minute from a value of the Date type. The second of a minute ranges from 0 to 59.

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

SECOND (Invoice Date)

Express

This function is intended to extract a type from an expression that can contain a complex type. If the expression contains a type other than the required type, a NULL value will be returned.

Options:

• The expression to be converted;
• Type indication. Type String. Contains a string of type. For example, "Number", "String", etc. Besides primitive types given string may contain the name of the table. In this case, an attempt will be made to express to a reference to the specified table.

Express (Data.Props1, "Number (10.3)")

YesNull

This function returns the value of the second parameter if the value of the first parameter is NULL.

Otherwise, the value of the first parameter will be returned.

YesNULL (Amount (Sales.SumTurnover), 0)

Common module functions

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

In this example, the function "Abbreviated Name" will be called from the general configuration module.

Note that the use of the functions of common modules is allowed only when the corresponding parameter of the data composition processor is specified.

In addition, the functions of common modules cannot be used in custom field expressions.

Supplementing ready-made reports with your own columns.
In the report made using the "Data composition schema" from the user mode, you can add your own Fields, which will be displayed during generation.
It is allowed to use arithmetic operations in these fields.

A long time ago I wrote a report "Sales of goods by shipment" http://infostart.ru/public/16477/. In this publication, we will not discuss its relevance and merits. This report will be taken as a template for experiments, modifications. Moreover, in the comments they asked how to add Fields.

Open "Settings ..." and go to the "User settings" tab.

2. Add "New Expression Field"

Adding fields means prescribing arithmetic operations between the Available fields (this is allowed, marked with a green square). Also, a feature, if the name of such a field consists of two or more words, it must be enclosed in square brackets... It will turn out like this - [Sale amount].

After saving, this field will become available for selection:

Here, with the help of simple manipulations, you get the value you need for yourself. This opportunity allow to add the required fields. In such calculation options as you need. Profitability, Efficiency and other values ​​will be considered in your arithmetic logic.

In this case, your work will not be lost and you can use it the next time you open the report.

Functions used when writing fields.

• Arithmetic "+, -, *, /" is understandable with this, I think it is not necessary to explain the principle of their operation.
• Boolean expressions c are used to determine selections. ">,< , = , >= , <= , <>, AND, OR, NOT ". These expressions are used with a select operation.
• Selection operations. Apply to obtain one of the possible values ​​in accordance with the specified conditions

CHOICE
WHEN< Операция выбора >THEN<Выражение>
OTHERWISE<Выражение>
THE END
Example:
CHOICE
WHEN Quantity> 0 and Amount> 0 THEN "Correct"
ELSE "Error"
THE END

This is deciphered as follows: if the amount is greater than zero and the sums are greater than zero, the text "Correct" will be displayed, and if at least one of the conditions is not met, "Error" will be displayed.

• A built-in function allows you to make certain transformations with fields.

SUBSTRING ("Expression", "Start Position", "End Position") - selects the part of the "Expression", limited by the frames "Start ..." and "End ...".
How it happens with the Nomenclature value - "Chocolate paste" the formula Substring (Nomenclature, 1,5) will output the "Paste" value

The functions are used to work with the date

YEAR(<Дата>), QUARTER (<Дата>), MONTH(<Дата>), DAY (<Дата>), DAY(<Дата>), A WEEK(<Дата>), WEEKDAYS (<Дата>),HOUR(<Дата>), MINUTE (<Дата>), SECOND (<Дата>)

The value corresponding to the function name will be displayed.

BEGINNING OF PERIOD ("Date", "Period of shift"), END OF PERIOD ("Date", "Period of shift"). For function data, the Date expression value is at the beginning or end of the specified date.

ADD TO DATE ("Date", "Shift period", "The amount by which you want to increase the date") - the function allows you to increase or decrease the date by a certain value.

DIFFERENCE DATE ("Date1", "Date2", "Shift period") - The function is intended to get the difference between two dates.

"Shift Period" is one of "Minute, Hour, Day, Week, Month, Quarter, Year, Decade, Half Year".

5.Aggregate functions of the query language are used when grouping results, calculating totals

SUM (<>) the arithmetic sum of all values ​​included in the sample.
THE AVERAGE (<>) calculates the average of all those in the sample
MINIMUM (<>) calculates the minimum value of all values ​​in the sample
MAXIMUM (<>) calculates the maximum value of all values ​​in the sample
NUMBER (<>) counts the number of parameter values ​​in the sample. This feature allows for use cases. QUANTITY (*) - allows you to count the number of rows in the result. QUANTITY (VARIOUS "Expression") - allows you to count the number of different values ​​of the specified field.

Amount (Choice
When Substring (Nomenclature, 1, 5) = "Paste"
Then Quantity * 0.2
Otherwise Amount * 0.35
End)

The result is the sum of the values ​​when at the beginning of the name of the item there is "Pasta" in this case, the quantity increases by 0.2, if not present, it increases by 0.35.

Year ([Document.Date])

The year when the document was written / posted will be displayed.

More complex example, without decoding.

Choice
When Quarter ([Document.Date]) = 1 AND Substring (Nomenclature, 1, 5) = "Paste"
Then Quantity * 0.2
Otherwise Choice
When Quarter ([Document.Date]) = 2
Then Quantity * 0.3
Otherwise Choice
When Quarter ([Document.Date]) = 3
Then Quantity * 0.4
Otherwise Choice
When Quarter ([Document.Date]) = 4
Then Quantity * 0.5
End
End
End
End

We have examined in some detail the settings for reports implemented on the basis of the ACS. Now let's take a look at the finer and more detailed settings of the report options. The window of "advanced" settings of the report variant is called by the command "More" - "Other" - "Change the variant of the report".

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

1. Structure of the report.

2. Report settings.

The structure section of a report variant 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 table of the structure of the report variant, in addition to the column with groupings itself, contains several additional columns:

The settings section of the report option gives the user ample opportunity to configure the report to suit his needs. It almost completely coincides with standard settings of the report, discussed in part 1. Let's consider 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.

ACS parameter is some value used to get report data. This can be a condition value for selecting or validating data, or an auxiliary value.

The parameter table is presented in the "Parameter" - "Value" format. Parameter values ​​can be changed if necessary. Clicking on the Custom Settings Item Properties button opens the custom settings for the item.

In this window, you can select whether the element will be included in the user settings (i.e. visible to the user when setting up the report), set the element presentation and edit mode ( fast access in the report header, normal in the report settings and unavailable).

Customization item properties also have groupable fields, fields, filters, and conditional elements.

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

User can add two kinds of fields:

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

Select fields allow you to calculate a value based on a given condition. The window for editing the selection field contains the title of the field and the table in which the selection, value and presentation of the field are set. Selection is a condition, depending on which the required 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, they sold a little, and if more than 10 units, a lot. To do this, we will set 2 values ​​for the calculated field: the first will be with the selection "Number of goods is less than or equal to" 10 "", the second with the selection "Number of goods is greater than" 10 "".

Expression fields allow you to compute a value using arbitrary algorithms. They can use the functions of the query language and the built-in programming language 1C. The expression field edit window contains two fields for detail and summary expressions. Totals records are groupings configured in the "Report Structure" area, in which you must use aggregate functions ("Sum", "Minimum", "Maximum", "Quantity").

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

· For detailed notes:

Choice

When [Amount of sales without discount] = 0

Then 0

Otherwise [Amount of sales without discount] - [Amount of sales with discount] / [Amount of sales without discount]

End

· For totals records:

Choice

When Amount ([Amount of sales without discount]) = 0

Then 0

Otherwise Amount ([Amount of sales without discount]) - Amount ([Amount of sales with discount]) / Amount ([Amount of sales without discount])

End

As mentioned earlier, in the expression of totals we use the "Sum" aggregate function.

3. Groupable fields. Contains the fields by which the result of the report variant will be grouped. Grouping fields are configured separately for each of the groupings, but you can define general grouping fields for a report variant if you select the "Report" root 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. Also, this tab allows you to change the order of the grouped fields.

4. Fields. Contains the fields that will be output in the result of the report variant. The fields are configured separately for each of the groupings, but you can set general fields for a report variant if you select the "Report" root 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. Also, this tab allows you to change the order of the fields.

Fields can be grouped to logically highlight some part of the report or to define 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 the fields automatically;
• Horizontal - fields are arranged horizontally;
• Vertically - fields are arranged vertically;
• In a separate column - fields are located in different columns;
• Together - the fields are arranged 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 of the groupings, but you can set general filters for a report variant if you select the "Report" root in the structure tree.

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

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

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

In conclusion, I would like to note that the report settings can not only be saved as a report variant, but also uploaded to a file (menu "More" - "Save settings"). To download, you need to select "Load settings" and select the saved file. Thus, we can transfer the settings of the report variant between different bases 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 later if necessary.

The report receives the balances of goods in warehouses in various aspects (characteristics, series, units of measure, etc.). In order not to complicate the example, we will leave only the grouping by the nomenclature and display only the final balance as of the date of the report. The summary table will show the stocks of the item in the inventory of warehouses.

But then the user said that he needs to add two additional fields:

1. Quantity alert. If the number is less than or equal to 5, then the alert is "Not enough". If the number is greater than 5, then less than or equal to 10, then the alert is "Normal". If the number is more than 10, then "Oversupply".
2. The formula for calculating the final balance. In the additional column, the user wants to see what actions the program performed to get the value of the final remainder. That is, the formula "Initial balance + Turnover = Final balance", where the corresponding values ​​will be substituted.

Of course, a programmer could intervene here and change the data composition schema query and report settings, but we will perform the task in user mode without changing the report in the configurator mode.

Additional fields

And so, let's get started. Let's go to the settings of the report option:

Before us will open the constructor of settings for the ACS report option. Going to the "Custom Fields" tab, let's start creating them.

The screenshot already shows two created custom fields, the functionality of which was described above. Let's consider the settings of each. Let's start with the "Alert" field.

In the setting, we need to set the field title that will be displayed in the report, as well as set expressions to form the value in the detailed records field and in the totals for this field. Since there is no need to show a notification in the totals, we will write an expression only for detailed records.

The expression syntax is similar to the 1C: Enterprise query language. There are some differences, but we will not deal with them in detail in this article. The expression uses the select operator:

"Choice When Then Otherwise End ",

similar to the operator in the query language. Fields in custom field expressions are specified by their presentation. In order for the platform to understand that the entered view denotes a certain field, the view name is surrounded by square brackets "". If the field representation has one word, then square brackets are optional. In the example above, we are referring to the Ending Balance field.

The settings for the "Formula for calculating the final balance" field are similarly set:

Here I need to say about some of the nuances:

1. We can show the formula in a cell only as a string. Therefore, we convert all numeric values ​​to a string using the ACS expression language method "String ()", which converts any value to a string. Then we perform string concatenation.
2. In order for the field to be displayed in the report totals, let's add a similar formula to the expression of totals records. You just need to add the aggregating functions "SUM ()" for each value in accordance with the totals.

Everything is ready to use the fields in the report!

Setting up and generating a report

Add the field "Formula for calculating the final balance" to the output fields of the report:

Add the "Alert" expression to the item detail records. To do this, add the "Alert" expression field to the "Nomenclature" grouping. After that, the structure of the report will take the following form:

Setting up the report is now complete. If necessary, the added settings can be saved for reuse... Let's generate a report:

As we can see, fields have been added to the report in accordance with the conditions of the task. At the same time, there was no need to modify the report in the configurator mode. This is the main advantage of custom fields! A user trained to use them can use them to create a report according to their requirements. on their own, without the participation of a programmer.

Trust me, you can train him to use these capabilities, because it is much easier than writing formulas in spreadsheets Excel.