We prepare reports for the board of directors in 20 minutes
I will continue the story about the capabilities of the Ultima Businessware platform . Today is about our tool for preparing special reports. This tool really arose as a response to the need to quickly receive a report in the usual form for senior managers. And the first reports made with his help were P&L and Balance.
For a better understanding of this article, it is very useful to read my article about the system device and a small introductory document from our website.
Just in case, I will try to briefly repeat the necessary minimum.
We store data on measurable indicators in the so-called totals. In a way, this is an analogue of a cube from OLAP. Or register for friends with 1C. The total has dimensions and variables. Totals change their meaning with 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 dimensions and 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 measurement (or related directories), and each variable is decomposed into 4 components: Incoming and outgoing balances (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 total Implementation with dimensions of customer, product, warehouse and quantity and amount variables.
Accordingly, we know from it how much goods to which customer and from which warehouse they sold and at what price (to be precise, at what cost and with what profit). The second important outcome is Costs with a measurement of the cost item and variable amount. In the simplest case, these two totals are enough to build an income statement (P&L).
The income statement has a very specific view in IFRS. Accordingly, a means is required for converting data from totals to a given view. This, of course, is easy to do with the print form, but we wanted to be able to expand the articles in this report. And for this we already have a form of analysis of the results! This means that you need to describe what transformations you need to carry out with the totals and how to combine the variables and dimensions of the various totals. SQL handles such a relational algebra perfectly, but we wanted to give the users of the system the opportunity to configure such reports. We called this mechanism virtual totals.
So, here is an example of a profit and loss account 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 constructor (invented by us).
So, since we are describing a new total, the dimensions and variables of this total are listed at the beginning.
Dim, respectively, dimension is the name of the dimension, then in brackets the reference is indicated, from which the values for this dimension are taken and finally localization.
Variables are described in the same way, here it is one - Amount.
The primitive income statement should look like
Total:
Income: Expense
:
Accordingly, in the Income group, we include the debit component of the Amount variable of the totals Sale (Implementation) and Expense (Costs).
All positive costs fall into the expense group.
Finally, the Profit group consists of (the sum) of the two groups Expense (with the opposite sign) and income.
As a result, the system generates such a form for setting up the report:
And this will be the result:
However, after all, we added measurements to this total, let's see which documents generated these figures:
Similarly, we can split the report into cost items and other measurements.
To simplify the syntax, I had to go for a large number of conventions. For example, by default, dimensions are mapped simply by coincidence of names.
Consider a more complex example. There is a result on which the currency is converted and, accordingly, the loss from conversion operations is accumulated. We (as top managers) want to consider this loss as a cost item for conversion losses. However, as a result of the conversion there is not one of the dimensions of our outcome. It doesn’t matter, you can set from:
Having fixed the cost item in this way, we can see the loss from conversion operations on the corresponding line of the cost item.
The syntax allows you to impose filters. For example, you can make the group “Balance of suppliers”, which will include only counterparties that are in the suppliers folder. The filter is specified by the filter keyword and then the measurement and filter 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 by predicates. The predicate in this case is a SQL statement formulated by the developer that can be integrated into the virtual result description language. For example, to create a group based on the total with counterparty balances, which will only include those who have unpaid orders, taking into account commodity loans and deferred payments.
I do not intend 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 dispensing with third-party data analysis tools, which significantly reduces the implementation budget. But the main thing is quick and 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 device and a small introductory document from our website.
Just in case, I will try to briefly repeat the necessary minimum.
We store data on measurable indicators in the so-called totals. In a way, this is an analogue of a cube from OLAP. Or register for friends with 1C. The total has dimensions and variables. Totals change their meaning with 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 dimensions and 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 measurement (or related directories), and each variable is decomposed into 4 components: Incoming and outgoing balances (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 total Implementation with dimensions of customer, product, warehouse and quantity and amount variables.
Accordingly, we know from it how much goods to which customer and from which warehouse they sold and at what price (to be precise, at what cost and with what profit). The second important outcome is Costs with a measurement of the cost item and variable amount. In the simplest case, these two totals are enough to build an income statement (P&L).
The income statement has a very specific view in IFRS. Accordingly, a means is required for converting data from totals to a given view. This, of course, is easy to do with the print form, but we wanted to be able to expand the articles in this report. And for this we already have a form of analysis of the results! This means that you need to describe what transformations you need to carry out with the totals and how to combine the variables and dimensions of the various totals. SQL handles such a relational algebra perfectly, but we wanted to give the users of the system the opportunity to configure such reports. We called this mechanism virtual totals.
So, here is an example of a profit and loss account 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 constructor (invented by us).
So, since we are describing a new total, the dimensions and variables of this total are listed at the beginning.
Dim, respectively, dimension is the name of the dimension, then in brackets the reference is indicated, from which the values for this dimension are taken and finally localization.
Variables are described in the same way, here it is one - Amount.
The primitive income statement should look like
Total:
Income: Expense
:
Accordingly, in the Income group, we include the debit component of the Amount variable of the totals Sale (Implementation) and Expense (Costs).
All positive costs fall into the expense group.
Finally, the Profit group consists of (the sum) of the two groups Expense (with the opposite sign) and income.
As a result, the system generates such a form for setting up the report:
And this will be the result:
However, after all, we added measurements to this total, let's see which documents generated these figures:
Similarly, we can split the report into cost items and other measurements.
To simplify the syntax, I had to go for a large number of conventions. For example, by default, dimensions are mapped simply by coincidence of names.
Consider a more complex example. There is a result on which the currency is converted and, accordingly, the loss from conversion operations is accumulated. We (as top managers) want to consider this loss as a cost item for conversion losses. However, as a result of the conversion there is not one of the dimensions of our outcome. It doesn’t matter, you can set 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
Having fixed the cost item in this way, we can see the loss from conversion operations on the corresponding line of the cost item.
The syntax allows you to impose filters. For example, you can make the group “Balance of suppliers”, which will include only counterparties that are in the suppliers folder. The filter is specified by the filter keyword and then the measurement and filter 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 by predicates. The predicate in this case is a SQL statement formulated by the developer that can be integrated into the virtual result description language. For example, to create a group based on the total with counterparty balances, which will only include those who have unpaid orders, taking into account commodity loans and deferred payments.
I do not intend 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 dispensing with third-party data analysis tools, which significantly reduces the implementation budget. But the main thing is quick and detailed access to data from analysts and company management. This is a real competitive advantage!