What should be the ideal report configurator
I received a request from the client: “we want a report configurator.” So that simple employees who are far from SQL can compose all kinds of reports necessary for them on any data in the system.
Abstract idea is clear. But how exactly should it look like?
After sitting, thinking, having studied what is on the market in this regard, an understanding has come that this topic is far from being beaten and very relevant. Therefore, I want to share an example of a “report configurator”, which turned out to be implemented on the basis of the ERP-Platform system.
In my opinion, it turned out ideologically close to ideal, so at the beginning of the article I will try to tell the concept of what the report configurator should be “for people”.
The ideal report configurator is a combination of simplicity and functionality. For example, in 1C, a functional but inexperienced in 1C programming person will not be able to use it. Or in bitrix - simple, but can not do the necessary things.
The ideal report configurator will be understood by a simple user and at the same time supports the creation of reports of any level of complexity.
The following requirements have been formed for the report configurator:
1) Simplicity
It is for non-programmers. A simple manager, far from understanding SQL, will report on the data of interest.
2) Versatility
Reports can be generated throughout the system, according to any user data.
3) Auto connect 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, then all changes should automatically be available in the configurator.
4) Nesting
There are relationships between tables. For example, tasks have properties: type, status, etc. This is stored in special directories, only links in the main table. Because system for non-programmers, we will not load users what is left join. Having selected the status in the field, the user should receive not the number, but the status of the task “in work”, etc. The depth of immersion must be at least one level in the related tables.
5) Support for complex structures
It happens that the report is complex, affects many tables, and you can’t even get by with a SQL query, and 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 built-in PL / SQL editor of the ERP Platform.
6) Scheduler and report delivery.
Often reports are generated on a periodic basis. For example, a user wants to see daily, weekly reports in the mail in the morning. monthly, etc. We need a task scheduler in which the report is set to automatically generate at a given moment.
In addition to the scheduler, a report delivery system is needed. This is not necessarily a letter. Depending on the user's settings, there may be a notification in the interface, sending to telegram, sending by email, etc. Delivery on all channels of communication with the user.
7) Charts
Formation of charts according to the report data
8) Output of the result Output the
result of the report in various formats, for example Excel or PDF.
What is in the world
We started by studying what the world has. The study showed that everything is sad. First was 1C. Yes, everything is cool there, you can do almost anything, but where is point 1, “for ordinary people”? There the devil will break his leg until he understands it. For a manager who sees it for the first time in his life, his brain will simply melt. In general, cool - but it should be simpler and more intuitive.
In popular crm systems, everything is sad. There is really nothing. The only thing that attracted attention was the Bitrix report configurator. In general, it fits the definition of "for people" and at first I even liked it, but as an illustrative example I will describe several shortcomings:
1) Narrowness of the system: 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 much% of the working time in employee schedules is occupied by tasks of this or that type, or I want to get a list of clients who do not have email in contacts. To make reports on data from installed applications, there is no question.
2) Conditions can only be built to a depth of 4 nesting. Was it really hard to do unlimited?
3) You can only sort by one of the fields. And if you need to sort by type of task, and inside the type by date in descending order?
4) Reports are all listed in a column alphabetically. Again they are divided 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 topic?
5) There is no scheduler to run at intervals. (I at least have not found). Moreover, he has tasks.
In general, the system is either universal but very complex, either for people - but too narrow, or nothing at all.
Therefore, we go our own way.
Tree
For starters, 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), or add your report to other branches available to him. When you mouse over a report, a description of the report pops up. I will not clutter up the article with print-screens from the tree management system. You can see it here .
Secondly, access rights are needed. It’s not good that everyone sees everything. There are general reports, but there are those that individual employees or groups of employees can see, including whole branches of reports.
Here, the staffing system of access rights in the ERP Platform turned out to be a boon. It has a structuring of access rights down to individual page elements and it was quite easy to integrate a reporting system into it. And after that, access rights to procedures for reports, but more on that below.
Setting the global structure
Next, you need to build the structure of the links in the system. The system should know which fields have dependencies with other tables (an example of a task and its status). This setup is done once by the configuration designer.
In essence, it is necessary to recreate the Foreign key (I’m not talking about integrity control here, only communications). The structure of the ERP Platform made it fairly easy to do. The necessary fields have been added to the regular table editor:
The table is checked that it will be visible in the report configurator. Each field is checked if it will be visible to the configurator, because not all fields make sense in the reports, and there’s nothing to do with excess 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 it a bit later). Select a field in another table that this field is associated with.
A detailed description of this editor, too, will not clutter up the article, you can read here .
The configuration developer does the work of configuring these relationships. In the default configuration, this is already done. At the same time, any user with the necessary access rights can go to the table editor and do the necessary manipulations.
If a new field is added and it will be needed in the reports, click on the checkbox for using this field in reports.
Now the report configurator will know the connections and display a picture like this when the user selects a field:
Here we come to point 3 of our requirements. When installing a new module in the system, its data and structure should automatically be included in the report.
Here, the developer of the module is required to check these boxes during development. And the system will pick them up during installation.
For ordinary users, it will look like everything automatically became 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 table fields, group and sort them.
2) Make conditions.
3) Preview the result.
In general, the editor turned out such
Fields (columns)
First, the user selects the "Reference" - this is the table behind which the functional module of the system stands. Then it simply adds the required fields. Fields can be renamed, their output order can be changed. Everything is clear and intuitive.
The second field is the Universal Directory. If a specific directory is associated with this field in the system, then the user can specify H - 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 display the text "In Work" if H is selected, etc. Everything is very simple.
Grouping Grouping
is just as easy. If you need to sum the value of a field, select SUM in the Aggregate field. In this case, the system will put group by itself on all other fields when forming the request.
Naturally for different types of data in the list different functions should be displayed. For example, in the varchar data type, it makes no sense to output SUM.
To keep things from expanding in breadth, I had to introduce some abbreviations, for example, COU_D is the “number of unique elements” (the abbreviation for sql-count distinct). But when you hover over the column heading, a legend pops up, what is the meaning of what.
For the remaining 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 sorting will be performed, if it is necessary to sort by another field, “2” is selected opposite it, if in the reverse order “2 desc”, etc. Everything is very simple.
A big advantage of this approach: the full pool of fields is always available and we can specify sorting by at least all fields inclusively.
Conditions
Everything consists of containers with conditions. A container is in parentheses. The condition is the operation of comparing A and B (A> B, A = B, A <= B ...).
For example: (<condition 1> AND <condition 2> AND <condition 3>) - is a container AND, respectively, the condition (<condition 1> OR <condition 2> OR <condition 3>) is an OR container. And from such an AND / OR configuration and conditions inside you can make arbitrarily complex sequences of conditions.
For example, if you need to select data that meets <condition 1> and one of conditions 2 or 3. Then conditions 2 and 3 need to be combined into the OR container. ( <condition 1> AND (<condition 2> OR <condition 3>))
Here in the AND container there are <condition 1> and the <OR> container, and inside the OR container, condition 2 and condition 3. For example: ( (A> B) AND ( (A> C) OR (A = C) ))
For the user in the interface it will look like this:
But it’s not enough just to create an editor for setting the conditions. The condition can be a constant or a variable. A 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 a report. If the user does not enter anything - set to null.
For the input field to look informative, you also need a property such as a name. The name may match the name of the field, or it may not.
For example, if we entered a constant in the "Date" field, but left the "Status" field empty,
then the system should ask the "Status" of the user before generating the report.
In general: the value is - constant, empty - ask. Everything here is also simple.
Again, the field may be associated with the CSS. In this case, you can specify which of the CSS fields to display and as in the example above, a beautiful selection list will be displayed instead of the number field “Status”.
There was no need, as in Bitrix, to limit 4 levels of nesting. Conditions can be branched endlessly.
Procedures
The report configurator is designed for non-programmers and knowledge of SQL to compile a report is not necessary. But in the case of a complex report, the data of which is in many different tables with complex relationships, the programmer will have to attract.
Such things can be done by writing the procedure in the standard PL / SQL configurator, which is part of the base programming system of the ERP Platform, and indicating this procedure in the report. In the procedure, you can make any PL / SQL configuration, i.e. virtually anything.
I will not delve into the topic of configuration programming in the ERP Platform, as This topic goes far beyond the scope of this article. Briefly - consists of 2 parts: web interface programming and database programming, and, accordingly, the relationship between them. In terms of database programming, you can create tables, triggers, procedures of any complexity. As part of the report configurator, here we are just interested in the part of the system for creating procedures. It implements full-fledged PL / SQL (and even a few chips that are not found anywhere, for example, the image data type, or API operators in triggers that allow direct transfer of data to external systems directly after an event in the database)
But not everything is so simple. Programming in the system is available to system administrators, or to persons to whom these rights have been granted by administrators.
The report configurator is accessible to everyone, and you cannot let any user include any procedure in the report.
Therefore, access to the procedures in the reports again had to be integrated into the regular system for assigning rights in the ERP Platform.
To use the procedure in the reporting system, you must register the rights to it in the Roles of the user who creates the report.
After the procedure has become available to the user, he can indicate it in the report. You can add fields from the fields of the procedure. Aggregate and sort functions are available.
But the conditions here are the input parameters of the procedure. They are all needed and the order cannot 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 start
Automatically receiving periodic reports is a very important feature. In my practice, a lot of such reports have been created.
The ERP Platform has a full-time task scheduler for launching scheduled procedures, programmed as a whole like a crown. It turned out to integrate the statement of launching reports into it. Those. again zayuzat regular function of the system and not write new. But it was not so simple.
With the planner itself is simple. The report is set up similarly to the procedure, only another script twitches, which pulls the report and receives data from it. But in the process, it came to the realization that just sending the report in html built into the letter was completely wrong and I had to drip much deeper.
It was decided not to limit the user in the possibilities of obtaining a 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 telegram. In general, to establish a full-time notification system for the ERP Platform for this. But the question arose - how to convey the body of the report, say in a telegram for example. Yes, you can send formatted messages there, but the reports are different ... it could be that sheet. The web interface has a similar problem. You can of course enter the blob field for notification, because we don’t know the size of the report, but again it would be a crutch that needs to be programmed individually, but I wanted to kill all the birds with one stone. To make a universal report delivery system so that it works the same in all information delivery channels without any modifications.
The solution was found as follows. The script that runs on schedule and receives the report data, makes a pdf file with the report and puts it on disk in the client’s account. Then, through a standard notification system, it sends out newsletters with a link to this file. Those. roughly speaking, a notification came, a report such and such, and a link. The user clicks on the link and the file opens to him.
The pdf format is universal, it will open everywhere on the phone and on the computer.
Ok, there’s a delivery vehicle. Now we need to form recipients.
Setting recipients
Recipients can be of 3 types:
1) Employee (specific person)
2) Unit (department according to the staff list)
3) Group (working groups of employees)
Accordingly, there may be any combination of these species. This information is filled in the report configurator in the report itself.
The script for generating a report based on this data will calculate a set of unique recipients and stamp them links to the report in a regular notification system.
Further, the distribution will already depend on the individual settings of users. Minimum - will come to the web interface. Further, if there is an email with the type “for notifications” in the employee’s contacts, he will go there. If the employee has a telegram connected, he will go there too.
But again, not the fact that everyone should see the report, but it lies on the disk. Here the problem is solved simply. Each folder on the disk has a system for restricting access rights. The rights to the Reports folder are cut off by default completely, not even reading. Those. no one will be able to see its contents, but links will open. If someone suddenly needs to view the contents of this folder, the administrator must user roles individually register the rights to it. So there are no security issues here. The principle works as in youtube "access by reference." You will not see anything in the list, but you can watch the video knowing the link.
The grafical part
In fact, charts are rarely needed. In practice, I practically did not meet the requirements to draw diagrams. But such an opportunity should be. In some situations, this can be a big plus.
At present, 3 types of diagrams are provided in our configurator:
1) Linear
2) Columnar
3) Circular
4) ... and in the future add other types as needed.
And the interface to them. It turned out very simple
The principle is this. There is at least one check mark - display a chart, everywhere empty - do not display. Accordingly, if X is indicated, then these will be the names of the divisions along the X axis. In the linear and columnar boxes, simply ticks indicate the data of which fields to draw lines and columns. In a circular, this can be done only on one of the columns.
Here, in principle, everything is clear and we will not go deep. More details can be found here .
Data 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. All these options are certainly provided, and here, in principle, everything is clear and nothing new.
Afterword
I would like to summarize the idea of what an ideal report configurator should be.
1) It should not hemorrhoids users. But simplicity and functionality are conflicting things. A functional thing has more difficult operation. Special training may even be required.
Here you can go by nesting dolls. On the top layer, make a simple, friendly interface that closes a large pool of needs. When meeting a complex need, there should be a door to the inner layers with the functionality necessary to solve it.
So they built it. Outwardly, it’s a simple interface, but if it’s complicated: here is the PL / SQL editor - do whatever you want and plug it in a ready-made block into a simple report interface.
2) The report configurator should not be static, but expand automatically with the extension of the system. It is necessary to make sure that when expanding the system, the code of the report configurator itself should not be written down, but it would automatically pick up all the changes.
In systems with the possibility of user configuration, 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 will not chase everyone to customize the report configurator for each system configuration.
3) The report configurator should perform the task of reporting well, and not deal with related things.
Here the concept is similar to the principle of small “sharp” utilitieson unix-like systems. The utility should be able to brilliantly cope with its task, and not be able to do everything a little bit. Related tasks to transfer to utilities brilliantly performing them and just get results from them.
This rule applies fully to the system. For example, a staff module of access rights should deal with access rights to reports, and as for access rights, it can do well, and no matter where in the system.
Why not build your own system of access rights in reports - this is a crutch that will never be as good as a module, ground for it.
Similarly with the scheduler. Why not build scheduled tasks in the report configurator, it’s better to entrust this to a system that knows how to do this well.
There is no need to implement an editor for complex queries in the report configurator. This will simplify the interface and scare away ordinary users. It’s better to use a standard procedure editor for these tasks, which can configure complex queries well.
There is no need for the report configurator to make an individual distribution system when it is possible to use a regular one and when a new delivery channel appears, does not care about it. Let the delivery module operate the module intended for this.
The same requirements apply to the configurator - it should cope with its direct tasks as well as possible and be able to be friends with other modules of the system.
Such an approach in the system allows you to fine-tune these narrowly focused functions in modules, as well as not to spend extra time on developers implementing partially duplicated functions in various parts of the system.
Abstract idea is clear. But how exactly should it look like?
After sitting, thinking, having studied what is on the market in this regard, an understanding has come that this topic is far from being beaten and very relevant. Therefore, I want to share an example of a “report configurator”, which turned out to be implemented on the basis of the ERP-Platform system.
In my opinion, it turned out ideologically close to ideal, so at the beginning of the article I will try to tell the concept of what the report configurator should be “for people”.
The ideal report configurator is a combination of simplicity and functionality. For example, in 1C, a functional but inexperienced in 1C programming person will not be able to use it. Or in bitrix - simple, but can not do the necessary things.
The ideal report configurator will be understood by a simple user and at the same time supports the creation of reports of any level of complexity.
The following requirements have been formed for the report configurator:
1) Simplicity
It is for non-programmers. A simple manager, far from understanding SQL, will report on the data of interest.
2) Versatility
Reports can be generated throughout the system, according to any user data.
3) Auto connect 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, then all changes should automatically be available in the configurator.
4) Nesting
There are relationships between tables. For example, tasks have properties: type, status, etc. This is stored in special directories, only links in the main table. Because system for non-programmers, we will not load users what is left join. Having selected the status in the field, the user should receive not the number, but the status of the task “in work”, etc. The depth of immersion must be at least one level in the related tables.
5) Support for complex structures
It happens that the report is complex, affects many tables, and you can’t even get by with a SQL query, and 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 built-in PL / SQL editor of the ERP Platform.
6) Scheduler and report delivery.
Often reports are generated on a periodic basis. For example, a user wants to see daily, weekly reports in the mail in the morning. monthly, etc. We need a task scheduler in which the report is set to automatically generate at a given moment.
In addition to the scheduler, a report delivery system is needed. This is not necessarily a letter. Depending on the user's settings, there may be a notification in the interface, sending to telegram, sending by email, etc. Delivery on all channels of communication with the user.
7) Charts
Formation of charts according to the report data
8) Output of the result Output the
result of the report in various formats, for example Excel or PDF.
What is in the world
We started by studying what the world has. The study showed that everything is sad. First was 1C. Yes, everything is cool there, you can do almost anything, but where is point 1, “for ordinary people”? There the devil will break his leg until he understands it. For a manager who sees it for the first time in his life, his brain will simply melt. In general, cool - but it should be simpler and more intuitive.
In popular crm systems, everything is sad. There is really nothing. The only thing that attracted attention was the Bitrix report configurator. In general, it fits the definition of "for people" and at first I even liked it, but as an illustrative example I will describe several shortcomings:
1) Narrowness of the system: 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 much% of the working time in employee schedules is occupied by tasks of this or that type, or I want to get a list of clients who do not have email in contacts. To make reports on data from installed applications, there is no question.
2) Conditions can only be built to a depth of 4 nesting. Was it really hard to do unlimited?
3) You can only sort by one of the fields. And if you need to sort by type of task, and inside the type by date in descending order?
4) Reports are all listed in a column alphabetically. Again they are divided 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 topic?
5) There is no scheduler to run at intervals. (I at least have not found). Moreover, he has tasks.
In general, the system is either universal but very complex, either for people - but too narrow, or nothing at all.
Therefore, we go our own way.
Tree
For starters, 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), or add your report to other branches available to him. When you mouse over a report, a description of the report pops up. I will not clutter up the article with print-screens from the tree management system. You can see it here .
Secondly, access rights are needed. It’s not good that everyone sees everything. There are general reports, but there are those that individual employees or groups of employees can see, including whole branches of reports.
Here, the staffing system of access rights in the ERP Platform turned out to be a boon. It has a structuring of access rights down to individual page elements and it was quite easy to integrate a reporting system into it. And after that, access rights to procedures for reports, but more on that below.
Setting the global structure
Next, you need to build the structure of the links in the system. The system should know which fields have dependencies with other tables (an example of a task and its status). This setup is done once by the configuration designer.
In essence, it is necessary to recreate the Foreign key (I’m not talking about integrity control here, only communications). The structure of the ERP Platform made it fairly easy to do. The necessary fields have been added to the regular table editor:
The table is checked that it will be visible in the report configurator. Each field is checked if it will be visible to the configurator, because not all fields make sense in the reports, and there’s nothing to do with excess 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 it a bit later). Select a field in another table that this field is associated with.
A detailed description of this editor, too, will not clutter up the article, you can read here .
The configuration developer does the work of configuring these relationships. In the default configuration, this is already done. At the same time, any user with the necessary access rights can go to the table editor and do the necessary manipulations.
If a new field is added and it will be needed in the reports, click on the checkbox for using this field in reports.
Now the report configurator will know the connections and display a picture like this when the user selects a field:
Here we come to point 3 of our requirements. When installing a new module in the system, its data and structure should automatically be included in the report.
Here, the developer of the module is required to check these boxes during development. And the system will pick them up during installation.
For ordinary users, it will look like everything automatically became available after installation.
Universal Guide
Now let's make the necessary digression. What is the Universal Directory? which I mentioned just above, and to which an entire column is highlighted when editing table fields. For brevity, we call it CSS.
First of all, CSS is a way to significantly reduce labor costs for development. It is noticed that most directories consist of 4 fields:
1) Parameter ID
2) Parameter name
3) Numeric value of parameter
4) Text value of parameter
For example. Directory Currency:
Or directory Status of the task:
And you can give a hundred examples.
They consist of the same set of fields of the same type. Those. you can use one editor for all such directories, one procedure for requesting data, etc.
It’s not necessary every time you need a guide to draw interfaces, make links, write requests and execute etc. dreary work.
In general, CSS is 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 table fields, group and sort them.
2) Make conditions.
3) Preview the result.
In general, the editor turned out such
Fields (columns)
First, the user selects the "Reference" - this is the table behind which the functional module of the system stands. Then it simply adds the required fields. Fields can be renamed, their output order can be changed. Everything is clear and intuitive.
The second field is the Universal Directory. If a specific directory is associated with this field in the system, then the user can specify H - 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 display the text "In Work" if H is selected, etc. Everything is very simple.
Grouping Grouping
is just as easy. If you need to sum the value of a field, select SUM in the Aggregate field. In this case, the system will put group by itself on all other fields when forming the request.
Naturally for different types of data in the list different functions should be displayed. For example, in the varchar data type, it makes no sense to output SUM.
To keep things from expanding in breadth, I had to introduce some abbreviations, for example, COU_D is the “number of unique elements” (the abbreviation for sql-count distinct). But when you hover over the column heading, a legend pops up, what is the meaning of what.
For the remaining 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 sorting will be performed, if it is necessary to sort by another field, “2” is selected opposite it, if in the reverse order “2 desc”, etc. Everything is very simple.
A big advantage of this approach: the full pool of fields is always available and we can specify sorting by at least all fields inclusively.
Conditions
Everything consists of containers with conditions. A container is in parentheses. The condition is the operation of comparing A and B (A> B, A = B, A <= B ...).
For example: (<condition 1> AND <condition 2> AND <condition 3>) - is a container AND, respectively, the condition (<condition 1> OR <condition 2> OR <condition 3>) is an OR container. And from such an AND / OR configuration and conditions inside you can make arbitrarily complex sequences of conditions.
For example, if you need to select data that meets <condition 1> and one of conditions 2 or 3. Then conditions 2 and 3 need to be combined into the OR container. ( <condition 1> AND (<condition 2> OR <condition 3>))
Here in the AND container there are <condition 1> and the <OR> container, and inside the OR container, condition 2 and condition 3. For example: ( (A> B) AND ( (A> C) OR (A = C) ))
For the user in the interface it will look like this:
But it’s not enough just to create an editor for setting the conditions. The condition can be a constant or a variable. A 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 a report. If the user does not enter anything - set to null.
For the input field to look informative, you also need a property such as a name. The name may match the name of the field, or it may not.
For example, if we entered a constant in the "Date" field, but left the "Status" field empty,
then the system should ask the "Status" of the user before generating the report.
In general: the value is - constant, empty - ask. Everything here is also simple.
Again, the field may be associated with the CSS. In this case, you can specify which of the CSS fields to display and as in the example above, a beautiful selection list will be displayed instead of the number field “Status”.
There was no need, as in Bitrix, to limit 4 levels of nesting. Conditions can be branched endlessly.
Procedures
The report configurator is designed for non-programmers and knowledge of SQL to compile a report is not necessary. But in the case of a complex report, the data of which is in many different tables with complex relationships, the programmer will have to attract.
Such things can be done by writing the procedure in the standard PL / SQL configurator, which is part of the base programming system of the ERP Platform, and indicating this procedure in the report. In the procedure, you can make any PL / SQL configuration, i.e. virtually anything.
I will not delve into the topic of configuration programming in the ERP Platform, as This topic goes far beyond the scope of this article. Briefly - consists of 2 parts: web interface programming and database programming, and, accordingly, the relationship between them. In terms of database programming, you can create tables, triggers, procedures of any complexity. As part of the report configurator, here we are just interested in the part of the system for creating procedures. It implements full-fledged PL / SQL (and even a few chips that are not found anywhere, for example, the image data type, or API operators in triggers that allow direct transfer of data to external systems directly after an event in the database)
But not everything is so simple. Programming in the system is available to system administrators, or to persons to whom these rights have been granted by administrators.
The report configurator is accessible to everyone, and you cannot let any user include any procedure in the report.
Therefore, access to the procedures in the reports again had to be integrated into the regular system for assigning rights in the ERP Platform.
To use the procedure in the reporting system, you must register the rights to it in the Roles of the user who creates the report.
After the procedure has become available to the user, he can indicate it in the report. You can add fields from the fields of the procedure. Aggregate and sort functions are available.
But the conditions here are the input parameters of the procedure. They are all needed and the order cannot 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 a procedure in a report in an ERP Platform
I’ll give an example under the spoiler if someone will be interested in how the use of procedures in the report looks in our editor.
For example, the procedure "APPLICATION_List", forming a list of applications for conditions in the interface of the application list:
If none of the conditions is filled, i.e. all null procedure will produce a list of applications with the status of "received".
If you specify it in the report, and select the same fields as in the interface with the list of applications,
then we will get a similar result in the report.
Here is another code for this procedure in the PL / SQL interface of the ERP Platform. But this is already very deeply beyond the scope of this article and is not intended for ordinary users. Here, just as an example, that such things can be done.
In general, the entire configuration of the ERP Platform is written in its own built-in language, respectively, all this is available in the report configurator, and users themselves can also edit and upgrade anything.
For example, the procedure "APPLICATION_List", forming a list of applications for conditions in the interface of the application list:
If none of the conditions is filled, i.e. all null procedure will produce a list of applications with the status of "received".
If you specify it in the report, and select the same fields as in the interface with the list of applications,
then we will get a similar result in the report.
Here is another code for this procedure in the PL / SQL interface of the ERP Platform. But this is already very deeply beyond the scope of this article and is not intended for ordinary users. Here, just as an example, that such things can be done.
In general, the entire configuration of the ERP Platform is written in its own built-in language, respectively, all this is available in the report configurator, and users themselves can also edit and upgrade anything.
Procedure APPLICATION_List
Scheduled start
Automatically receiving periodic reports is a very important feature. In my practice, a lot of such reports have been created.
The ERP Platform has a full-time task scheduler for launching scheduled procedures, programmed as a whole like a crown. It turned out to integrate the statement of launching reports into it. Those. again zayuzat regular function of the system and not write new. But it was not so simple.
With the planner itself is simple. The report is set up similarly to the procedure, only another script twitches, which pulls the report and receives data from it. But in the process, it came to the realization that just sending the report in html built into the letter was completely wrong and I had to drip much deeper.
It was decided not to limit the user in the possibilities of obtaining a 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 telegram. In general, to establish a full-time notification system for the ERP Platform for this. But the question arose - how to convey the body of the report, say in a telegram for example. Yes, you can send formatted messages there, but the reports are different ... it could be that sheet. The web interface has a similar problem. You can of course enter the blob field for notification, because we don’t know the size of the report, but again it would be a crutch that needs to be programmed individually, but I wanted to kill all the birds with one stone. To make a universal report delivery system so that it works the same in all information delivery channels without any modifications.
The solution was found as follows. The script that runs on schedule and receives the report data, makes a pdf file with the report and puts it on disk in the client’s account. Then, through a standard notification system, it sends out newsletters with a link to this file. Those. roughly speaking, a notification came, a report such and such, and a link. The user clicks on the link and the file opens to him.
The pdf format is universal, it will open everywhere on the phone and on the computer.
Ok, there’s a delivery vehicle. Now we need to form recipients.
Setting recipients
Recipients can be of 3 types:
1) Employee (specific person)
2) Unit (department according to the staff list)
3) Group (working groups of employees)
Accordingly, there may be any combination of these species. This information is filled in the report configurator in the report itself.
The script for generating a report based on this data will calculate a set of unique recipients and stamp them links to the report in a regular notification system.
Further, the distribution will already depend on the individual settings of users. Minimum - will come to the web interface. Further, if there is an email with the type “for notifications” in the employee’s contacts, he will go there. If the employee has a telegram connected, he will go there too.
But again, not the fact that everyone should see the report, but it lies on the disk. Here the problem is solved simply. Each folder on the disk has a system for restricting access rights. The rights to the Reports folder are cut off by default completely, not even reading. Those. no one will be able to see its contents, but links will open. If someone suddenly needs to view the contents of this folder, the administrator must user roles individually register the rights to it. So there are no security issues here. The principle works as in youtube "access by reference." You will not see anything in the list, but you can watch the video knowing the link.
The grafical part
In fact, charts are rarely needed. In practice, I practically did not meet the requirements to draw diagrams. But such an opportunity should be. In some situations, this can be a big plus.
At present, 3 types of diagrams are provided in our configurator:
1) Linear
2) Columnar
3) Circular
4) ... and in the future add other types as needed.
And the interface to them. It turned out very simple
The principle is this. There is at least one check mark - display a chart, everywhere empty - do not display. Accordingly, if X is indicated, then these will be the names of the divisions along the X axis. In the linear and columnar boxes, simply ticks indicate the data of which fields to draw lines and columns. In a circular, this can be done only on one of the columns.
Here, in principle, everything is clear and we will not go deep. More details can be found here .
Data 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. All these options are certainly provided, and here, in principle, everything is clear and nothing new.
Afterword
I would like to summarize the idea of what an ideal report configurator should be.
1) It should not hemorrhoids users. But simplicity and functionality are conflicting things. A functional thing has more difficult operation. Special training may even be required.
Here you can go by nesting dolls. On the top layer, make a simple, friendly interface that closes a large pool of needs. When meeting a complex need, there should be a door to the inner layers with the functionality necessary to solve it.
So they built it. Outwardly, it’s a simple interface, but if it’s complicated: here is the PL / SQL editor - do whatever you want and plug it in a ready-made block into a simple report interface.
2) The report configurator should not be static, but expand automatically with the extension of the system. It is necessary to make sure that when expanding the system, the code of the report configurator itself should not be written down, but it would automatically pick up all the changes.
In systems with the possibility of user configuration, 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 will not chase everyone to customize the report configurator for each system configuration.
3) The report configurator should perform the task of reporting well, and not deal with related things.
Here the concept is similar to the principle of small “sharp” utilitieson unix-like systems. The utility should be able to brilliantly cope with its task, and not be able to do everything a little bit. Related tasks to transfer to utilities brilliantly performing them and just get results from them.
This rule applies fully to the system. For example, a staff module of access rights should deal with access rights to reports, and as for access rights, it can do well, and no matter where in the system.
Why not build your own system of access rights in reports - this is a crutch that will never be as good as a module, ground for it.
Similarly with the scheduler. Why not build scheduled tasks in the report configurator, it’s better to entrust this to a system that knows how to do this well.
There is no need to implement an editor for complex queries in the report configurator. This will simplify the interface and scare away ordinary users. It’s better to use a standard procedure editor for these tasks, which can configure complex queries well.
There is no need for the report configurator to make an individual distribution system when it is possible to use a regular one and when a new delivery channel appears, does not care about it. Let the delivery module operate the module intended for this.
The same requirements apply to the configurator - it should cope with its direct tasks as well as possible and be able to be friends with other modules of the system.
Such an approach in the system allows you to fine-tune these narrowly focused functions in modules, as well as not to spend extra time on developers implementing partially duplicated functions in various parts of the system.