Financial Reports Setup (Balance Sheet)

(7) Share

Report

Posted on by LM-26090852-0

175

Good day,

I'm configuring a financial report (balance sheet) to display the sum of several accounting accounts in a single row. The issue arises when I need the report to include an account in the sum only if its balance is positive, while negative balances should be added to a different row.

For example, let's take account 2405004—a typical case of a debt with a group company. Today, the company owes me money, so it falls under "Investments in Group Companies." However, next year, if I borrow money from them, I will owe them instead. The account number remains the same, but now it should be classified under "Group Company Debts."

I can't simply say, "2405004 is group company debts," because not all of them are debts—only those with a negative balance should be classified as such.

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

Holly Huffman 6,530 Super User 2025 Season 2 on at

Like (1)

Report
Hi there! Good morning, evening, or afternoon - depending on where you are :) Hope you are well today!

This is a common reporting challenge, and the solution involves dynamically classifying accounts based on their balance values. Here's how you can approach this within Dynamics NAV or Business Central:

1. Use Calculation Formulas

In your balance sheet setup, leverage conditional formulas to classify account balances. Dynamics NAV and BC allow you to set up logic to calculate sums based on conditions, such as whether an account's balance is positive or negative.

For example, you can use CASE or IF statements in account schedules to direct positive balances to one row (e.g., "Investments in Group Companies") and negative balances to another (e.g., "Group Company Debts").

2. Account Schedules

Create account schedules that utilize formulas to split balances into separate rows:

Define one row for positive balances (Amount > 0).

Define another row for negative balances (Amount < 0).

The "Totaling Type" field in the account schedules can support complex totaling with conditions applied to the sum of accounts.

3. Custom Fields or Dimensions

If conditional logic cannot be handled directly in the report configuration, you can add a custom field or dimension to track balance classifications dynamically. This allows the system to evaluate the balance before placing it into the correct reporting category.

4. Use Excel for Advanced Calculation (Optional)

If Dynamics NAV or BC does not fully support the required conditional logic, you can export the data to Excel, apply formulas or pivot table logic to categorize accounts, and import the adjusted data back into the system for reporting.

5. Testing

After implementing the formulas or conditional logic, test the report across multiple scenarios to ensure accounts are correctly classified based on their balance at the time of reporting.

Hope this helps some!

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,593 Super User 2025 Season 2 on at

Like (2)

Report

Hi, In Amount Type, you can set Debit Amount or Credit Amount. I think you can try first.

Hope this helps.

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

Jainam M. Kothari 15,639 Super User 2025 Season 2 on at

Like (2)

Report

Hello,

Yes, it’s an accounting matter. Some G/L accounts appear in the trial balance as investments in one year and as debt in another. Therefore, you need to adjust the display of the same G/L account in the Account Schedule for different years.

If you want to show the G/L account under the same heading, you can net off the particular head account.

However, if you want to display the G/L account under different headings, you need to adjust the account schedule accordingly

Was this reply helpful? Yes No