How to implement country-specific report in Manager

[lubos]

Manager includes programming framework which allows to create variety of highly-customizable reports. This framework is primarily used to implement country-specific reports.

Currently, Manager includes 9 country-specific reports but there are almost 200 countries and every country has several country-specific reports which businesses require.

The goal of this framework is to allow community from around the world to author and submit these reports so they can be merged with the program (just like translations are at the moment) and can benefit everyone using the program.

This topic serves as a reference guide how to author country-specific report.

Getting Started

Go to Settings tab, then Report Transformations and create New Report Transformation.

Name it Test and put something into Script field such as Testing 123.

After you create this report transformation, go to Reports tab and you will see Test report in the list of reports.

You can create the report under Test and view it.

You will get:

The report doesn’t do anything. It just prints what you have entered into Script field. This is not very useful but it’s a good start.

Script

Typically, you want your report to pull some figures from general ledger and show them (e.g. tax totals)

Script field accepts Liquid markup

The most important variable is {{ transactions }} which represents general ledger. Go ahead and edit Report transformation and put {{ transactions }} into Script field.

Then view your report under Reports tab.

You will see zero which is clickable.

Clicking on zero will show you all general ledger transactions in your business and the number is zero because sum of all general ledger transactions is zero (double-entry accounting principle)

This is still not very useful but we are getting somewhere…

Let’s say we do not want all transactions, we want only transactions for specific tax code. This is where liquid filters come into picture.

First we need to get GUID identifier of the tax code. Go to Tax Codes and click Edit on tax code you are interested in. Look into top-right corner to find its GUID.

Copy and paste it to avoid typo. Now that we have the tax code identifier, we can use it in our script like this:

Or we can make the code more readable by assigning tax code identifier to a variable and then using the variable like this:

Now when I view my report, I will see:

This is because the balance of all transactions with tax code VAT 20% is credit 2,000.

You can click on the amount to see which transactions are behind the balance.

You will notice there is a problem. It shows transactions with your tax code for entire history of the business but we typically only want transactions for the period of time.

This is where new variables called {{ report.From }} and {{ report.To }} come into picture. You can apply another filter to get transactions only between those two dates like this:

Notice between: report.From, report.To filter.

This will give us tax inclusive total of transactions subject to VAT 20%.

If you want only tax amounts on sales subject to VAT 20%, then you’d write something like this:

Notice tax_on_sales filter.

There are more filters you can use such as:

• tax_amount
• tax_exclusive_amount
• tax_on_purchases
• tax_exclusive_sales
• tax_exclusive_purchases
• tax_inclusive_sales
• tax_inclusive_purchases
• group_by_supplier
• group_by_employee
• where_payslip_earnings_item
• where_payslip_deduction_item
• where_payslip_contribution_item
• reverse_sign

More filters will be implemented, these are currently supported as they were needed for already implemented country-specific reports.

Real-world examples

The report should not just display the figures, it should be also professionally presented. For example, this is how real-world example would look like for Australian Business Activity Statement where tax office requires:

• Total sales including any GST 10%
• Tax on GST 10% on sales
• Tax on GST 10% on purchases
• Total salary & wages
• Payroll tax withheld

Notice how script will define variables which are then used in Layout section.

You can have a look at more real-world examples by importing report transformations for various countries which have been already implemented.

Submissions

Would you like to implement country-specific report for your country and make it part of Manager?

Great! I’m still working on submission system where you will be able to submit your demo file with country-specific report. For the time being, feel free to experiment with the framework and see what you can create.

[Patch]

@lubos

will be able to submit your demo file with country-specific report

Attaching a test business to a forum post in a country specific thread maybe all that is needed. The test business could contain:

• One or more suggested report transformation for a jurisdiction

• any tax codes required (although there maybe some value in retaining the prior localisation tax codes until all report transformations are written to allow easy adoption of new reports)

• Test data is optional as other forum members could enter and data and provide verification prior to general distribution.

[Mark]

@lubos

First we need to get GUID identifier of the tax code. Go to Tax Codes and click Edit on tax code you are interested in. Look into top-right corner to find its GUID .

From which version is this code visible? In v20.8.67 it isn’t (although I guess they can be copied from the localised Report Transformation ‘Concept BTW Aangifte’ for The Netherlands?).

[lubos]

@Mark

From which version is this code visible?

This was only added today so you need the latest version.

[Davide]

@lubos

Submissions

Would you like to implement country-specific report for your country and make it part of Manager?

Great! I’m still working on submission system where you will be able to submit your demo file with country-specific report. For the time being, feel free to experiment with the framework and see what you can create.

I’ll start next week to work on the italian version.

It would be great if you can bring back the online cloud version of Manager for the localisation on which I used to work for italian Tax Codes.

The Italian reporting for taxes relays on the Custom Fields for Tax Codes that I have created. How can I query/filter them in Report Transformation?

[Patch]

@lubos

There are more filters you can use

Lubos would it be possible for you to document what parameters each filter expects, it’s input and output data type.

This information can be guessed by reverse engineering however doing so is a barrier to entry and less accurate.

@Davide

Custom Fields for Tax Codes that I have created. How can I query/filter them in Report Transformation?

David
Is it possible to effectively hard code the custom field values in the report transformation by just listing all tax code with a particular custom field value. That appears to be what has been done in the Dutch transformation which maybe similar to the Italian requirements

[lubos]

@Davide I’ve added new filter called where_tax_code_custom_field to the latest version (20.9.45).

So if your custom field has GUID 21cfb1fb-b82e-466d-814c-244dfcea7e20 and you want transactions where custom field value is XYZ then you use filter:

where_tax_code_custom_field: “21cfb1fb-b82e-466d-814c-244dfcea7e20”, “XYZ”

[Davide]

Thanks

[Davide]

Dear @lubos, is it possible to show directly in the report a line for each transaction like in the Tax Transaction report?

If so, can you provide an example?

[Patch]

Yes
You need a flow control loop.
Have a look at the localisation for Australia

• Taxable Purchases by Supplier report illustrated it well
• Single touch pay roll has a large complicated loop

[Davide]

Thanks

[lubos]

Those loops are actually grouping tax transactions by supplier or employee. It’s not looping through individual transactions. I just made it possible in the latest version (20.9.47) to loop through individual transactions.

{% assign myTransactions = transactions | between: report.From, report.To %}
{% for e in myTransactions %}
{{ e.Date }} {{ e.Account }} {{ e.BaseAmount }}
{% endfor %}

There are far more attributes but not everything is supported yet.

[novica]

I hope updating doesn’t break existing country-specific reports already created under report transformations.

[Davide]

It seems that I cannot apply the same filters, ie

• tax_amount
• tax_exclusive_amount
• tax_on_sales
• tax_on_purchases
• tax_exclusive_sales
• tax_exclusive_purchases
• tax_inclusive_sales
• tax_inclusive_purchases
• reverse_sign

to individual transaction. If I am not wrong I think that I should start by reproducing both Tax Summary and Tax Report and then step by step implementing the Italian reports.

[lubos]

Those filters are applicable to array of transactions. If you are enumerating through individual transactions, then you need to use attributes such as…

{% assign myTransactions = transactions | between: report.From, report.To %}
{% for e in myTransactions %}
{{ e.Date }} {{ e.Account }} {{ e.TaxExclusiveSalesAmount }} {{ e.SalesTaxAmount }} {{ e.TaxExclusivePurchaseAmount }} {{ e.PurchaseTaxAmount }}
{% endfor %}

I will upload documentation so you can see list of all attributes which are possible.

[Patch]

@lubos Lubos
Is there any chance the filters could mimic the custom reports eg

• Where: table_specifier, Field_specifier
• Select: table_specifier, Field_specifier

It may reduce the learning curve of user transition between them

[Davide]

Thanks.

Today I’ve checked everything with my chartered accountant.

Before I start implementing I would like to ask how long do you think it will take:

1. to have custom fields shared at least through all the fiscal documents (sales invoices, credit notes, purchase invoices and debit notes);
2. to extend all typologies of custom fields at line level (again, our need is for fiscal documents).

This would change a lot how I could make Manager compliant to the italian law.

[lubos]

@Patch I think that’s what I will end up doing.

@Davide the latest version (20.9.51) is exposing almost all the data in the database. To see what global variables are available, create report with just {{ globals }} which will reveal list of top-level global variables.

Most of them are enumerable which means you would use them such as:

{% for e in customers %}
{{ e.Name }}<br />
{% endfor %}

To discover what fields are available, there is new fields filter which can be applied to individual object.

E.g.

{{ customers | first | fields }}

Will show

So this way you can use reflection on your variables to see what’s available without referring to documentation.

[Ealfardan]

Manager.io is becoming a stronger tool by the day.

Thanks a lot @lubos

[Patch]

Agree
The changes Lubos announced today is a massive increase in reporting functionality.

Filters mimicking custom report options should also capitalise on user custom report writing skills facilitating enhanced functionality when required. It may also simplify documentation as users will be able to guess parameters and output by comparison with custom reports.