DESKTOP EDITION CLOUD EDITION SERVER EDITION GUIDES FORUM

[18.9.60] Improved custom reports (added visual query builder)


#1

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:

  • Done Add ability to rename column names on report
  • Done More filter operators such as is empty, is not empty, is zero, is not zero etc…
  • Done Add totals at the bottom of each group (if using Group by) and at the bottom of the report
  • Done Support for translations (currently this feature is in English but it will be fully translatable)
  • Done Expose more fields including custom fields
  • Done Add ability to query individual lines such as sales invoice lines, purchase invoice lines etc.
  • Done Add 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.


General Ledger Transaction Report (Suggested Modifications)
Itemwise Sales Report
#2

#3

Great news! Thanks for making this happen.

I described what is mandatory for Macedonia here, I hope we are getting closer to that.


#4

This is really Great news! Thanks a lot, @lubos. Please try to add the custom fields also in selection drop-down menu.

Regards

B4ALLB4U


#5

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)


#6

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?


#7

This two… more exposing custom fields. Also adding the possibility to export the report in excel.


#8

Export to excel :heart: & Custom field report


#9

@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


#10

@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


#11

@lubos,

Well done, this is a major leap forward. Thank you!


#12

@lubos,

Sir, You are always helpful. It is a great feature for us in manager. Thank you so much.


#13

@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


#14

Any ideal why although I ask from the report to Group by CustomerName the results:


Nothing shows


#15

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?


#16

Well, maybe, but my example is not showing something like that, I think. I cannot show any data from customers in this example


#17

@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.


#18

thanks @lubos


#19

I can’t believe it’s happening.


#20

Business Name on report should be fixed.