Corporate reports

Working in large companies, especially multinational corporations, often involves periodically sending the standardized Excel reports that are intended to provide management with information about the business. These are usually consolidated balance sheets and other reports that display key performance indicators, and the employees who should fill them have a task to enter data into ready-made templates.

Creating standardized reports, which are sometimes even password protected, so that the person who makes them physically unable to accede to other cells except those that should be filled out, it is always a potential problem. First, because of manual entrance of data, which increases the possibility of error, and then the opportunity to tune the data which brings incorrect information to the management.

The question is: is it possible to create automation, that would use up to date information from existing ERP solution, to fill in standardized reports in Excel? The following text will describe techniques for extracting data from business information system, and we will show an example of filling the template income statement.

First of all, lets prepare the data. To be able to take data from the information system it is necessary to know the database structure. Each ERP usually has one or more tables that are used to record transactions. We create database connection by going to Data toolbar and choosing an option From Other Sources / From SQL Server (author assumes that your Information system is based on MS SQL Server).

Dialog box Data Connection Wizard should appear. First you have to enter the name of the server and type of authentication. Enter the name of your database server. Then enter type of authentication, which is  usually Windows authentication (consult your System Administrator if you have any doubts). Click on Next and move on to a new screen where cite the name of the database and tables with which you want to connect (in this example the database name is IS, and the table is Dnevnik, which is Serbian word for ledger). Click Next once again and (if you want) enter some more details that describe the connection to the database. Click Finish to complete the process of creating a database connection, and new dialog box will appear in which is necessary to specify in what form we want to import data. By selecting Table, data will be imported into a structured table.

It seldom happens that all the necessary data for the report are in one table, and preparation of data often includes writing the appropriate SQL query. Since people who staff these reports generally do not know the SQL language, my recommendation is to find a similar report in existing ERP solution. Then the report should be exported to Excel. The Data toolbar contains the option Connections. Clicking on this option, and then selecting Properties, will open dialog box Connection Properties. It has two tabs: Usage and Definition. Select Definition tab and you’ll be able to see definition of the connection, and on it you can see the SQL query by which the data was downloaded into a structured table. This SQL query can be copied and/or modified to extract different set of data, and then used to make your own connection to the database.

After we downloaded the necessary data we should fill predefined reports. Data to be completed are generally reduced to the sum. By applying functions of multiconditional aggregation we can summarize data from a structured table (populated with ERP data) and show them in a dedicated field. For example, the first field in the income statement should represent net revenues from sales. To get them we need to sum up a column of figures, in the case of the requirement that the account is 6020. That is, we’ll write the formula:

=SUMIFS(Table_Dnevnik[POTRAŽUJE],Table_Dnevnik[KONTO],6020)

In this formula we have used just one condition. In practice, it would be nice to add a current year as another condition, or maybe some other account numbers. In this way we reach the desired reports by using data that already exist in the ERP solution. I hope that accountants won’t mind if I used wrong account numbers, my aim was to demonstrate the principle.

We have avoided manual entrance of data. Besides that, these reports have another good trait. Every time you refresh the data, by clicking Refresh button in Data ribbon, you’ll get updated information from the ERP. Generally speaking, once made a report can be used multiple times.