Summen- und Saldenliste: missing information

[dgerhardt]

When testing the "Summen- und Saldenliste" report, I noticed a few issues, which seem to limit its usefulness. In its current state, it can only be used to evaluate a discrete month. It cannot be used to evaluate the fiscal year up to a certain cutoff date because of missing information.

The specific issues I have noticed:

• The report seems to be incomplete. It only lists accounts which have GL entries for the selected month. This might be useful as an option, but I would not expect this as default behavior.
• Accumulated values ("Debit/Credit until" column) do not include the current month. This is unexpected because I can't really see the use case for having the previous months data. It would make more sense to me, to explicitly select the previous month if the data is needed. As a consequence, it is currently not possible to get the accumulated values for a full accounting period because December would only include data until the end of November.
• To avoid confusion, it would be helpful to have the exact period for the data mentioned on the report (e.g.: Is the report starting with the calendar year or fiscal year?).

[barredterra]

Thanks for your feedback. I tried to model the report we get from DATEV, which I deemed to be industry standard.

The report seems to be incomplete. It only lists accounts which have GL entries for the selected month. This might be useful as an option, but I would not expect this as default behavior.

Since ERPNext comes preinstalled with hundreds of accounts and you usually use only a few of them, this would make the report very hard to read. However, I agree that we should likely include all accounts that had any transactions in the past.

Accumulated values do not include the current month.

Are you sure about this? I'd be surprised.

To avoid confusion, it would be helpful to have the exact period for the data mentioned on the report (e.g.: Is the report starting with the calendar year or fiscal year?).

Column	From Time	To Time
Debit Opening Balance	beginning of time	start of fiscal year
Credit Opening Balance	beginning of time	start of fiscal year
Debit until {month}	start of the fiscal year	start of the month
Credit until {month}	start of the fiscal year	start of the month
Debit in {month}	start of the month	end of the month
Credit in {month}	start of the month	end of the month
Debit Closing Balance	beginning of time	end of the month
Credit Closing Balance	beginning of time	end of the month

If that's any help, you can check the code here:

erpnext_germany/erpnext_germany/erpnext_germany/report/summen__und_saldenliste/summen__und_saldenliste.py

Lines 95 to 214 in f047b8e

	def get_data(company: str, fy_start, month_start, month_end):
	gl_entry = frappe.qb.DocType("GL Entry")
	account = frappe.qb.DocType("Account")
	opening_balance = (
	frappe.qb.from_(gl_entry)
	.left_join(account)
	.on(gl_entry.account == account.name)
	.select(
	gl_entry.account,
	Case()
	.when(
	account.root_type == "Asset",
	Sum(gl_entry.debit_in_account_currency) - Sum(gl_entry.credit_in_account_currency),
	)
	.else_(None)
	.as_("debit"),
	Case()
	.when(
	account.root_type.isin(("Liability", "Equity")),
	Sum(gl_entry.credit_in_account_currency) - Sum(gl_entry.debit_in_account_currency),
	)
	.else_(None)
	.as_("credit"),
	)
	.where(
	(gl_entry.company == company) & (gl_entry.is_cancelled == 0) & (gl_entry.posting_date < fy_start)
	)
	.groupby(gl_entry.account)
	)
	sum_until_month = (
	frappe.qb.from_(gl_entry)
	.select(
	gl_entry.account,
	Sum(gl_entry.debit_in_account_currency).as_("debit"),
	Sum(gl_entry.credit_in_account_currency).as_("credit"),
	)
	.where(
	(gl_entry.company == company)
	& (gl_entry.is_cancelled == 0)
	& (gl_entry.posting_date >= fy_start)
	& (gl_entry.posting_date < month_start)
	& (gl_entry.voucher_type != "Period Closing Voucher")
	)
	.groupby(gl_entry.account)
	)
	sum_in_month = (
	frappe.qb.from_(gl_entry)
	.left_join(account)
	.on(gl_entry.account == account.name)
	.select(
	gl_entry.account,
	gl_entry.account_currency,
	Sum(gl_entry.debit_in_account_currency).as_("debit"),
	Sum(gl_entry.credit_in_account_currency).as_("credit"),
	)
	.where(
	(gl_entry.company == company)
	& (gl_entry.is_cancelled == 0)
	& (gl_entry.posting_date >= month_start)
	& (gl_entry.posting_date <= month_end)
	& (gl_entry.voucher_type != "Period Closing Voucher")
	)
	.orderby(Cast(account.account_number, "int"))
	.groupby(gl_entry.account, gl_entry.account_currency)
	)
	query = (
	frappe.qb.from_(sum_in_month)
	.left_join(sum_until_month)
	.on(sum_until_month.account == sum_in_month.account)
	.left_join(opening_balance)
	.on(opening_balance.account == sum_in_month.account)
	.left_join(account)
	.on(sum_in_month.account == account.name)
	.select(
	sum_in_month.account,
	sum_in_month.account_currency,
	opening_balance.debit,
	opening_balance.credit,
	sum_until_month.debit,
	sum_until_month.credit,
	sum_in_month.debit,
	sum_in_month.credit,
	Case()
	.when(
	account.root_type.isin(("Asset", "Expense")),
	(
	Coalesce(opening_balance.debit, 0)
	+ Coalesce(sum_until_month.debit, 0)
	+ Coalesce(sum_in_month.debit, 0)
	)
	- (
	Coalesce(opening_balance.credit, 0)
	+ Coalesce(sum_until_month.credit, 0)
	+ Coalesce(sum_in_month.credit, 0)
	),
	)
	.else_(None),
	Case()
	.when(
	account.root_type.isin(("Liability", "Equity", "Income")),
	(
	Coalesce(opening_balance.credit, 0)
	+ Coalesce(sum_until_month.credit, 0)
	+ Coalesce(sum_in_month.credit, 0)
	)
	- (
	Coalesce(opening_balance.debit, 0)
	+ Coalesce(sum_until_month.debit, 0)
	+ Coalesce(sum_in_month.debit, 0)
	),
	)
	.else_(None),
	)
	)
	return query.run()

[dgerhardt]

Thanks for the quick reply!

I tried to model the report we get from DATEV, which I deemed to be industry standard.

There seem to be a lot of difference between the structure SuSa reports of different tools, but it is seems reasonable to me to model the reports based on the structure of DATEV reports.

I don't think there is anything wrong with the structure, it is more about the information used for the report.

Since ERPNext comes preinstalled with hundreds of accounts and you usually use only a few of them, this would make the report very hard to read. However, I agree that we should likely include all accounts that had any transactions in the past.

I agree. I would only expect the accounts which have been used in the current accounting period to be included.

Accumulated values do not include the current month.

Are you sure about this? I'd be surprised.

To clarify, I'm talking about the "Debit/Credit until" columns here. Not the "Closing Balance" column. I would expect them to include the current month (also the column title would need to be adjusted to avoid ambiguity).

I don't have a DATEV SuSa report at hand, but looking at screenshots, it seems to have "kum. Werte S/H" columns instead of "D/C until" columns which include data of the current month.