Help with report

V5 Question
1

Hello I have a report which shows me all outstanding balances due. I need to filter this report for the accountant by date. I need to know what the outstanding balance was on a specific date. If I change dates it still gives me the same figure. The accountant needs to know what was the balance owed on the 31st Dec 2017.

[Customer Account Balances:2, 1]

>Name| Balance

{REPORT ENTITY DETAILS:EC.Customer,E.Balance.sum:(ET=Customer) && E.AccountId>0:::$2>0}

{REPORT ENTITY DETAILS:EC.Customer,E.Balance.sum:(ET=Customer) && E.AccountId>0:::$2<0}

>TOTAL|{REPORT ENTITY DETAILS:E.Balance.sum:(ET=Customer) && E.AccountId>0:::}

[Open Layaways:3, 2, 2]

>| Total| Balance Due

>@@Openlayaways

2

I strugled with similar when trying to sort BF and CF figured for a debtors report on a system.
Balance type reports generally only returned current figure and didnt back date with report period.

3

REPORT ENTITY DETAILS report is useful to report Entity Data. You can’t filter customer balance on this report. Preparing a report based on Customer Account transactions and summarize transaction amounts will be a better idea. I prepared a report but.added few features to simplify syntax. That will work on next update.

[REPORT 2:2, 1]
@{REPORT ENTITY DETAILS:E.AccountId:(ET=Customers):,}
@{REPORT ENTITY DETAILS:E.Name:Entity.AccountId == $1}
{REPORT ACCOUNT TRANSACTION DETAILS: BBI.$1.sum:(STAI=$1):$2|{0}}

PS: Second parameter list is useful to capture Entity Name instead of using account name.

– BBI (Balance By Id) returns transaction balance for the account id.
– STAI parameter allows us the filter account transactions by source or target account id.

2 Likes