Configure financial reports in Excel (Russia)
This article explains how a user in the System Administrator or Electronic Reporting Developer role can create a new Electronic reporting (ER) configuration that contains a template for generating a financial report in Microsoft Excel format.
Before you read this article, you should review Create Electronic reporting (ER) configurations and related electronic reporting topics about creating configurations.
Set up a financial report
Set up a financial report so that it has the list of financial report cells and rules for calculating financial reports cells.
- Upload data management package settings. For this example, upload the RU Accounting reporting 5.07 (2016).zip data management package settings as described in Accounting reporting in electronic format.
- Go to General ledger > Financial reports setup > Financial reports.
- Select the line that has the value Баланс in the Report code column.
- Select Setup.
- On the Requisites setup page, set up calculation rules for report cells, as described in Financial reporting (Russia).
Create an Excel template for the financial report
Create an Excel template for your financial report. At a minimum, you should assign names to all the Excel cells that should have values on the report that is generated.
For an example, download the Excel template example for a Russian balance sheet.
Create an ER configuration for the financial report in Excel format
Create an ER configuration format that is based on the Financial reports model ER model.
Before you complete this procedure, see ER Design a configuration for generating reports in OPENXML format (November 2016) for information about how to set up an ER configuration that generates a report in Excel format.
Download the latest version of the following ER configurations:
- Financial reports model
- Financial reports model mapping (RU)
- Balance sheet format Excel example (RU)
For instructions, see Download Electronic reporting configurations from Lifecycle Services.
Go to Workspaces > Electronic reporting.
Select the Reporting configurations tile.
Create a format for a financial report in Excel format:
On the Configurations page, select the Financial reports model ER configuration.
On the Action Pane, select Create configuration > Format based on data model Financial reports model.
Enter a name.
In the Format type field, select Excel.
In the Data model definition field, select Financial report.
Select Create configuration.
Select the new configuration, and then, on the Action Pane, select Designer.
On the Action Pane, on the Import tab, select Import from Excel.
In the Import from Excel dialog box, select Add template, and then select the Balance sheet.xls Excel file.
Set the Create Excel Sheet format element option to Yes.
Review the cells that are created from the names in the Excel template.
Close the page.
Configure the Balance sheet format Excel example (RU) ER format:
On the Configurations page, select the Balance sheet format Excel example (RU) ER configuration, and then, on the Action Pane, select Designer.
Expand the Excel = "Balance sheet" format element and then the Sheet<стр.1_2> format element. Review all cells that will contain data in the output for the financial report.
On the Mapping tab, review the elements of the Financial reports model that are sources for financial report cells.
Review the cells from DD through PrevPrevYear (DD, MM, YYYY, Company name, and so on). Make sure that they are mapped to model elements from the Header group that contains various information about the organization and report.
If any of the information about the organization on your financial report isn't in scope Financial reports model, you can follow these steps:
- If the missed data is external, create format User input parameter.
- Create a derived model that is based on the Financial reports model, and add new model elements. Then create a derived model mapping that is based on Financial reports model mapping, and bind new model elements to Finance data sources.
For more information about how to create ER data models, see ER Design domain specific data model.
For more information about how to map data model elements to data sources, see the following topics:
Review the cells that the financial report values are exported to. For example, the АктивВнеОбАНематАктПояснения, АктивВнеОбАНематАктСумОтч, АктивВнеОбАНематАктСумПрдщ, and АктивВнеОбАНематАктСумПрдшв cells are mapped to the following calculated field: Calculations.'$Values'("<Input parameter = Cell name>").Value or Calculations.'$Values'("<Input parameter = Cell code>").Text.
The Calculations.'$Values' calculated field contains the value of the financial report cell that has a code that equals the "Input parameter."
For more information about the Financial reports model, see Financial reporting (Russia).
Bind the calculated values of financial report cells to elements of the ER format cells:
In the current example, elements are already bound in the format. To complete the steps in the example, you must first unbind the elements.
Select the АктивВнеОбАНематАктПояснения ER format cell, and then select Unbind. Repeat this for the following three ER format cells: АктивВнеОбАНематАктСумОтч, АктивВнеОбАНематАктСумПрдщ, and АктивВнеОбАНематАктСумПрдшв. You won't need to do this for your new reports.
On the Mapping tab, expand the Calculations container, expand the $Values calculated field, and select the Text element.
In the list of Excel format cells, select the АктивВнеОбАНематАктПояснения cell, and then select Bind.
On the Mapping tab, select the Value element.
In the list of Excel format cells, select the АктивВнеОбАНематАктСумОтч cell, and then select Bind.
Repeat the previous two steps for АктивВнеОбАНематАктСумПрдщ, and АктивВнеОбАНематАктСумПрдшв Excel cells.
Run the financial report format
You can configure the Electronic messages feature to run any ER configuration.
To run the ER format that is based on the Financial reports model, follow these steps.
Go to General ledger > Inquiries and reports > Financial reports (Russia).
In the Financial reports (Russia) dialog box, in the Format mapping field, select the ER format that should be run. For example, select Balance sheet format Excel example (RU).
In the Electronic report parameters dialog box, define the report parameters.
Field Description Signatory first name, Signatory middle name, Signatory last name Enter the full name of the signatory. To date Enter the base date of the financial report. Approval date Enter the approval date of the financial report. Reporting date Enter the reporting date for the corrective report. Economic activity type code Enter the economic activity type code ("ОКВЭД"). Organizational form code Enter the organizational form code ("ОКОПФ"). Ownership form code Enter the ownership form code ("ОКФС"). Report Select the financial report that should be calculated. For this example, select Баланс.
Review the Excel report that is generated.