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