The latest version (18.9.60) is greatly improving custom reports functionality.
Most notably, it’s no longer required to write SQL. Instead of SQL, there is visual query builder. For example, to generate report which lists your customers with their email address and billing address, you’d set it up like this:
Or you can generate custom report which shows your general ledger transactions grouped by account for specific period.
Or you can take it even further and show general ledger transactions only for Accounts receivables account group them by customer name.
There are number of things I need to add to custom reports to make it truly powerful. I will try to squeeze in as much as possible in upcoming days. Most notably:
DoneAdd ability to rename column names on report
DoneMore filter operators such as is empty, is not empty, is zero, is not zero etc…
DoneAdd totals at the bottom of each group (if using Group by) and at the bottom of the report
DoneSupport for translations (currently this feature is in English but it will be fully translatable)
DoneExpose more fields including custom fields
DoneAdd ability to query individual lines such as sales invoice lines, purchase invoice lines etc.
DoneAdd export button
To prioritize what’s important, I’d like to hear from you to see what reports you hope to generate in Manager right now. This will give me valuable feedback to see what features need to be added to custom reports first.
quatities of items sold to a customer in a choosen time frame and total value
with possibility to drill down, to click total and give a list of transactions for the item (date, item name, description, qty, price, amount)
I have started looking through the different information in each table (bank Accounts, etc) but wuld it be possible to add all of the fileds at one go eg using * or a box to include all fields.
It isn’t clear what each field holds especially for GL transactions.
Why do Bank Receipts not have a field to distinguish receipts from payments?
@lubos
Great news. Thanks and it would be great if you could squeeze in the other things in the upcoming days.
Today I played a little bit with the new functionality and I created a general ledger transaction report.
There is something funny with the layout. Some amounts show up as e.g. 135.00 and others as 60.0.
It is just a minor thing, but it makes reading a report somewhat difficult. See screenshot
Kind regards,
Hennie
@lubos
Thanks Lubos for solving in release 18.9.63 the minor issue I mentioned a couple of hours ago and adding totals to the columns. Again a minor thing: if the total of a column adds up to 0 please show it also with 2 digits as decimals
Kind regards,
Hennie
@lubos, thanks for the great feature.
Think about ability of the operator “or” in “Where” part of the SQL Query, as well as using more than one table in “From” part.
Also query individual lines such as sales invoice lines, purchase invoice lines etc., would be very helpful
I thing that the “join” part, in @lubos idea, is completely carried out via general ledger transaction query which join every table of the software. Is my interpretation correct?
@pandhm@Davide, joins in custom reports are automatic but General Ledger Transactions fields are not yet 100% populated. Currently, Customer field is filled only for general ledger transactions posting to Accounts Receivable. Similarly, InventoryItem field is only filled for general ledger transactions posting to Inventory on hand account.
That’s why getting general ledger transactions for Inventory - sales shows empty customer and empty inventory item.
I’m working on fixing this issue. Your custom report setup is correct, it’s just that General Ledger Transactions report doesn’t populate those fields yet. This will be fixed.
& Custom field report
