Report Transformations - No-code development platform

The latest version (21.6.54) contains new no-code development platform to create report transformations.

This should make it a lot easier to create country-specific reports such as GST Return for New Zealand.

Report transformations are under Settings then Report Transformations.

image

And the report transformation for GST Report in New Zealand looks like this:

No coding. Just a table to define what each cell in the report should contain.

The real power of report transformations is that when they are created, they can be submitted to our Github repository. All the report transformations from Github are then automatically included in Manager for the use of everyone.

Currently report transformations can pull in the following figures:

  • Tax code totals - total sales, net sales, tax on sales, total purchases, net purchases, tax on purchases & tax liability (this is useful for country-specific VAT, GST, Sales tax etc reports)
  • Payslip item totals per custom field (this is useful for country-specific payroll reports)

Also you can pull in text fields (both built-in and custom fields) from business details, suppliers and employees.

I will add more based on what’s needed. For example, we could add support to be able to pull in P&L account balances. This would be useful for country-specific income tax worksheets.

So let’s create our first report transformation.

Go to Report Transformations and click New Report Transformation.

image

You will get empty form.

Enter the name for your custom report. E.g. VAT Return Worksheet. Select how many columns your report contains. Currently you can choose either two or three.

For example, New Zealand report example has two columns but there are layouts which require three columns. For example, VAT Return for Saudi Arabia:

The first column is always plain text. The second and third columns are calculated figures you choose from dropdown.

Going back to our empty form, just define the structure without selecting any figures in the second or third column:

And then click Create to save the report transformation.

Once the report transformation is created, go to Reports tab.

You will see your new report available.

image

Create new report and view it. This is what you will see.

image

It doesn’t do anything but it’s a good start. You will notice both New Zealand and Saudi Arabia reports have headers which separate the report into sales, purchases and summary. We can do the same in our report.

While viewing the report, click Layout button in bottom-right corner.

image

This will take you to report transformation edit form.

To create a header, simply create a line item where the second line will contain sequence of dashes ------

image

Save the report and you will see:

image

Now, let’s add the figures. This assumes you already have tax codes created.

For the purpose of this excercise, I’ve created tax code VAT 20% and selected appropriate figures like this:

image

Now, when I view the report I see:

image

The amounts are zero because in my test database I have no transactions but the report is already working. All the figures are clickable.

When selecting figures on report transformation, you can select multiple figures in which case the figures will be added up. You can see this on New Zealand report transformation where one field contains total from multiple tax codes.

image

On report transformation form, there is also set of checkboxes at the bottom.

image

When Suppliers or Employees checkbox is clicked, it allows to create loop for each supplier or employee. This is useful when country-specific report needs to show figures per each supplier or employee.

For example, in Australia, some industries need to report all taxable purchases per each supplier:

So Supplier checkbox would be checked which will allows to create report which will contain totals per supplier.

The same is useful for employees when business needs to report totals per individual employee.

When Description checkbox is checked, you can provide description of the report.

image

When Instructions checkbox is checked, you can provide instructions how the report should be used.

image

This will be shown under report like this:

Of course, country-specific report transformation does not have to be in English. It can be in any language.

6 Likes

Thanks a lot for giving this update.

Well done. I think that is very impressive. Pity I can’t use it, as I already have the UK VAT worksheet. However, the VAT form may end up changing as we are no longer in the UK so?

Does this mean that we can’t use code now?

thank you so much Lubos and team for all the new updates but my question some countries don’t have localization so how to create reports transformation

How can we make calculations based on the data we get from Report Transformations. For example subtract two different amounts?

I suspect you just enter the constituents of both prior cells, which will show the total of both combined and subtract expenses from income amounts. Hopefully that’s what you wanted to do.

I don’t think the interface supports

  • intermediate value rounding
  • actual arithmetic with intermediate values.

That appears to be the case.

Thanks Lubos for this new function. When I try to create a suppliers report called Opgaaf ICP, the “where-is” section doesn’t give me any option to choose. What do I do wrong.
In the EU/NL we have to submit together with the VAT return form a form, showing the socalled intracommunautaire transactions. The report has to show the country code,the Customer-VAT registration nummer and the total net sales in a given period of time
Could you please add Customers/Debtors to the list of options. It would be very handy if two custom codes, representing Country and VAT registration code could be selected as well. This would be beneficial for all EU users.
It would even be better if “Country Code” is added as a databasetable to Customers and Suppliers

Kind regards,
Hennie

I hope that that’s temporary, I have made many reports using the old transformations and my business relies on it.

This new implementation is way too limited to be useful to me.

I hope @lubos reenables custom report transformations using code.

1 Like

Thank you @lubos, just a quick question, would you consider to include at least some basic arithmetic operations rather than automatic additions of the selected figures?

1 Like

Thank you so much for this valuable update … i tested this function and how much it add value for the application …

The approach to developing electronic transmission files is interesting, as that aspect is becoming a critical part of modern accounting software. Demonstrating how to generated CVS files for export does appear clean however a couple of things concern me

  • Hiding the control structures give an artificial impression of simplicity until someone tries to actually use it to generate a specific file. Clear documentation will be essential to avoid everyone reverse engineering the interface and having their report later fail when undocumented program behaviour is changed. Such as what goes in the header line, what goes in data lines, what triggers a new record, what delimiters are used, are delimiters in the data escaped, etc.

  • XML is likely to be more important than CSV moving forwards. How is it envisioned XML will be incorporated into Report transformation?

I suppose a way of incorporating XML into the current Report transformation development platform would be to add an export drop down menu along side the columns menu with options

  • None
  • CSV
  • XML

Selecting other than None would enable a column to specify the export files contents. A “Show Export file contents” option should also be added Reports → Report Transformation → < Specific Report transformation > → report definition.

1 Like

To do that the Report Transformation environment would need the capability to Name / reference prior results not just Figures predefined in the report development environment. Enabling such functionality would require a slightly different approach to Report Transformation - No-code development platform. Such as discussed here, where the “Figures” definition is exposed and names given to “Figures” could be subsequently used in other parts of the Report transformation.

I strongly support the addition of this capacity as it would enable inclusion of rounding consistent with individual countries tax authorities requirements. Such as

Similarly the ATO GST Worksheet also specifies rounding down of all intermediate values Complete your BAS | Australian Taxation Office

Note
Calculation of returns with rounding would also enable calculation of a “Rounding adjustment” to deduct from the “GST/VAT Payable” account when GST/VAT is paid in accordance with a countries tax authorities rounding requirements.

In this case, create new business for the country that doesn’t exist yet. Create your tax codes and report transformation using them. Then this can be exported to JSON file which can be uploaded to Github. I will provide guide to explain step-by-step how to contribute new country or how to improve existing country.

At this point, only additions are supported. I will need use-case why subtraction is needed. So far it wasn’t needed for existing country-specific reports. If it’s needed for some new one, I will find the way to support it one way or another.

Two considerations. If you need to loop through all suppliers, then choose nothing in where-is section. If you need to loop through only specific suppliers, then this can be controlled using custom field. You will need to create custom field on supplier-level and custom field needs to be a drop-down.

Example:

image

Then on report transformation this custom field will be available.

Loops for customers haven’t been implemented because it wasn’t required for any country-specific report yet. If there is country-specific report which require looping through customers. Just start new topic and will get it done.

Is there country-specific report which requires this? The scope of this feature is to faciliate country-specific reports. It’s not to faciliate anything because total flexibility would be possible with coding only. And I want to stay this within no-code zone because that’s the only way to make creation of country-specific reports easy.

XML or JSON would suit better. I will add support for all three formats or any format that is useful. What I would like to avoid is to add ability to create custom structures. That would probably require coding.

So it would be better if websites which do electronic submissions can simply accept Manager CSV, XML or JSON output as it’s generated.

Hi lubos

I’m having a hard time recreating the reports that I used to have using the “No Code” method, specifically the PAYG value I need

As a sole trader, I don’t have payslips, so all the the payslip earning values are empty (or non-existent). When I could write my own code, I calculated my total earnings (Total Sales - Total Purchases etc) then multiplied that number be my estimated tax rate to find my PAYG amount owing. There no longer appears to be anyway to do anything like this.

The available fields in the reports don’t seem to cover what I want anymore. Is there a work around to get the values I need, or am I back to spitting out the values I can get and doing the rest by hand.

Thanks
Andrew

@andrewbaglin what are you trying to achieve? Some kind of Income Tax Worksheet where you can see estimate income tax?

Just trying to calculate the quarterly PAYG component I need to pay the ATO as part of my BAS. Because my income can vary so much quarter to quarter, I use a percentage of my income for that quarter as the PAYG amount.

I effectively want to use the “Business Activity Statement - Full” template but replace the PAYG tax withheld section values W1 & W2 with

T1 - Earnings for the qtr (i.e. sales (ex gst) - purchases (ex gst)
5A - 30% of T1

Currently I have no solution but this will continue to evolve so eventually I will find the way how to incorporate this without using the code. I want to support ability to have income tax worksheets which would require ability to estimate income tax based on profit.

1 Like

Thinking about this further a possible Report writer interface is:

  • A menu to select the type of file to be exported
  • Cells in that column enable selection of a data element or valid data delimiter for the chosen file type.
  • Entering a data element would consist of entering the data element name (column heading in a cvs file, element name in structured data like xml) and which report column the data value is taken from.

This would simplify generation of electronic communication data. I think it would cover all static data structures, which I suspect is by far the majority.

The only exception I’m aware of is the “Allowance - Other” in the Australian single touch payroll which uses a dynamic data structure consisting of data labels

  • Other allowance [ integer 1 … 20 ] description
  • Other allowance [ integer 1 … 20 ] value

Where the description is a pay type specific to a group of employees such as “Jobkeeper-Topup”, so in Manager terminology it is a payslip item name. I suspect this is rare in data communication standards so could be ignored. If not supporting it could be done by allowing the data element name to be dynamic (reference a cell not static text) and allow more general control structures (not just “Supplier” or “Employees”). Hopefully this isn’t required as it risks complicating the interface.

Report writer control structures

The current No code report writer supports looping for

  • Suppler
  • Employee

If a spread sheet style layout was used these could continue to be implemented using the current interface, perhaps enhanced by showing the loop boundaries in a grey area with labelling.

An alternative is to enter the control structure in a similar manner to entering a data value in column A. Doing so would result in the same changes as described above. While this approach would result in a more flexible report writer architecture, it may involve too much programming to be viable in the near term.

@lubos Can I suggest a third road?

In the past you had the form designed in such a way that it required coding to do something as simple as update the report transformation. Now you have it where it is code free to do simple things like the report transformation.

The problem with the original design was that all end users had to have some knowledge of coding to do even the simplest report. The problem with the new design is that the functionality is limited to what reports can be created without using the code.

Why not have a code free development module for generic things like country report transformations and other commonly used reports and have a separate advanced report development module for more specialised reporting needs.

Or at the very least have both modules until you have found a way to produce the reports requested in a code free way. This would enable users currently using the old report system to continue to use it without any loss of functionality which they are now currently experiencing.

Having said that, I am not clear on why Andrew needs to create a custom report using coding to determine what his payslip is as I would have thought his requirement would be applicable to all businesses that run as a sole trader? Should there not be a sole trade payg monthly net profit report?

1 Like