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

Excellent job lubos. Eventry trend mostly sold items and least sold items from inventory and age of inventory. expiry alert,out of stock items

Hi and thanks for this great feature.

I am trying to use this to create a report on QTY and unit prices from the Purchase Invoice, however have noticed that the fields needed are not available in the query builder.

Purchase Invoice

Query builder

It would be great if all the fields in the documents created are available in the query builder.

Regards
Junaid

Following on with my previous query, it would also be great if the query builder would allow a Virtual Column that could be used to have a formula using the columns that have been included in the query.

Thank you

The Purchase Invoice file only contains header information from the Purchase Invoices. If you want the lines from the Purchase Invoices, you need to querythe General Ledger Transaction

Thank you very much. That solved my QTY issue, however I can’t seem to find the Unit Price.

That is because the general ledger entry does not include the unit price, only the amount. The reason quantity is included is because inventory quantities must be adjusted. The financial adjustments are made on the basis of amount, not individual unit price.

Can you get what you want from the Inventory Quantity Movement and Inventory Value Movement reports that are built in?

Tried it. result is 0 as I am not using inventory.

Where is the unit price stored when entering it without inventory ?

Thank you

Unit price is stored in the record of the line item itself. These records are not exposed for custom reports currently. Perhaps they will be in the future as the feature continues to be improved.

Thanks for the prompt response. I look forward to the feature.

I just started to explore this new Custom Report Builder. Absolutely great…!!!

I just have some questions;

  1. It seems like the report builder is not able to extract the currency. I have tried with both Account/Currency/Code as well as Account/Currency/Name (as “Select” options), but both columns will be blank.

  2. I’m also trying to create a report for some specific Control Accounts, where I would like to “Group” the report by the “Special Accounts” (that are part of this Control Account). But I cannot find any way to choose Special Accounts in any way.

Any help or comments would be appreciated…

I think the issue with currency is that currencies are represented by hexadecimal keys in the data base and are not supported for custom reports yet.

Special accounts probably also fall into the same category. The feature is still being developed.

This topic has exceed its practical limit for the number of posts. I am closing it. If you have further questions about custom reports, please start a specific, new topic.