Tax Summary and Tax Transactions under new Custom Reports

It seems that with the new adds to Custom Reports we are now able to reproduce Tax Summary and Tax Transactions even with further implementantions for TT to make it coherent with TS (see the attached screenshot). But there are the issues:

  1. I cannot show dates when I use “Group to collapse”. I understand why @lubos has excluded them by default but I think that he should add Issue Date under Transactions (as well as Type, ie Sales Invoice, Purchase Invoice etc etc if we want to subgroup them);
  2. Reverse Charge for Tax doesn’t show up. For Invoices subject to Reverse Charge the tax amount should be shown both on Sales and Purchase side (with coherent results also on Total Sales, Total Purchases and Tax Liability). This is coherent with TR (but it’s an issue that I’ve already pointed out) but not with TS. As said TR it is completely wrong under a fiscal point of view since it should show symmetrical amounts on both Tax on Sales and on Purchase.
  3. I had some difficulties to find out Tax On Purchases since in the report is called PurchaseTaxAmount while on for sales is called Tax On Sales coherently with the standard reporting.
  4. Last but not least, the most important and urgent one, the new custom reporting tool is missing a flag to choose if we are working under accrural base or cash base. I have same taxes that are under one or the other tax regime. Until that it will be usable only for accrural based taxes.

In the end what we are missing for a full reporting, together with accrural Vs cash base, is the possibility to have custom fields across different modules. I refer in particular to all kind of “invoices” which could and should share additional data.

The goal is to be able for custom reports to reproduce Tax Transactions report. I’m not there yet but I’m getting close.

Reverse charged taxes don’t show on tax transactions report as well, am I right?

Your approach is similar to what I’d do however not sure why you are making sure IsAccountsReceivable accounts are excluded since tax code cannot be set on these accounts anyway.

I think that you already reached the goal for these reports.

Right, the same result for Custom Report and Tax Transactions report. The fact is that both reports are wrong under a fiscal point of view.

Reverse charge means that the tax is applied twice, both on Sales and on Purchase side, not that is tax exempt.

I know that the result (Tax Liability) is zero but you have to show the increment both in Tax on Sales and in Tax on Purchases.

The correct approach is the one you have in Tax Summary.

You are right in my approach this filter is redundant. I started from another report which showed invoices so it excluded Accounts Receivable and the filter remained there.

@lubos, how about your approach for cash based reporting? Am I missing something, ie can Cash Based reporting be done with the current tools?

By the way, can you please re-expose generic Issue Date in Custom Reports?

I think that it would be great to have a submenu with:

  1. Standard (standard format of the date)
  2. MM/YYYY (month and year of the current date)
  3. Q/YYYY (quarter and year of the current date)
  4. H/YYYY (semester and year of the current date
  5. YYYY (year of the current date)

In this way we will be able to GROUP BY under these approaches.

1 Like

@Davide I agree exposing the date in custom reports is a good idea to support

  • Sorting by date then something else
  • Move the position date is displayed in a report or hide it if not relevant.

However the problem with fixed date periods is different jurisdictions use different periods for financial reporting. In a southern hemisphere country such as Australia the financial year is 1 Jul to 30 Jun next calendar year.

I think the generic solution is to support relative date periods using a format something like

RelativeDate = BaseDate +/- X months +/- Y days

Where BaseDate (a drop down list) can be

  • Today
  • Start week
  • Start Month
  • Start financial year (a new Manager business parameter)
  • Start of period (when specifying end of the period)

With month adjustments to achieve

  • Year = 12 months
  • Semester = 6 months
  • Quarter = 3 months

Day adjustments to achieve

  • Fortnight = 14 days
  • Week = 7 days
  • Last day month = First day next month - 1

Then allow custom reports to have a multiple date ranges like all other reports in Manager

Added the Base date option “Start of period” when defining end of period

That’s an advanced but complicated approach, useful for sure. I think that we can start from the standard ones which are very fast to implement and than add relative dates.

I don’t think is feasible directly in Custom Reports since they already choose what to put in columns (amounts, taxes, quantities) and what in lines. A GROUP BY goes under lines.

What you want is something that has to be set directly up in the date range settings so it implies a more in depth change of custom reports and it will apply only to the issue dates and not all the other dates.

Maybe I am wrong, but I think that this multi column need will be better fulfilled by new Report Transformation.

You are correct. A limitation of posting when not in front of a Manager business.

  • Report transformations supported multiple date ranges in the past but it appears that has been removed now.
  • Not all standard reports allow multiple time period, it’s about 1/3 of them
  • Supporting multiple date ranges is probably simpler in reports with a single numerical output (eg amount not credit & debit) as that requires supporting a group of columns for each time period.
  • You may well be right, report transformation may make this simple, however comparator time periods is a common requirement.

Standard reports allowing multiple time periods include

  • Profit and Loss Statement
  • Balance Sheet
  • Statement of Changes in Equity
  • Receipts & Payments Summary
  • Bank Account Summary
  • Cash Account Summary
  • Trial Balance
  • Tax Reconciliation
  • Sales Invoice Totals by Customer
  • Sales Invoice Totals by Item
  • Sales Invoice Totals by Custom Field
  • Payslip Totals per Item and Employee

That’s the main issue I was thinking for. A four column report with three period comparison will end up with 12 columns plus the descriptive ones.