Today, we’re diving into the world of column definitions in Business Central financial reports. If you’ve ever felt overwhelmed by the columns in your financial statements, don’t worry — You’re not alone.
In this blog, we’ll break down the process of creating and modifying column definitions step by step. By the end, you’ll be a pro at customizing your financial reports to show exactly what you need.
What are Column Definitions?
Column definitions determine what each column in your financial statement will display. This could include anything from net changes and balances to specific account entries. By mastering column definitions, you can tailor your reports to provide the most relevant and insightful data for your organization.
How to create Column Definitions in Business Central
- Search for Column Definitions: Open Business Central and use the search bar to look for “Column Definitions.” This will take you to the page where you can create and manage your column definitions.
- Create a New Column Definition: Click on the New button to start creating a new column definition. Give it a meaningful name and description. Optionally, you can also assign an Analysis View Name if you want to link it to a specific analysis view.
- Edit Column Definition: Select the column name you just created and click on Edit Column Definition. This is where you’ll define the details of each column. You can also copy, export, or import existing column definitions if you want to save time.
Column Definition details, explained
- Column No.: While this is technically optional, it’s best practice that every column should have a value. Assign a unique number to each. It’s a good practice to start with 100 and increment by 10, just in case you need to insert new columns later.
- Column Header: Enter a description that explains what the numbers in this column represent. For example, you could choose “Net Sales” or “Operating Expenses.”
- Column Type: There are several options to choose from here:
- Formula allows you to include basic calculations.
- Net Change shows the net change between periods and is typically used on income statements.
- Balance at Date shows the balance at a specific date. This is typically used on balance sheets.
- Beginning Balance shows the beginning balance at a specific date. You will most often use this for cash flow reports.
- Year to Date shows the balance for the entire year up to the current filtered date.
- Rest of Fiscal Year shows the balance for the remaining periods in the year and is most helpful to show your remaining budget amount.
- Entire Fiscal Year shows the balance for the entire year. This is typically used to show a full year’s budget amount.
- Ledger Entry Type: You can choose to pull either GL entries or Budget entries.
- Budget Name: If you chose Budget entries in the step above, then this is where you select the Budget name you want to pull.
- Formula: If you selected Formula as the column type, enter the formula needed to calculate the values. For example, you can use basic calculations like 10+20 or a range like 10..30.
- Show Opposite Sign: Check this box if you want to reverse the balance. For example, sales normally has a credit balance, but you might not want to show negative sales on your report.
- Comparison Date Formula: Define what dates the column should pull data from. For example, you can use formulas like -1P for the previous period or -1FY[1..LP] for the entire previous fiscal year.
- Comparison Period Formula: Define what period (such as Fiscal Year) the column should pull data from.
- Cost Center Totaling: Pull values based on your Cost Center posting accounts.
- Cost Object Totaling: Pull values based on your Cost Object posting accounts.
Show Indented Lines: Decide whether to show all indented lines, only indented lines, or non-indented lines. - Hide Currency Symbol: Check this box to hide the currency symbol.
Dimension Code 1-8 Totaling: Add any dimension filters that are specific to that column. Similar to what you learned with Row Definitions, this is unique to most organizations.
Show: Decide whether to show or hide the specified column. - Rounding Factor: Allows for rounding the values. You can choose to round to whole dollars, the nearest thousand, or the nearest million.
Here are two quick notes for you to keep in mind when creating your Column Definitions in Business Central:
- If you are not able to see all of the Column Definition options listed above, you might need to personalize your page to add them into your view.
- If your columns have dimension filters, keep in mind that blank dimensions will not show up. You may want to consider having a hidden column to validate that all your data is being captured.
Mastering column definitions in Business Central financial reports is a game-changer for customizing your financial statements. By defining columns accurately, you can ensure that your reports provide the most relevant and insightful data for your organization.
