Bug in Financial Statement Report - Menu Export Excel (Balance Sheet)

The Excel file download has the bug:
The Column Titles are misaligned with the Data Columns

Column Title
A - Account
B to M Jan 2020 to Dec 2020\

Data Row
A - Account Code
B - Currency (PHP)
C to N - Jan 2020 to Dec 2020

As can be seen, the December Column Title is Blank in row 1, and the Jan 2020 Column Title is on B instead of C

Got it! The Data part did not check for hidden columns.

Bugfix;
frappe/frappe/desk/query_report.py
lines 353: (add the check if not columns[idx].get(“hidden”)

					if not columns[idx].get("hidden"):
						label = columns[idx]["label"]
						fieldname = columns[idx]["fieldname"]
						cell_value = row.get(fieldname, row.get(label, ""))
						if cint(include_indentation) and 'indent' in row and idx == 0:
							cell_value = ('    ' * cint(row['indent'])) + cell_value
						row_data.append(cell_value)

Now the hidden Currency Column is no longer printed and the data rows are vertically aligned with their respective monthly headings. I guess this also fixes other query and script based reports like trial balance.

Great! Thanks for posting the fix. The most major issue for us though is the fact that Group accounts are not highlighted in any way! How do you make sense of any financial reports (in Excel) without being able to tell the Groups from the Ledgers???

Cheers

How would you prefer to differentiate group accounts from non-group accounts?
Different font like Bold for group accounts?

Absolutely! Bold for Group accounts. If possible, the rows for Group accounts could also be highlighted with a color

Hi @Joseph_Marie_Alba1

Trust you had a good weekend. Any idea how this can be fixed via custom script ?

Kind regards,

A simpler way is to Add Column to the Report (Balance Sheet, Profit Loss, etc.)

Menu > Add Column:

Add Column (Dialog)
From Document Type: Account
Field: Is Group
Insert After: Account
→ Click Submit (Click once only even if the Dialog box does not close immediately.)

You will see that the non-group Accounts (entries) will be unchecked while the Group Accounts (ledger accounts) will be checked.

Unfortunately, while Print shows the added column, the exported Excel does not show the added Is Group column. So, I guess I have to do another round of bug hunting.

Yes, the issue is with the Excel export, not the print format. If you can come up with anything, it will be greatly appreciated

Cheers

Got it. custom_columns are not considered.

change line 170 of frappe/frappe/desk/query_report.py

if report.prepared_report and not report.disable_prepared_report and not ignore_prepared_report 

to

if report.prepared_report and not report.disable_prepared_report and not ignore_prepared_report and not custom_columns:

Hi @Joseph_Marie_Alba1

Thanks a lot for this! It however seems the solution involves changing the core code? Would have preferred to avoid that but at least this provides an option for emergency use

Many thanks

I proposed the fix in the core just now.

1 Like

Pls link the Github issue here so I can up-vote

Thanks

https://github.com/frappe/frappe/pull/11593

2 Likes