Issue with custom report with invoices & tax info

Hi Team,

PFB my custom report with details like the taxable amount, tax amount and the total amount for invoices for a certain period to produce it to my accountant.

Below generated report has the first two entries correct as expected.
Please look at the third entry highlighted yellow. Instead of the taxable amount, it displays the tax amount which is wrong. The report has the same issue showing for few other invoices too.

Attaching the invoice screenshot which is showing issue in the custom report. But I’m not able to find any issue in it.
Really not sure why is the taxable amount (highlighted yellow) not getting shown in the report. I feel there is some bug in the application.

Kindly let me know on this

Thanks & Regards,

Manager is a double entry accounting system so the total of all general ledger entries is zero.

You need to exclude the general ledger entries you don’t want to include in your totals.

Perhaps try adding a

  • where … general ledger account → isProfitandloss is checked

Nope. It didn’t work. I tried the way u said.

If you look at the screenshots that I have attached. The first two entries in the report are correct and are exactly the same that I’m expecting. But the third record is wrong.

@Tut / @lubos ,
Any suggestions ?

Thanks & Regards,

Show the Edit screens of a transaction that came out as expected and the one that did not. Also show a screen shot of the entire custom report definition. You cut off important entries at the top.

@Tut ,
Thanks for your response.

Below is the screenshot of the invoice (#122) that works as expected,

Below is the screenshot of the invoice (#124) that is not working.

Below is the custom report definition,

Please let me know if you need anything additional.

Thanks & Regards,

As said it is double entry accounting so it is not al that you have the opposite value if you query like this. In the posted screenshot of the settings I don’t see the suggested “where” GLT is P&L

Is there a problem with inventory not being in stock?

Can you drill down on the Inventory Sales account to see if the sales have been recorded

Also remove the checkbox on Group to collapse and you will see all the transactions involved

@Davide ,

Here you go !

This completely chopped off the tax amount in the report now :frowning: .

But when trying to write a custom query with sales invoices & taxes in it, why does the query need to have ‘P&L’ in the ‘where’ condition. Technically yes there might be several reasons to get the results, but functionally does it makes sense to involve ‘Profit & Loss’ when dealing with SalesInvoice.


It does not. In fact, it should not if you are looking for tax information, because tax amounts are not posted to a P&L account.

Possibly. That depends on what accounts you want transactions reported from. When you create a sales invoice, Accounts receivable and an income account are always involved. If inventory items are listed, Inventory on hand and Inventory - cost will be included. If taxes are applied, your tax liability account will also be included.

My point is that a single sales invoice can result in general ledger transactions to many different accounts. Without understanding this, and without proper criteria selection and filtering, you can end up reporting ledger entries that you might not normally think about. You can also be confused by what you see, thinking numbers come from one place when they actually come from another.

You are never going to get all the parameters you are trying to show on a single line, because some of them are posted to different accounts. So you need to construct the custom report not only to retrieve them from the data base, but filter out the ones you don’t want, keep the ones you do, and group them appropriately.

Having said that, your report definition looks to me like it should produce the type of entries you see for invoices 122 and 123. Your second screen shot in post #1 shows the wrong number for Taxable Amount on invoice 124. But your second screen shot in post #8 shows the correct number after you filtered out balance sheet accounts. Are you sure the screen shot in post #1 is of the correct version of your report? Remove the Where… filter @Davide suggested and check the report again.

Which is why during custom report development I always also show (Select … in Manager terminology) extra information such as:

  • General ledger account → name (& code)

It gives context to the data displayed, or in more detail Custom Report Not Returning Transaction Type Properly - #12 by Patch. Similarly there are multiple amount type variables which can be displayed / Selected, see this post for details Account Amount inclusive Tax in Reports - #7 by Patch
After the report is working the extra information can be removed from the report definition

Edit, link to a comparison of the various amounts displayable via custom reports

1 Like