Column definitions in Financial Reporting
Use column definitions to specify the columns to include in a report. For example, you can design a report layout to compare net change and balance for the same period this year and last year. You can have up to 15 columns in a column definition. For example, multiple columns are useful for displaying budgets for 12 months with a column that shows the total.
Create or edit a column definition
To create or edit a column definition, follow these steps.
Note
Printed, previewed, and saved versions of a financial report display a maximum of five columns. In contrast, if a financial report is only meant for analysis on the Financial Report page, you can create as many columns as you want.
- On the Financial Reports page, select the relevant financial report, and then choose the Edit Column Definition action.
- On the Column Definition page, create a row for each column of financial data shown in the financial report. Hover over a field to read a short description.
- Choose OK.
- Open the Financial Report page from time to time to verify the new column definition works as intended.
Built-in column definitions
Business Central provides sample column definitions that can help you to quickly get started setting up finance reports that suit your needs.
Example: Create a column definition to calculate percentages
You might want to include a column in a financial report to calculate percentages of a total. For example, if you have rows that break down sales by dimension, you might want a column to indicate the percentage of total sales in each row.
- Choose the
icon, enter Financial Reports, then choose the related link. - On the Financial Reports page, select a financial report.
- Choose the Edit Financial Report action to set up a financial report row to calculate the total on which the percentages are based.
- Add a line immediately above the first row for which you want to display a percentage.
- Fill in the fields on the line as follows:
- In the Totaling Type field, enter Set Base for Percent.
- In the Totaling field, enter a formula for the total that the percentage is based on. For example, if row 11 contains the total sales, enter 11.
- Choose the Edit Column Definition action to set up a column.
- Fill in the fields on the line as follows.
- In the Column Type field, select Formula.
- In the Formula field, enter a formula for the amount you want to calculate a percentage for, followed by the percentage sign (%). So, if column number N contains the net change, enter N%.
- Repeat steps 4 through 7 for each group of rows you want to break down by percentage.
Comparing accounting periods using period formulas
Your financial report can compare the results of different accounting periods, such as the past month versus the same month last year. To do that, open the Column Definition page, and personalize it by adding the Comparison Period Formula field as a column. Learn more at Personalize Your Workspace. You can then set that field to a period formula.
An accounting period doesn't need to match the calendar. However, each fiscal year must have the same number of accounting periods, although each period can be different in length.
Business Central uses the period formula to calculate the duration of the comparison period in relation to the period represented by the date filter on the report. The comparison period is based on the period of the start date of the date filter. The following table shows the abbreviations for period specifications.
| Abbreviation | Description |
|---|---|
| P | Period. |
| LP | Last period of a fiscal year, half-year, or quarter. |
| CP | Current period of a fiscal year, half-year, or quarter. Use CP in formulas to set the period that starts or ends the formula. For example, FY[1..CP] denotes the time from the beginning of the current fiscal year to the current period. |
| FY | Fiscal year. For example, FY[1..3] denotes the first quarter of the current fiscal year. |
Examples of formulas:
| Formula | Description |
|---|---|
| <Blank> | Current period. |
| -1P | Previous period. |
| -1FY[1..LP] | Entire previous fiscal year. |
| -1FY | Current period in previous fiscal year. |
| -1FY[1..3] | First quarter of previous fiscal year. |
| -1FY[1..CP] | From the beginning of the previous fiscal year to the current period in the previous fiscal year, including both periods. |
| -1FY[CP..LP] | From the current period in previous fiscal year to the last period of the previous fiscal year, including both periods. |
To calculate by regular time periods, enter a formula in the Comparison Date Formula field instead. For example, if the field is set to -1Y, Business Central compares to the same period one year earlier.
Note
It isn't always obvious which periods you're comparing because you can set a date filter on a report that spans dates that are different than the accounting periods in your chart of accounts. So, if you create a financial report where you want to compare this period to the same period last year, set the Comparison Date Formula field to -1FY. Then, run the report on February 28th and set the date filter to January and February. As a result, the financial report compares January and February this year to January last year, which is the only completed accounting period of the two for last year.
Learn more at Work with Calendar Dates and Times.
Best practices for working with column definitions
Column definitions aren't versioned. When you change a column definition, the old version is replaced when your change saves to the database. The following list contains some best practices for working with column definitions.
- If you add a column definition, choose a good code and fill in the Description field with meaningful text while you still know what you use the column definition for. This information helps your coworkers (and your future self) work with financial reporting and perhaps changing the column definition.
- Before you change a column definition, consider taking a copy of it as a backup, just in case your change doesn't work as expected. You can either just copy the definition (give it a good name), or export it. To learn more, go to Import or export column definitions.
- If you need a fresh copy of a definition that Business Central provides, an easy way to get one is to create a new company that only contains setup data. Then, export the definition and import it in the company where the definition needs a refresh.
Import or export financial report column definitions
Starting with the 2024 release wave 1 (version 24.1), you can import and export financial report column definitions as RapidStart configuration packages. For example, configuration packages are useful for sharing information with other companies. The package is created in a .rapidstart file, which compresses the contents.
Note
When you import financial report column definitions, they replace existing records with the same names. The configuration package for a report definition won't overwrite any existing row or column definitions that are used in the report definition.
To import or export financial report column definitions, follow these steps:
- Choose the icon, enter Column Definitions, and then choose the related link.
- Choose the row definition, and then choose the Import Column Definition or Export Column Definition action, depending on what you want to do.
See also
Row definitions in financial reporting
Prepare financial reporting
Financial Business Intelligence
Finance
Setting Up Finance
Work with Business Central