Preparing reports for the board of directors in 20 minutes
I will continue the story about the capabilities of the platform Ultima Businessware . Today - about our tool for the preparation of special reporting. This tool really arose as a response to the need to promptly receive a report in the form familiar to top managers. And the first reports made with it were P & L and Balance.
For a better understanding of this article, it is very useful to read my article about the system structure and a small introductory from the documentation on our site.
Just in case, I will try to briefly repeat the necessary minimum.
Data on measurable indicators we store in the so-called totals. In a sense, this is an analogue of the cube from OLAP. Or register for those familiar with 1C. The total has dimensions and variables. Totals change their value by transactions that are generated by documents. All transactions are saved, and you can calculate the status of the total at any time. The transaction in turn contains the measurements and the delta for each of the variables.
Based on the description of the total, the system can build an arbitrary report. Data can be grouped by measurements (or related directories), and each variable is decomposed into 4 components: Incoming and outgoing balance (the value of the variable at the beginning and end of the period) and debit and credit (the sum of positive and negative deltas).
For example, in the system there is a summary Implementation with the dimensions customer, product, warehouse, and variables the quantity and amount.
Accordingly, we know from it how much goods were sold to which client and from which warehouse and at what price (to be exact, with what cost price and with what profit). The second important outcome is the Costs with the measurement of the cost item and the variable amount. In the simplest case, these two totals are enough to build a profit and loss statement (P & L).
The profit and loss statement has a very specific form in IFRS. Accordingly, a tool is needed to convert the data from the totals to the specified form. This, of course, is easy to do with a printed form, but we wanted to be able to expand the articles of this report. And for this we already have a form of analysis of the results! This means, it is necessary to describe what transformations should be carried out with the results and how to combine the variables and measurements of different totals. SQL does a great job with this relational algebra, but we wanted to give the system users the ability to customize such reports. We called this mechanism virtual results.
')
So, here is an example of a description of the income statement:
Yes, this report is described in synthetic language. As practice has shown, it is more convenient to do this in text form than in any (invented by us) designer.
So, since we are describing a new total, the dimensions and variables of this total are listed at the beginning.
Dim, according to dimension, is the name of the dimension, then the reference book is indicated in parentheses, from which values ​​are taken for the given dimension and finally localization.
Variables are described in the same way, here it is one - Amount.
Primitive income statement should be
Total:
Income:
Consumption:
Accordingly, in the Revenue group, we include the debit component of the Amount variable of the Sale (Implementation) and Expense totals (Costs).
All positive costs fall into the expense group.
Finally, the Profit group consists of (the sum) of two groups Expense (with the opposite sign) and income.
As a result, the system generates the following form for setting up the report:
And this will be the result:
However, we after all added measurements to this result, let's see which documents formed these figures:
Similarly, we can split up the report on cost items and other measurements.
To simplify the syntax, we had to go through a large number of agreements. For example, by default, measurements are matched simply by matching the names.
Consider a more complex example. There is a result on which currency conversion takes place and, accordingly, the loss from conversion operations is accumulated. We (as top managers) want to treat this loss as a loss item from conversion. However, in the end, the conversion is not one of the measurements of our total. It does not matter, you can specify from:
Fixing the cost item in this way, we can see the loss from the conversion operations on the corresponding line of the cost item.
Syntax allows you to overlay filters. For example, you can make the “Supplier Balance” group which will only include contractors that are in the Suppliers folder. The filter is specified by the filter keyword and then the filtering dimension and condition are specified.
However, sometimes there are conditions that are inexpressible within the framework of this simple query language and then it can be expanded to include predicates. In this case, the predicate is an SQL expression formulated by the developer that can be integrated into the virtual total description language. For example, to create a group on the basis of counterparty balances, where only those who have unpaid orders, taking into account commodity loans and payment delays, will fall.
I do not set out to describe in detail the whole mechanism and syntax of the language in this article (there is documentation for this). However, I hope to shed light on the capabilities of the system and ease of development. The described mechanisms allow doing without third-party data analysis tools, which significantly reduces the implementation budget. But the main thing is prompt and well-detailed access to data from analysts and company management. This is a real competitive advantage!
For a better understanding of this article, it is very useful to read my article about the system structure and a small introductory from the documentation on our site.
Just in case, I will try to briefly repeat the necessary minimum.
Data on measurable indicators we store in the so-called totals. In a sense, this is an analogue of the cube from OLAP. Or register for those familiar with 1C. The total has dimensions and variables. Totals change their value by transactions that are generated by documents. All transactions are saved, and you can calculate the status of the total at any time. The transaction in turn contains the measurements and the delta for each of the variables.
Based on the description of the total, the system can build an arbitrary report. Data can be grouped by measurements (or related directories), and each variable is decomposed into 4 components: Incoming and outgoing balance (the value of the variable at the beginning and end of the period) and debit and credit (the sum of positive and negative deltas).
For example, in the system there is a summary Implementation with the dimensions customer, product, warehouse, and variables the quantity and amount.
Accordingly, we know from it how much goods were sold to which client and from which warehouse and at what price (to be exact, with what cost price and with what profit). The second important outcome is the Costs with the measurement of the cost item and the variable amount. In the simplest case, these two totals are enough to build a profit and loss statement (P & L).
The profit and loss statement has a very specific form in IFRS. Accordingly, a tool is needed to convert the data from the totals to the specified form. This, of course, is easy to do with a printed form, but we wanted to be able to expand the articles of this report. And for this we already have a form of analysis of the results! This means, it is necessary to describe what transformations should be carried out with the results and how to combine the variables and measurements of different totals. SQL does a great job with this relational algebra, but we wanted to give the system users the ability to customize such reports. We called this mechanism virtual results.
')
So, here is an example of a description of the income statement:
// dimensions and variables dim AgentID (Agent): ru() dim CostItemID (CostItem): ru( ) dim OfficeID (Office): ru() dim FrcID (FRC): ru() var Amount: ru() // calculated groups group Income: ru() var Amount total Expense: -Amount.Sub total Sale: -Amount.Sub end group Outcome: ru() var Amount total Expense: Amount.Add end group Profit: ru() no details var Amount group Income: Amount group Outcome: -Amount end Yes, this report is described in synthetic language. As practice has shown, it is more convenient to do this in text form than in any (invented by us) designer.
So, since we are describing a new total, the dimensions and variables of this total are listed at the beginning.
Dim, according to dimension, is the name of the dimension, then the reference book is indicated in parentheses, from which values ​​are taken for the given dimension and finally localization.
Variables are described in the same way, here it is one - Amount.
Primitive income statement should be
Total:
Income:
Consumption:
Accordingly, in the Revenue group, we include the debit component of the Amount variable of the Sale (Implementation) and Expense totals (Costs).
All positive costs fall into the expense group.
Finally, the Profit group consists of (the sum) of two groups Expense (with the opposite sign) and income.
As a result, the system generates the following form for setting up the report:
And this will be the result:
However, we after all added measurements to this result, let's see which documents formed these figures:
Similarly, we can split up the report on cost items and other measurements.
To simplify the syntax, we had to go through a large number of agreements. For example, by default, measurements are matched simply by matching the names.
Consider a more complex example. There is a result on which currency conversion takes place and, accordingly, the loss from conversion operations is accumulated. We (as top managers) want to treat this loss as a loss item from conversion. However, in the end, the conversion is not one of the measurements of our total. It does not matter, you can specify from:
group Outcome: ru() var Amount total Expense: Amount.Add total Convertation: Amount value CostItemID: 192 value OfficeID: none value FrcID: none value AgentID: none end Fixing the cost item in this way, we can see the loss from the conversion operations on the corresponding line of the cost item.
Syntax allows you to overlay filters. For example, you can make the “Supplier Balance” group which will only include contractors that are in the Suppliers folder. The filter is specified by the filter keyword and then the filtering dimension and condition are specified.
However, sometimes there are conditions that are inexpressible within the framework of this simple query language and then it can be expanded to include predicates. In this case, the predicate is an SQL expression formulated by the developer that can be integrated into the virtual total description language. For example, to create a group on the basis of counterparty balances, where only those who have unpaid orders, taking into account commodity loans and payment delays, will fall.
I do not set out to describe in detail the whole mechanism and syntax of the language in this article (there is documentation for this). However, I hope to shed light on the capabilities of the system and ease of development. The described mechanisms allow doing without third-party data analysis tools, which significantly reduces the implementation budget. But the main thing is prompt and well-detailed access to data from analysts and company management. This is a real competitive advantage!
Source: https://habr.com/ru/post/259923/
All Articles