This article discusses some of the features of setting up a period when using a Data Composition System (DCS), problems that arise due to differences in the concept of a period between an ordinary user and the 1C system, and also suggests ways to solve them.
Most reports that are developed using a Data Composition System (DCS) require the user to enter the period for which the report will be built. As a rule, in ACS, period entry is organized through parameters, using the following construction, see. Fig.1 This method of entering a period is considered “classical”; it is described in an article on ITS and other literature devoted to development in 1C, so let’s take it as a basis. Let's consider as an example a simple request that receives all documents Sales of Goods and Services for a given period, see Fig.2 When using this report, the user sets the period through the parameters, see. Fig.3 Everything seems to be correct... BUT there is a small problem:
The thing is that the vast majority of users “understand” the period differently than 1C “understands” it, examples:
1). Let's consider Fig.3
From the user's point of view, the period is not specified, that is, UNLIMITED, that is, ALL documents without date restrictions should be included in the report.
“From the point of view” of the 1C system, the parameter-period is set and ... both of its boundaries are equal to 01.01.0001 and only documents with an empty date will be included in the report, which in practice means not a single document will be included.
2). Let's consider Fig.4
From the user's point of view, the report should include all documents starting from the date 01/28/2010.
“From the point of view” of 1C, the period 01/28/2010 – 01/01/0001 will cause an exception.
You can, of course, try to explain to the user why the report does not display the documents that he expects to see and how the period is presented from the “point of view” of 1C, but this is a thankless task, and it is also wrong. Good program must, first of all, be convenient for the user, because the program exists for the user, and not vice versa, therefore it is necessary to “teach” 1C to understand the period as the user understands it, namely:
1). Start of Period and End of Period are not specified -> all documents.
2). Only the Beginning of the Period is specified –> all documents starting from the Beginning of the Period
3). In addition, we will check that End of Period >= Beginning of Period, and if this is not true, then we will assume that End of Period is not specified, i.e. 2).
Based on the above, the expression for the End Date parameter will look like this:
SELECT WHEN &Period.EndDate=DATETIME(1,1,1) THEN DATETIME(3999,12,31,23,59,59) ELSE SELECT WHEN &Period.EndDate<&Период.ДатаНачала ТОГДА ДАТАВРЕМЯ(3999,12,31,23,59,59) ИНАЧЕ &Период.ДатаОкончания КОНЕЦ КОНЕЦ
The final form of our period selection design is shown in Fig.5
Some features of setting the period in the access control system.
Most reports that are developed using a Data Composition System (DCS) require the user to enter the period for which the report will be built.
As a rule, in ACS, period entry is organized through parameters, using the following construction, see. This method of period entry is considered “classical”; it is described in an article on ITS and other literature devoted to development in 1C, so let’s take it as a basis. Let's consider as an example a simple request that receives all documents Sales of Goods and Services for a given period, see
When using this report, the user sets the period through the parameters, see. Everything seems to be correct..., BUT there is a small problem:
The thing is that the vast majority of users “understand” the period differently than 1C “understands” it, examples:
From the user's point of view, the period is not specified, that is, UNLIMITED, that is, ALL documents without date restrictions should be included in the report.
“From the point of view” of the 1C system, the parameter-period is set and ... both of its boundaries are equal to 01.01.0001 and only documents with an empty date will be included in the report, which in practice means not a single document will be included.
From the user's point of view, the report should include all documents starting from the date 01/28/2010.
“From the point of view” of 1C, the period 01/28/2010 - 01/01/0001 will cause an exception.
You can, of course, try to explain to the user why the report does not display the documents that he expects to see and how the period is presented from the “point of view” of 1C, but this is a thankless task, and it is also wrong. A good program should, first of all, be user-friendly, because the program exists for the user, and not vice versa, therefore you will have to “teach” 1C to understand the period as the user understands it, namely:
1). Start of Period and End of Period are not specified -> all documents.
2). Only the Beginning of the Period is specified -> all documents starting from the Beginning of the Period
3). In addition, we will check that End of Period >= Beginning of Period, and if this is not true, then we will assume that End of Period is not specified, i.e. 2).
Based on the above, the expression for the End Date parameter is:
WHEN &Period.EndDate=DATETIME(1,1,1)
THEN DATETIME(3999,12,31)
WHEN &Period.End Date<&Период.ДатаНачала
THEN DATETIME(3999,12,31) DATETIME(3999,12,31,23,59,59)
&Period.End Date
The final form of our period selection design is shown in
Note: this mechanism for setting parameters is intended for older platforms 1C 8.1 and 8.2 (and configurations running under their control); older versions of the 1C platform have built-in mechanisms for controlling empty parameters and there is no need to resort to the mechanism described in this article, in addition On some versions of the 1C platform, errors and incorrect operation are possible.
So, let's begin.
For simplicity, understanding the example, we will build on one simple circulating accumulation register.
In my case, this is the accumulation register "Work in Progress Accounting".
For example, we will indicate its parameters rigidly (not through soft imposition of parameters on the access control system):
Please note that the frequency of the virtual table is “Record”.
But, as noted above, we need the period in terms of periodicity, so I propose to calculate the “Period” field in the following way (not very nice, but I haven’t seen any better options):
As can be seen from the screenshot, a parameter is passed to the request, which the user specifies on the form: The value of the "Frequency" enumeration - this enumeration is found in almost all standard solutions.
We will indicate its available types on the “Parameters” tab:
With this setting we format our period so that everything is beautiful and pleasing to the eye)
Here are the formats themselves:
Month: DF="MMMM yyyy "y.""
Day: DF = dd.MM.yyyy
Week: DF = ""Week from "dd.MM.yyyy"
Quarter: DF = "to "quarter" yyyy "y.""
Year: DF = "yyyy "y.""
Decade: DF = ""Decade with "dd.MM.yyyy"
Half-year: DF = ""Half-year from" dd.MM.yyyy"
That's all. The output is a wonderful picture:
When creating reports on an access control system, there is often a need to display a period selection on the report form, so that you do not need to enter dates manually, but select from a list of standard periods, such as: “Year”, “Month”, “Week”, etc. . For parameters of the Date type, you can only specify “The beginning of this year, month, etc.”, but “End” is not provided.
The thing is, of the data types, only the “Standard start date” type is available, but I also want the “Standard end date”.
There is a way to get around this.
- Let's create a new Parameter, call it “Period”
- Set this parameter to the type “Standard period”
- In the “Expression” field of the “Start of Period” and “End of Period” parameters, which are used in the request, set the expressions “ &Period.StartDate" and " &Period.End Date” respectively.
But there is a slight subtlety. If we use virtual tables in the query, then most likely the report will stop working and an error message like “Error processing the view, type mismatch, parameter number...” will be displayed.
To avoid this, you need to remove all virtual table parameters.
And add them to the tables on the “Data Composition” tab.
In order for the parameters to be displayed in the quick report settings, let’s enable the corresponding flag for the report parameters.
Now the period selection on the report form looks like this.
Good day, dear readers of the blog site! In the last article we learned why these roles are needed. And today, in the second of this series of articles, we will look at setting up a role with the “Period” property, and also consider examples of filling these roles. The remainder is calculated using the field with the “Period” role. Just like in the field with the “Dimension” role, which we will talk about another time. So, let's begin!
Let's create a new report:
- In the Configurator, select the menu item “File” - “New” - “External report”.
- Click on the “Open data composition diagram” button. In the dialog that opens, click the “Finish” button.
- Now let’s create a that accesses the virtual table “Accumulation Registers”.
- Right-click on the “Data Sets” node and select the line “Add Data Set - Query”.
- Now click on the “Query Builder” button. Let’s select the accumulation register “GoodsInWarehousesRemainsAndTurnover” (USP configuration).
- Let’s open the “Virtual Table Parameters” dialog and indicate that the frequency “Auto” will be used, that is, it will be possible to specify several periods.
Now let's configure the output fields. Let these be the following fields: “Registrar”, “PeriodMonth”, “Nomenclature”, “Quality” and information on balances. Adding a field is done by double-clicking the left mouse button on the desired field or using the “>” button. After adding the fields, click the “OK” button.
Please note that for some fields a role with the “Period” property is automatically configured.
Let's look at what exist role settings for the “Period” property. First, the serial number of the period is indicated. The numbering must be continuous, starting from one, from the lowest periods to the highest, that is, first there will be, for example, the line number, then “Recorder”, then the second, day, week, month, quarter, year.
Thus, the fields that appear in our request should be numbered. Notice that we have two period fields - "Registrar" and "PeriodMonth". The low field is “Registrar”, it is assigned one, and the high field is “PeriodMonth”, it is assigned two. We will look at this in more detail in the next article.
Let's set up our report:
- Let's go to the "Resources" tab and define the resources of our report.
- Click on the “>>” button to select all fields for resources.
- Now let’s go to the “Settings” tab and create a setting in the form of a list.
- Click on the “Data composition settings designer” button (the button in the form of a magic wand).
- Report type: "List". Click the “Next” button.
- Let's configure the output fields by clicking on the ">>" button. Let's arrange them like this: “PeriodMonth”, “Nomenclature”, “Quality”, “Registrar”.
- Click the “Next” button and set up the grouping. We will set up the grouping in the following order: “PeriodMonth”, “Nomenclature”, “Quality”. The “Registrar” grouping will be displayed in the form of detailed records.
- Click the “OK” button.
Let's open our report. If we run this report, we will see some features when receiving balances. If you look closely at the report result, you will immediately notice several errors. In particular, for some reason, at the very beginning of the company's period of activity, there is an initial balance.
And this error is related to the feature of receiving balances from the registrar. In order for these balances to be displayed correctly, you need to add one more field to the output fields of the request - the “PeriodSecond” field. To add the “PeriodSecond” field, open the report in the Configurator and click on the “Open data composition scheme” button. Now click on the “Query Builder” button and add “PeriodSecond”. In this case, the “Registrar” field will remain the first field of the period, “PeriodSecond” will be the second, and “PeriodMonth” will be the third.
What is a second for? The data composition system calculates balances by calculation, and in order to unambiguously determine the position of the recorder on the time axis, the link to the recorder itself is not enough; a second is also needed, that is, the date of this recorder, and then the layout system will be able to obtain the correct balance by calculation. If we specify the correct order of the fields and generate the report again, we will get:
Now there is no balance left for the start of activities under the Plinth nomenclature. Then for the next period it coincides with the final balance, that is, we see a truly correct result. You can download an example report from the link below. Did you like the article? What can be changed, what can be added? Feel free to share about it in the comments!
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:Probably, you shouldn’t try to surf the Internet yourself in search of 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:
Loading...
