Every new day often throws up new puzzles. Not in the sense of their radical novelty, but in the sense of “one more type of tasks”, which are usually found in the business environment.

This time the task turned out to be extremely simple and pragmatic, but its solution gives far-reaching consequences.

Case

The essence of the problem is as follows:

1. There are long-term contracts for the provision of services.
2. Under these contracts reporting activities should be carried out in the form of a large number of paper documents (boxes), stitched and under the blue seal. And nothing else.
3. Monthly reporting.
4. The reports themselves are fairly formalized, but their preparation requires lengthy and, as the performers confidently declared, strictly manual work to collect information from a dozen systems, comparison and reconciliation, analysis, development of aggregates and formation of word documents in a consistent form.

The amount of work is such that a couple of analysts worked on this every month for a couple of weeks. Naturally, at the same time something was forgotten, something was missing, errors were made somewhere. The only thing that saves is that the receiving party does not have the resources to completely check all the reports provided.

One of the approximate report types is as follows:

The cumulative number of pages is in the thousands (tens of thousands in a bad month).

The data sources are a pair of desks service, a trio of monitoring systems, corporate directories, contracts, an accounting system.

For justice, I must say that earlier we have already done work on connecting these sources by means of R. Not to say that this is very heavy work, but not simpler than simple. The main thing was to figure out where the information lies and how it correlates:

• 80% of the time for formalization (what needs to be done in any case, whether manual or automatic data collection);
• 20% for the implementation of these procedures in the code on R.

So, in the course of the working discussion that it is fundamentally impossible to automate from the current activity this task was announced. She was hailed as “creative” and inaccessible to the understanding of the “positron brain” .

The call has been accepted.

After 4 hours, we already had a working R script (~ 4 screens with comments) that performs this task of preparing one of the reports.
The script performs the work on a turnkey basis, producing at the word output a file of the required format with actual data:

1. loads data from three sources (excel and 2 databases);
2. conducts pre-processing to identify confirmed cases;
3. carries out calculations (very unpretentious, but it does not matter) on the correlation of cases with contractual SLAs;
4. cleans and consolidates time intervals;
5. calculates monthly indicators;
6. generates a word file containing headings, text, generated formatted tables, generated according to the graphic data.

A significant addition to previous publications is the last item - the generation of a word file. This task is elegantly solved with the help of the cross-platform package ReporteRs . In addition to creating word files, it also allows you to generate powerpoint files, which is also very useful. It is no secret that many companies like to watch statements in the form of presentations. A good manual with examples can be read here: "Create and format Word documents using R software and Reporters package . "

As a result, 2 people * 2 weeks = 1 person months. is replaced by the script for 5-15 minutes. The benefit to the company is obvious.

Conclusion

The whole story could be described exclusively in iridescent colors, if it did not contain sad moments.

Yes, we fundamentally decided it, but at the very top, the results have not yet been reported.
This is not entirely correct with respect to analysts, it is necessary to give time for them to become aware of the situation and adopt a certain position. It is obvious that after such a report and trial operation (a month or two), the desire to optimize the payroll from the theoretical plane will be transferred to the practical one.

But, on the other hand, it is an inevitable vector of development and it is also impossible to keep silent about it.

PS
Using this approach allows you to do such tricks in a couple of lines that are very laborious when manually creating a word report:

• regrouping the tables according to changing criteria;
• criterial coloring of tables, design change;
• calculation of various units;
• bulk update of calculated fields;
• change the structure of the document.

Naturally, the classic approach “Made in Excel, dragged into Word” exists, but it is a long manual effort, error-prone and not always available for use in dynamically changing requirements. Those who want to get an independent view of the situation can also ask questions to the user AristarXXXX , who is an active participant in the process.

Previous post: "More examples of using R to solve practical business problems"