Functions of the expression language of the data composition system. Using resources in SKD using the example of creating an external tabular report Calculate the amount by horizontal grouping of SKD 1s
There is an information register “Stops along the route”, which stores information about the distance from the start to the final stop.
Rice. 1. Information register "Stops along the route"
It is required to develop a report on the access control system that will show the distance between any two stops along the route.
Solution
In any configuration that supports managed forms, we will create a non-periodic register of information “Stops along the route”. Let's add to it the dimensions Route and Stop with a string type of length 4 and 50, respectively, as well as a Distance resource with a number type with a length of 10.2. Let's save the changes in the new configuration. To create an external report, in Menu-->File-->New, select “External report”, set its name and open the data layout diagram:
Rice. 2. Creating an external report on the access control system
In the window that opens, click the “Add data set” button and select the line “Add data set - query”:
Rice. 3. Adding a dataset - query
Let's create a new request. To solve the problem, we will need in the query the information register “Stops along the route” and, since the report should clearly show the distance between any two stops, we will create a query for a tabular representation along the X and Y axes, using a full external join of the register table with itself:
Rice. 5. Specifying resources for access control systems
Let's now go to the "Settings" tab and click the "Open settings designer" button, select the report type Table:
Rice. 7. Selecting report fields
Fig.8. Selecting Grouping Fields
Rice. 9. Selecting sort fields
All that remains is to use the "Properties of user settings elements" button to create these settings, and also on the "Other settings" tab to indicate how the resources will be located and discard the totals (they are not needed here):
Rice. 10 User and other settings.
The report is ready. Now let’s go from the configuration to the Enterprise and fill out the information register (if it is not already filled):
Rice. 11. Filling out the information register
And finally, from Menu-->File we call our report from the place where we saved it in the configurator, click the “Generate” button and see the solution to the problem:
Rice. 12. Report "Distance between stops"
The publication prompted me to write an article
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 at this grouping level, since there are no values above or below 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:
In data composition setting up totals looks a little different than in requests. Let us define the “Query” data set in the data composition system.
In the request itself, we do not configure the totals, but go to the “Resources” tab of the data composition. At the data composition schema level, we determine resources. These are the fields that need to be counted at the grouping level. Click on the “>>” button and the system itself will transfer all the numeric fields and define them as resources.
You can also specify non-numeric fields in resources. For example, if you select the “Link” attribute, the system will count the number of documents in our groups. This information may also be useful. So, in the layout diagram we only define resources, and the groupings themselves are configured at the report variant level. The user can also create the groupings that he wants to see in his report option settings.
Let's create a standard data composition setting.
Click on the “Open settings designer” button.
Let's select the report type - list. Click the “Next” button.
Let's select all fields and move the counterparty field to the top position. Click the “Next” button.
Let's select all fields and move the counterparty field to the top position. Click the “OK” button.
The result is the following setting:
As you can see, in setting up a report option, resources are highlighted with a green icon so that they can be quickly distinguished from other fields.
If we open our report in 1C:Enterprise mode and generate it, we will see that final data is generated at the grouping level. Results by item and by counterparties.
Setting up resources in the 1C data composition scheme
Now let's turn our attention to settings that exist for resources. In the “Expression” field we can specify an aggregate function that can be used to obtain the value of the resource. In the drop-down list you can see a number of standard functions, but not all. For example, there are no functions.
Here in the “Expression” field we can write our own expression.
In the "Expression" field we can also access the functions of common modules.
In addition, you can specify in the “Calculate by...” field which groupings the resource should be calculated by. If the “Calculate by...” field is not filled in, then the final value of the resource will be calculated at all grouping levels that are defined in the settings of the report option. In our case, we need to fill in the “Calculate by...” field of the “Quantity” resource, since we can sell goods with different units of measurement. For example: oil in liters and wheels in pieces. Isn't it true that it would be illogical to add up the quantities of these goods? Therefore, we need to leave the summation of quantity at the item level, and at the counterparty level
Let's remove the summation.
If we generate a report, we will see that the quantity totals are calculated only by item, and the quantity totals for contractors are empty.
Possibilities when describing resources in the 1C data composition scheme
let's consider a number of non-obvious features related to the description of resources.
- Each resource can define several times. But this only makes sense if
resource will be calculated according to different levels groups. For example, if the quantity, in one case
is summed up for the item, and for counterparties we get the minimum value.
If we generate a report, we will see that for the counterparty “Deriya” the minimum purchase is five units of the “Self-adhesive paper” product range.
- In the “Expression” field, in addition to writing a formula, you can use a special data composition function called “Calculate”. This function allows you to calculate some final value using a certain formula. For example, for each counterparty it is necessary to know the percentage of purchases in natural units relative to the total volume. But how to get the total volume of purchases by quantity? To do this, use the “Calculate” function and write the following expression in the “Expression” field:
As seen, all parameters of the "Calculate" function are strings. In order for the quantity field to be displayed beautifully in the report, we’ll configure it on the “Data Sets” tab. In the quantity line we will find the “Editing Options” field. Let’s open the dialog, find the “Format” line and edit the format line in it, setting the “Accuracy” value to two on the “Number” tab.
Let's run the report and see the result of calculating the percentage of purchases for the counterparty "AUPP KOS TOO" relative to
total volume:
At the end of the article I want to recommend you a free one from Anatoly Sotnikov. This is a course from an experienced programmer. It will show you on a separate basis how to build reports in the access control system. You just need to listen carefully and remember! You will receive answers to the following questions:Perhaps you shouldn’t try to surf the Internet yourself in search of the necessary information? Moreover, everything is ready for use. Just get started! All the details about what is in the free video lessons
- How to create a simple list report?
- What are the Field, Path and Title columns on the “Fields” tab for?
- What are the limitations for layout fields?
- How to configure roles correctly?
- What are the roles for layout fields?
- Where can I find the data composition tab in a query?
- How to configure parameters in the access control system?
- It gets even more interesting...
Here is one of the lessons about bookmarking the data composition in a query:
Let's look at an example:
Several units of goods are shipped to the client at different discounts. You need to display the discount values for each product in a report and calculate the average discount. Let's create a report on the access control system.
For all fields for which we need to display totals, we will set an expression (method) for their calculation on the “Resources” tab. For the "Discount" Field, set the calculation method as "Average".
We output the report:
As you can see, the total for the “Discount” field is calculated as the arithmetic average of the field values, i.e. the sum of all values divided by the number of values. But because Since the volumes of goods shipped with different discounts are different, the average discount can be calculated in another way: for example, as the ratio of the total amount of goods shipped, taking into account the discount, to the total amount of goods shipped. But here a question arises: if you can write a formula to calculate the values of the fields of detailed records in the ACS, then the totals are calculated automatically according to the expression specified on the “Resources” tab, and there, as we saw earlier, “Average” is calculated as the arithmetic mean of the displayed values, as In this case, should I calculate the total in this column using my own formula?
It's actually not difficult. To do this, we will adjust our report by adding to it the necessary fields that will participate in the calculation, in our case these are “AmountWithDiscount” and “AmountAtPrice”.
In order to “teach” the ACS to display our results, we take the following steps:
1. on the “Settings” tab, assign a name to the grouping “”, for example, “ResultSKD”, to do this, call the context menu for the grouping and click “assign a name”;
2. on the “Layouts” tab, add a “grouping header layout”, where we select the previously assigned name “TotalSKD”;
3. we draw a line of the layout, where we leave all the results, the calculation of which suits us, as they are, and to calculate the average discount percentage we write a formula for calculation;
If everything is correct, then when the report is displayed, two total lines will be displayed below it, first the one that is generated automatically, then the one created by you.
In order to leave the output of only your total line, you need to on the “Settings” tab in the “Other settings” section of the layout output, disable the output for “Horizontal grand total layout” and “Vertical grand total layout”
Loading...