What should be the perfect report configurator
A client received a request: “x report report configurator”. So that simple employees, far from SQL, could compile all kinds of reports necessary for them using any data in the system.
Abstract idea is clear. But how exactly should it look?
After sitting, thinking, having studied what is on the market in this regard, it is understood that this topic is still far from beaten and very relevant. Therefore, I want to share an example of the "report configurator" that I managed to implement on the basis of the ERP-Platform system.
In my opinion, it turned out to be ideologically close to the ideal, so at the beginning of the article I will try to explain the concept of what should be the report configurator “for people”.
The perfect report configurator is a combination of simplicity and functionality. For example, in 1C functional, but inexperienced in programming 1C people can not use it. Or in bitrix - simple, but can not do the necessary things.
')
The ideal report configurator will be understandable to the simple user and at the same time supports the creation of reports of any complexity level.
Such requirements to the report configurator were formed:
1) Simplicity
It is for non-programmers. A simple manager, far from understanding SQL, will report on data of interest.
2) Versatility
Reports can be generated throughout the system, according to any user data.
3) Automatic connection of the new
When changing the system configuration, if a new field is added to the table, or a new table, or a whole new module is installed, all changes should be automatically available in the configurator.
4) Nesting
There are links between the tables. For example, tasks have properties: type, status, etc. This is stored in special directories, only links in the main table. Since system for non-programmers, we will not load users what is left join. The user selecting the status in the field should receive not a number, but the status of the task “in work”, etc. The immersion depth must be at least one level in the associated tables.
5) Support for complex structures
It happens that the report is complex, it affects a lot of tables, and it is even impossible to get by with a SQL query, but serious PL / SQL processing is needed. To do this, the report configurator provides an indication of the procedure that the programmer will write on the embedded PL / SQL editor of the ERP Platform.
6) Scheduler and report delivery
Often reports are generated on a periodic basis. For example, the user wants to see in the morning reports in the mail, daily, weekly. monthly etc. A task scheduler is needed in which the report is set to automatically generate at a given moment.
In addition to the scheduler, you need a report delivery system. This is not necessarily a letter. Depending on the user's settings, there may be a notification in the interface, sending to a telegram, sending by email, etc. Delivery on all channels of communication with the user.
7) Charts
Formation of charts according to the report
8) Output
Print the result of the report in various formats, such as Excel or PDF.
What is in the world
We started with the study of what is in the world. The study showed that everything is sad. At first there was 1C. Yes, everything is cool there, you can do almost anything, but where is the claim 1, “for ordinary people”? There the devil will break his leg while he understands. The manager, who sees this for the first time in his life, simply melts the brain. In general, cool - but it should be easier and more intuitive.
In the popular crm systems, everything is sad. There is really nothing. The only thing that caught the eye was the Bitrix report configurator. In general, it fits the definition of “for people” and even liked it at first, but I will describe a few flaws as a good example:
1) System narrowness: reports are tied only to certain tables with the main functionality: “Tasks”, “Goods”, “Transactions”, etc. Only 6 pieces! But what to do if I want to analyze how many% of the work time in employee schedules are occupied by tasks of this or that type, or I want to get a list of clients whose email is not filled in contacts. To make reports on data from installed applications there is no speech.
2) Conditions can only be built to a depth of 4 nesting. Was it difficult to make unlimited?
3) You can sort by only one of the fields. And if you need to sort by task type, and inside type by date in descending order?
4) Reports all list in a column alphabetically. They are divided again according to the tables “Tasks”, “Goods”, ... 6 pieces. And if I need my own group, or a subgroup of reports. How to group reports by subject?
5) There is no scheduler to run at intervals. (I at least did not find it). At the same time in the tasks it is.
In general, the system or universal but very complex, or for people - but too narrow, or nothing at all.
Therefore, we go our own way.
Tree
To begin, it was decided to organize the construction of reports in the form of a tree structure.
Any user can create his own branch-node (you can create nested branches, there are no restrictions here), or add your report to other branches accessible to him. When you hover the mouse over a report, a report description pops up. I will not clutter the article with print screens from the tree management system. You can see here .
Secondly, you need access rights. It's not good for everyone to see everything. There are general reports, and there are those that can be seen by individual employees or groups of employees, including there may be entire branches of reports.
Here was the benefit of a regular system of access rights in the ERP-Platform. It has the structuring of access rights down to individual elements of the page, and it was quite easy to build in the reporting system. And after that, and access rights to the procedures for reporting, but more on that below.
Setting up the global structure
Next, you need to build a structure of connections in the system. The system should know which fields have dependencies with other tables (example of a task and its status). This configuration is done once by the configuration developer.
In essence, it is necessary to recreate the Foreign key (I don’t say anything about integrity control here, just connections). The structure of the ERP Platform made it quite easy to do this. The required fields have been added to the regular table editor:
The table is ticked that it will be visible in the report configurator. Each field is ticked whether it will be visible to the configurator, since It does not make sense to list all the fields in the reports, and there is nothing to do with the extra garbage in the list.
If the field is an identifier from the universal directory, then you can specify the number of the universal directory (about him a little further). Select the field of another table with which this field is associated.
I will not overload the article with a detailed description of this editor either, you can read it here .
The job of setting up these links is done by the configuration developer. This is already done in the default configuration. At the same time, any user with the necessary access rights can enter the table editor and make the necessary manipulations.
If a new field is added and it will be needed in the reports, click on the checkbox of using this field in the reports.
Now the report configurator will know the links and display a user with a picture like this:
Here we come to paragraph 3 of our requirements. When a new module is installed in the system, its data and structure should automatically be included in the report.
Here, the developer of the module is required to place these checkmarks during development. And the system will pick them up during installation.
For ordinary users, it will look like everything is automatically available after installation.
Now let's go back with a new understanding of CSS and consider the field editor in the report configurator.
It should have the following functionality:
1) Select the table fields, group and sort them.
2) Make up the conditions.
3) Preview the result.
In general, the editor turned out to be
Fields (columns)
First, the user selects the "Reference" - this is the table behind which the functional module of the system stands. Then simply adds the required fields. Fields can be renamed, change their display order. Everything is clear and intuitive.
The second field is the Universal Directory. If a specific directory is associated with this field in the system, the user can specify H - the name, 1 - the first parameter, 2 - the second parameter. And the system, when generating a report, for example, in the Task Status field, instead of the number 92, will show the text “In Work” if H is selected, etc. Everything is very simple.
Grouping
Grouping is just as easy. If it is necessary to sum up the value of a field, select SUM in the Aggregate field. At the same time, the system will put group by itself on all the other fields when forming the request.
Naturally for different data types in the list should display different functions. For example, in the varchar data type it makes no sense to output SUM.
So that everything does not grow in breadth, we had to introduce some abbreviations, for example COU_D - this is the “number of unique elements” (short for sql-th count distinct). But by hovering the mouse over the column heading a legend pops up, which value means what.
For the rest of the column headings there is a similar brief information on the functionality.
Sorting
Sorting fields is also simple and versatile. In the Sorting column, “1” is selected in the field by which the sorting will occur, if it is necessary to sort by one more field in front of it, “2” is selected, if in the reverse order “2 desc”, etc. Everything is very simple.
The great advantage of this approach is that the full pool of fields is always available and we can specify a sorting even across all fields inclusive.
Conditions
Everything consists of containers with conditions. The container is in brackets. A condition is a comparison operation of A and B (A> B, A = B, A <= B ...).
For example: (<condition 1> AND <condition 2> AND <condition 3>) - is a container AND, respectively condition (<condition 1> OR <condition 2> OR <condition 3>) is a container OR. And from this configuration of AND / OR and conditions inside you can make arbitrarily complex string of conditions.
For example, if you need to select data that meets <condition 1> and one of conditions 2 or 3. Then you need conditions 2 and 3 to be combined into a container OR. ( <condition 1> AND (<condition 2> OR <condition 3>))
Here in the container AND there are <condition 1> and container <OR>, and inside the container OR condition 2 and condition 3. For example: ( (A> B) AND ( (A> C) OR (A = B) ))
For the user in the interface, it will look like this:
But it is not enough to create an editor for the arrangement of conditions The condition can be constant or variable. The variable condition is defined simply - it is not filled in the configurator. If the condition value is empty, then you need to ask the user before generating the report. If the user does not enter anything, set it to null.
In order for the input field to look informative, you also need a property such as a name. The name may coincide with the name of the field, but maybe not.
For example, if we entered a constant in the Date field, but left the Status field empty
then the system should ask the user the “Status” before generating the report.
In general: the value is - constant, empty - to ask. Everything here is also simple.
Again, the field can be associated with CSS. In this case, you can specify which of the CSS fields to display and, as in the example above, instead of the “Status” numeric field, a beautiful selection list will be displayed.
There was no need to limit it to 4 levels of nesting as in Bitrix. Conditions can be branched forever.
Procedures
The report configurator is created for non-programmers and knowledge of SQL is not necessary to compile a report. But in the case of a complex report, the data of which are in many different tables with complex links, the programmer will have to be involved.
Such things can be done by writing a procedure in the standard PL / SQL configurator included in the ERP Platform basic programming system, and specifying this procedure in the report. In the procedure, you can make any PL / SQL configuration, i.e. virtually anything.
I will not go into the topic of configuration programming in the ERP Platform, since This topic goes far beyond the scope of this article. In short, it consists of 2 parts: programming the web interface and programming the database, and, respectively, the connections between them. In the database programming part, you can create tables, triggers, procedures of any complexity. As part of the report configurator, we are just interested in a part of the procedure creation system. It implements a full PL / SQL (and even a few chips which are not found anywhere, for example, the image data type, or API operators in triggers, which allow transferring data to external systems directly by event in the database)
But not everything is so simple. Programming in the system is available to system administrators, or to persons who are given these rights by administrators.
The report configurator is available to everyone, and it is impossible to let any user include any procedure in the report.
Therefore, access in the reports to the procedures again had to be integrated into the regular system of assigning rights in the ERP Platform.
To use the procedure in the reporting system, it is necessary to register the rights to it, in the role of the user creating the report.
After the procedure has become available to the user, he can indicate it in the report. You can add fields from the procedure fields. Aggregate and sorting functions are available.
The conditions here are the input parameters of the procedure. They need everything and the order can not be changed. Therefore, editing is limited here. You can only set a constant. But changing the order, deleting, adding new ones will not work.
Scheduled run
Automatic periodic reporting is a very important feature. In my practice, a lot of such reports were created.
The ERP Platform has a regular task scheduler to run the procedures on a schedule, which is programmable as a whole, similar to crown. It turned out to embed and launch reports. Those. Again, use the full-time function of the system and not write a new one. But it turned out not so simple.
With the scheduler in itself is simple. A report is made in the same way as a procedure, only another script is jerking, which is jerking a report and receiving data from it. But in the process, it came to the realization that simply sending the report to html embedded in the letter was completely wrong and had to drop much deeper.
It was decided not to limit the user in the possibilities of receiving the report. In addition to email, you can get a report in the web interface of the account in the notification system, or on the phone in a telegram. In general, to do this for a regular notification system ERP-Platform. But the question arose - how to transfer the body of the report, say in a telegram for example. Yes, there you can send formatted messages, but the reports are different ... it can be the same sheet. In the web interface, the problem is similar. You can of course enter the blob field to the notification, because we do not know the size of the report, but again it would be a crutch that needs to be programmed individually, but we wanted to kill all the hares with one code. Make a universal report delivery system so that it works the same in all channels of information delivery without any modifications. For here a new channel will appear and for it again something will have to be written individually.
The solution was found as follows. A script that runs on a schedule and receives report data, makes a pdf file with a report and puts it on a disk in a client account. Then through the regular notification system produces a mailing with a link to this file. Those.roughly speaking, a notification came, a report of such and such, and a link. The user clicks on the link and the file opens.
Pdf format is universal, will open everywhere on your phone and on your computer.
Ok, there is a delivery medium. Now you need to create recipients.
Configuring recipients
Recipients can be 3 types:
1) Employee (specific person)
2) Division (department according to staffing)
3) Group (working groups of employees)
Accordingly, there can be any combination of these types. This information is populated in the report configurator in the report itself.
The report generation script based on this data will calculate a set of unique recipients and stamp them with links to the report to the standard notification system.
Further distribution will already depend on the individual user settings. Minimum - will come to the web interface. Further, if you are in contact with the employee email type "for notifications" - will go there. If an employee has a telegram connected, he will go there.
But again, not the fact that the report should see everything, but it lies on the disk. Here the problem is solved simply. Each folder on the disk has a system of restricting access rights. Permissions to the Reports folder are cut by default, not even reading. Those.Nobody can see its contents, but the links will open. If someone suddenly needs to view the contents of this folder, the administrator must specify the rights to it individually. So there are no security issues. The principle of “link access” in youtube works. You will not see anything in the list, but you can see the video by knowing the link.
Graphic part
In fact, charts are rarely claimed. In practice, I practically had no requirements to draw diagrams. But such an opportunity should be. In some situations this can be a big plus.
In our configurator, at the moment we have provided 3 types of diagrams:
1) Linear
2) Columnar
3) Circular
4) ... and in the future to add other species as the actual need.
And the interface to them. It turned out to be a very simple
principle. It should be at least one tick - to display the chart, everywhere is empty - do not display. Accordingly, if X is indicated, then these will be the names of the divisions of the X stem. In a linear and columnar column, simply indicate according to which fields to build lines and columns. In a circular one, this can only be done in one of the columns.
Here, in principle, everything is clear and we will not go deep. More details can be found here .
Output formats
This is also an important aspect. At a minimum, the user should be able to print the report with one click. As a maximum, get it as a PDF file or upload to Excel for further processing. Of course, all these options are provided, and here, in principle, everything is also clear and nothing new.
Afterword
I would like to sum up the final idea of what should be the perfect report configurator.
1) It should not hemorrhage users. But simplicity and functionality are contradictory things. Functional things are harder to exploit. May even require special training.
Here you can go through the dolls. On the top layer, make a simple, friendly interface that closes a large pool of needs. When meeting a complex need, there must be a door to the inner layers with the functionality necessary to solve it.
So they built. Externally, a simple interface, but if you encounter a difficult one: here is the PL / SQL editor, do anything and connect it as a ready block to a simple report interface.
2) The report configurator should not be static, but expand automatically with system expansion. It is necessary to do so that when expanding the system, the code of the report configurator itself should not be written, and it automatically picked up all the changes.
In systems that can be configured by the user, for example, in 1C or in ERP-Platform, this approach is the only way out, because what configuration the user will have is not known in advance, and you’re not hiding behind everyone to customize the report configurator for each system configuration.
3) The report configurator should perform well the task of creating reports, and not do related things.
Here the concept is similar to the principle of small “sharp” utilities in unix-like systems. The utility should be able to brilliantly cope with its task, and not be able to do everything for a little bit. Related tasks transfer utilities brilliantly performing them and simply get results from them.
This rule applies entirely to the system. For example, a regular module of access rights should deal with access rights to reports, and everything related to access rights - it can do well, and no matter where in the system.
There is no reason why in reports to build your system of access rights - this is a crutch that will never be as good as a module, it is sharpened.
Similarly with the scheduler. There is no reason to build a scheduled task launch in the report configurator, it is better to entrust it to a system that knows how to do it well.
There is no need to implement an editor for complex queries in the report configurator. This will overload the interface and scare away ordinary users. It is better to use a standard procedure editor for these tasks, which is well able to configure complex queries.
There is no need for the report configurator to make an individual mailing system, when it is possible to build a regular one and do not care about it when a new delivery channel appears. Let the delivery channel operate a module for this intended.
Similar requirements for the configurator - it should cope with its direct tasks as well as possible and be able to be friends with the other modules of the system.
Such an approach in the system makes it possible to debug these narrowly focused functions in the modules, as well as not to waste time on developers to implement partially duplicated functions in different parts of the system.
Abstract idea is clear. But how exactly should it look?
After sitting, thinking, having studied what is on the market in this regard, it is understood that this topic is still far from beaten and very relevant. Therefore, I want to share an example of the "report configurator" that I managed to implement on the basis of the ERP-Platform system.
In my opinion, it turned out to be ideologically close to the ideal, so at the beginning of the article I will try to explain the concept of what should be the report configurator “for people”.
The perfect report configurator is a combination of simplicity and functionality. For example, in 1C functional, but inexperienced in programming 1C people can not use it. Or in bitrix - simple, but can not do the necessary things.
')
The ideal report configurator will be understandable to the simple user and at the same time supports the creation of reports of any complexity level.
Such requirements to the report configurator were formed:
1) Simplicity
It is for non-programmers. A simple manager, far from understanding SQL, will report on data of interest.
2) Versatility
Reports can be generated throughout the system, according to any user data.
3) Automatic connection of the new
When changing the system configuration, if a new field is added to the table, or a new table, or a whole new module is installed, all changes should be automatically available in the configurator.
4) Nesting
There are links between the tables. For example, tasks have properties: type, status, etc. This is stored in special directories, only links in the main table. Since system for non-programmers, we will not load users what is left join. The user selecting the status in the field should receive not a number, but the status of the task “in work”, etc. The immersion depth must be at least one level in the associated tables.
5) Support for complex structures
It happens that the report is complex, it affects a lot of tables, and it is even impossible to get by with a SQL query, but serious PL / SQL processing is needed. To do this, the report configurator provides an indication of the procedure that the programmer will write on the embedded PL / SQL editor of the ERP Platform.
6) Scheduler and report delivery
Often reports are generated on a periodic basis. For example, the user wants to see in the morning reports in the mail, daily, weekly. monthly etc. A task scheduler is needed in which the report is set to automatically generate at a given moment.
In addition to the scheduler, you need a report delivery system. This is not necessarily a letter. Depending on the user's settings, there may be a notification in the interface, sending to a telegram, sending by email, etc. Delivery on all channels of communication with the user.
7) Charts
Formation of charts according to the report
8) Output
Print the result of the report in various formats, such as Excel or PDF.
What is in the world
We started with the study of what is in the world. The study showed that everything is sad. At first there was 1C. Yes, everything is cool there, you can do almost anything, but where is the claim 1, “for ordinary people”? There the devil will break his leg while he understands. The manager, who sees this for the first time in his life, simply melts the brain. In general, cool - but it should be easier and more intuitive.
In the popular crm systems, everything is sad. There is really nothing. The only thing that caught the eye was the Bitrix report configurator. In general, it fits the definition of “for people” and even liked it at first, but I will describe a few flaws as a good example:
1) System narrowness: reports are tied only to certain tables with the main functionality: “Tasks”, “Goods”, “Transactions”, etc. Only 6 pieces! But what to do if I want to analyze how many% of the work time in employee schedules are occupied by tasks of this or that type, or I want to get a list of clients whose email is not filled in contacts. To make reports on data from installed applications there is no speech.
2) Conditions can only be built to a depth of 4 nesting. Was it difficult to make unlimited?
3) You can sort by only one of the fields. And if you need to sort by task type, and inside type by date in descending order?
4) Reports all list in a column alphabetically. They are divided again according to the tables “Tasks”, “Goods”, ... 6 pieces. And if I need my own group, or a subgroup of reports. How to group reports by subject?
5) There is no scheduler to run at intervals. (I at least did not find it). At the same time in the tasks it is.
In general, the system or universal but very complex, or for people - but too narrow, or nothing at all.
Therefore, we go our own way.
Tree
To begin, it was decided to organize the construction of reports in the form of a tree structure.
Any user can create his own branch-node (you can create nested branches, there are no restrictions here), or add your report to other branches accessible to him. When you hover the mouse over a report, a report description pops up. I will not clutter the article with print screens from the tree management system. You can see here .
Secondly, you need access rights. It's not good for everyone to see everything. There are general reports, and there are those that can be seen by individual employees or groups of employees, including there may be entire branches of reports.
Here was the benefit of a regular system of access rights in the ERP-Platform. It has the structuring of access rights down to individual elements of the page, and it was quite easy to build in the reporting system. And after that, and access rights to the procedures for reporting, but more on that below.
Setting up the global structure
Next, you need to build a structure of connections in the system. The system should know which fields have dependencies with other tables (example of a task and its status). This configuration is done once by the configuration developer.
In essence, it is necessary to recreate the Foreign key (I don’t say anything about integrity control here, just connections). The structure of the ERP Platform made it quite easy to do this. The required fields have been added to the regular table editor:
The table is ticked that it will be visible in the report configurator. Each field is ticked whether it will be visible to the configurator, since It does not make sense to list all the fields in the reports, and there is nothing to do with the extra garbage in the list.
If the field is an identifier from the universal directory, then you can specify the number of the universal directory (about him a little further). Select the field of another table with which this field is associated.
I will not overload the article with a detailed description of this editor either, you can read it here .
The job of setting up these links is done by the configuration developer. This is already done in the default configuration. At the same time, any user with the necessary access rights can enter the table editor and make the necessary manipulations.
If a new field is added and it will be needed in the reports, click on the checkbox of using this field in the reports.
Now the report configurator will know the links and display a user with a picture like this:
Here we come to paragraph 3 of our requirements. When a new module is installed in the system, its data and structure should automatically be included in the report.
Here, the developer of the module is required to place these checkmarks during development. And the system will pick them up during installation.
For ordinary users, it will look like everything is automatically available after installation.
Universal Reference
Now make the necessary digression. What is the "Universal Directory"? which I mentioned just above, and to which the whole column is highlighted when editing the table fields. For brevity, we will call it CSS.
First of all, CSS is a way to significantly reduce labor costs for development. It is noticed that the majority of reference books consist of 4 fields:
1) Parameter ID
2) Parameter name
3) The numeric value of the parameter
4) Parameter text value
For example. Reference Currency:
Or reference task status:
And you can give a hundred examples.
They consist of the same set of fields of the same type. Those. it is possible for all such directories to use one editor, one procedure for querying data, etc.
It is not necessary every time you need a reference book, draw interfaces, make links, write queries and perform the like. dreary work.
In general, the US - mega-convenient.
Now let's go back with a new understanding of CSS and consider the field editor in the report configurator.
It should have the following functionality:
1) Select the table fields, group and sort them.
2) Make up the conditions.
3) Preview the result.
In general, the editor turned out to be
Fields (columns)
First, the user selects the "Reference" - this is the table behind which the functional module of the system stands. Then simply adds the required fields. Fields can be renamed, change their display order. Everything is clear and intuitive.
The second field is the Universal Directory. If a specific directory is associated with this field in the system, the user can specify H - the name, 1 - the first parameter, 2 - the second parameter. And the system, when generating a report, for example, in the Task Status field, instead of the number 92, will show the text “In Work” if H is selected, etc. Everything is very simple.
Grouping
Grouping is just as easy. If it is necessary to sum up the value of a field, select SUM in the Aggregate field. At the same time, the system will put group by itself on all the other fields when forming the request.
Naturally for different data types in the list should display different functions. For example, in the varchar data type it makes no sense to output SUM.
So that everything does not grow in breadth, we had to introduce some abbreviations, for example COU_D - this is the “number of unique elements” (short for sql-th count distinct). But by hovering the mouse over the column heading a legend pops up, which value means what.
For the rest of the column headings there is a similar brief information on the functionality.
Sorting
Sorting fields is also simple and versatile. In the Sorting column, “1” is selected in the field by which the sorting will occur, if it is necessary to sort by one more field in front of it, “2” is selected, if in the reverse order “2 desc”, etc. Everything is very simple.
The great advantage of this approach is that the full pool of fields is always available and we can specify a sorting even across all fields inclusive.
Conditions
Everything consists of containers with conditions. The container is in brackets. A condition is a comparison operation of A and B (A> B, A = B, A <= B ...).
For example: (<condition 1> AND <condition 2> AND <condition 3>) - is a container AND, respectively condition (<condition 1> OR <condition 2> OR <condition 3>) is a container OR. And from this configuration of AND / OR and conditions inside you can make arbitrarily complex string of conditions.
For example, if you need to select data that meets <condition 1> and one of conditions 2 or 3. Then you need conditions 2 and 3 to be combined into a container OR. ( <condition 1> AND (<condition 2> OR <condition 3>))
Here in the container AND there are <condition 1> and container <OR>, and inside the container OR condition 2 and condition 3. For example: ( (A> B) AND ( (A> C) OR (A = B) ))
For the user in the interface, it will look like this:
But it is not enough to create an editor for the arrangement of conditions The condition can be constant or variable. The variable condition is defined simply - it is not filled in the configurator. If the condition value is empty, then you need to ask the user before generating the report. If the user does not enter anything, set it to null.
In order for the input field to look informative, you also need a property such as a name. The name may coincide with the name of the field, but maybe not.
For example, if we entered a constant in the Date field, but left the Status field empty
then the system should ask the user the “Status” before generating the report.
In general: the value is - constant, empty - to ask. Everything here is also simple.
Again, the field can be associated with CSS. In this case, you can specify which of the CSS fields to display and, as in the example above, instead of the “Status” numeric field, a beautiful selection list will be displayed.
There was no need to limit it to 4 levels of nesting as in Bitrix. Conditions can be branched forever.
Procedures
The report configurator is created for non-programmers and knowledge of SQL is not necessary to compile a report. But in the case of a complex report, the data of which are in many different tables with complex links, the programmer will have to be involved.
Such things can be done by writing a procedure in the standard PL / SQL configurator included in the ERP Platform basic programming system, and specifying this procedure in the report. In the procedure, you can make any PL / SQL configuration, i.e. virtually anything.
I will not go into the topic of configuration programming in the ERP Platform, since This topic goes far beyond the scope of this article. In short, it consists of 2 parts: programming the web interface and programming the database, and, respectively, the connections between them. In the database programming part, you can create tables, triggers, procedures of any complexity. As part of the report configurator, we are just interested in a part of the procedure creation system. It implements a full PL / SQL (and even a few chips which are not found anywhere, for example, the image data type, or API operators in triggers, which allow transferring data to external systems directly by event in the database)
But not everything is so simple. Programming in the system is available to system administrators, or to persons who are given these rights by administrators.
The report configurator is available to everyone, and it is impossible to let any user include any procedure in the report.
Therefore, access in the reports to the procedures again had to be integrated into the regular system of assigning rights in the ERP Platform.
To use the procedure in the reporting system, it is necessary to register the rights to it, in the role of the user creating the report.
After the procedure has become available to the user, he can indicate it in the report. You can add fields from the procedure fields. Aggregate and sorting functions are available.
The conditions here are the input parameters of the procedure. They need everything and the order can not be changed. Therefore, editing is limited here. You can only set a constant. But changing the order, deleting, adding new ones will not work.
An example of using the procedure in a report in the ERP Platform
Under the spoiler, I’ll give an example if it will be interesting to someone what the use of procedures in the report in our editor looks like.
Here, for example, the “APPLICATION_List” procedure, which forms a list of applications according to the conditions in the interface of the list of applications:
If none of the conditions are filled, i.e. All null procedure will issue a list of applications with the status of "received."
If you specify it in the report, and select similar fields, as in the interface with the list of applications
Then we will get a similar result in the report.
Here in the dogonku even the code of this procedure in the PL / SQL interface of the ERP-Platform. But this is already very deep beyond the scope of this article and is not intended for ordinary users. Here is just an example of what such things can be done.
In general, the entire configuration of the ERP-Platform is written in its own embedded language, respectively, all this is available in the report configurator, as well as the users themselves can edit and modernize anything.
Here, for example, the “APPLICATION_List” procedure, which forms a list of applications according to the conditions in the interface of the list of applications:
If none of the conditions are filled, i.e. All null procedure will issue a list of applications with the status of "received."
If you specify it in the report, and select similar fields, as in the interface with the list of applications
Then we will get a similar result in the report.
Here in the dogonku even the code of this procedure in the PL / SQL interface of the ERP-Platform. But this is already very deep beyond the scope of this article and is not intended for ordinary users. Here is just an example of what such things can be done.
In general, the entire configuration of the ERP-Platform is written in its own embedded language, respectively, all this is available in the report configurator, as well as the users themselves can edit and modernize anything.
Procedure APPLICATION_List
Scheduled run
Automatic periodic reporting is a very important feature. In my practice, a lot of such reports were created.
The ERP Platform has a regular task scheduler to run the procedures on a schedule, which is programmable as a whole, similar to crown. It turned out to embed and launch reports. Those. Again, use the full-time function of the system and not write a new one. But it turned out not so simple.
With the scheduler in itself is simple. A report is made in the same way as a procedure, only another script is jerking, which is jerking a report and receiving data from it. But in the process, it came to the realization that simply sending the report to html embedded in the letter was completely wrong and had to drop much deeper.
It was decided not to limit the user in the possibilities of receiving the report. In addition to email, you can get a report in the web interface of the account in the notification system, or on the phone in a telegram. In general, to do this for a regular notification system ERP-Platform. But the question arose - how to transfer the body of the report, say in a telegram for example. Yes, there you can send formatted messages, but the reports are different ... it can be the same sheet. In the web interface, the problem is similar. You can of course enter the blob field to the notification, because we do not know the size of the report, but again it would be a crutch that needs to be programmed individually, but we wanted to kill all the hares with one code. Make a universal report delivery system so that it works the same in all channels of information delivery without any modifications. For here a new channel will appear and for it again something will have to be written individually.
The solution was found as follows. A script that runs on a schedule and receives report data, makes a pdf file with a report and puts it on a disk in a client account. Then through the regular notification system produces a mailing with a link to this file. Those.roughly speaking, a notification came, a report of such and such, and a link. The user clicks on the link and the file opens.
Pdf format is universal, will open everywhere on your phone and on your computer.
Ok, there is a delivery medium. Now you need to create recipients.
Configuring recipients
Recipients can be 3 types:
1) Employee (specific person)
2) Division (department according to staffing)
3) Group (working groups of employees)
Accordingly, there can be any combination of these types. This information is populated in the report configurator in the report itself.
The report generation script based on this data will calculate a set of unique recipients and stamp them with links to the report to the standard notification system.
Further distribution will already depend on the individual user settings. Minimum - will come to the web interface. Further, if you are in contact with the employee email type "for notifications" - will go there. If an employee has a telegram connected, he will go there.
But again, not the fact that the report should see everything, but it lies on the disk. Here the problem is solved simply. Each folder on the disk has a system of restricting access rights. Permissions to the Reports folder are cut by default, not even reading. Those.Nobody can see its contents, but the links will open. If someone suddenly needs to view the contents of this folder, the administrator must specify the rights to it individually. So there are no security issues. The principle of “link access” in youtube works. You will not see anything in the list, but you can see the video by knowing the link.
Graphic part
In fact, charts are rarely claimed. In practice, I practically had no requirements to draw diagrams. But such an opportunity should be. In some situations this can be a big plus.
In our configurator, at the moment we have provided 3 types of diagrams:
1) Linear
2) Columnar
3) Circular
4) ... and in the future to add other species as the actual need.
And the interface to them. It turned out to be a very simple
principle. It should be at least one tick - to display the chart, everywhere is empty - do not display. Accordingly, if X is indicated, then these will be the names of the divisions of the X stem. In a linear and columnar column, simply indicate according to which fields to build lines and columns. In a circular one, this can only be done in one of the columns.
Here, in principle, everything is clear and we will not go deep. More details can be found here .
Output formats
This is also an important aspect. At a minimum, the user should be able to print the report with one click. As a maximum, get it as a PDF file or upload to Excel for further processing. Of course, all these options are provided, and here, in principle, everything is also clear and nothing new.
Afterword
I would like to sum up the final idea of what should be the perfect report configurator.
1) It should not hemorrhage users. But simplicity and functionality are contradictory things. Functional things are harder to exploit. May even require special training.
Here you can go through the dolls. On the top layer, make a simple, friendly interface that closes a large pool of needs. When meeting a complex need, there must be a door to the inner layers with the functionality necessary to solve it.
So they built. Externally, a simple interface, but if you encounter a difficult one: here is the PL / SQL editor, do anything and connect it as a ready block to a simple report interface.
2) The report configurator should not be static, but expand automatically with system expansion. It is necessary to do so that when expanding the system, the code of the report configurator itself should not be written, and it automatically picked up all the changes.
In systems that can be configured by the user, for example, in 1C or in ERP-Platform, this approach is the only way out, because what configuration the user will have is not known in advance, and you’re not hiding behind everyone to customize the report configurator for each system configuration.
3) The report configurator should perform well the task of creating reports, and not do related things.
Here the concept is similar to the principle of small “sharp” utilities in unix-like systems. The utility should be able to brilliantly cope with its task, and not be able to do everything for a little bit. Related tasks transfer utilities brilliantly performing them and simply get results from them.
This rule applies entirely to the system. For example, a regular module of access rights should deal with access rights to reports, and everything related to access rights - it can do well, and no matter where in the system.
There is no reason why in reports to build your system of access rights - this is a crutch that will never be as good as a module, it is sharpened.
Similarly with the scheduler. There is no reason to build a scheduled task launch in the report configurator, it is better to entrust it to a system that knows how to do it well.
There is no need to implement an editor for complex queries in the report configurator. This will overload the interface and scare away ordinary users. It is better to use a standard procedure editor for these tasks, which is well able to configure complex queries.
There is no need for the report configurator to make an individual mailing system, when it is possible to build a regular one and do not care about it when a new delivery channel appears. Let the delivery channel operate a module for this intended.
Similar requirements for the configurator - it should cope with its direct tasks as well as possible and be able to be friends with the other modules of the system.
Such an approach in the system makes it possible to debug these narrowly focused functions in the modules, as well as not to waste time on developers to implement partially duplicated functions in different parts of the system.
Source: https://habr.com/ru/post/331884/
All Articles