Report Generator

1. november 2023


Benefits of the Report Generator

Create reports based on your needs
Sometimes the base reports a program offers don’t have the exact information you require. This is why we have the report generator - you can create your own very specific reports.

Keep an eye on your sales
The report generator is an amazing tool for creating sales-based reports.

Report Generator Overview

The report generator is a powerful tool, if you know how to use it. In this article, that is exactly what you will learn.

You can find the report generator in the back office, by navigating to Reports -> Report Generator
Here, you will have an immediate view of all of the reports you have created. You can view them, edit them, and delete them with complete ease.

The report generator has many tables you can activate, and in every table, there are many fields to activate. The complete overview of them can be found HERE.

Now let’s see how you can create a report.
Note: The examples in this article are from test accounts and show no information regarding actual people or companies.

This user guide has a lot of examples on building a sales report, on the assumption that such examples should make the report generator easier to understand. However, Erply has a quite comprehensive set of standard sales reports; most of these examples could just as well be solved by choosing the right standard report.

Create a Report

To create a report, navigate to Reports -> Report Generator -> Create Report.
Besides creating reports, you can also create Report Groups, which make it easier to find the exact report you’re looking for. We recommend using this when you have more than ten reports in your generator.

Once you create a report, you need to name it and select what it shows.
You can do this in the main information box.

From here you can also add a report group to the report, select display functions, and choose how it exports to excel. Let’s see what the display function does.

How to display report

There are two settings here: Show both individual rows and subtotals and Show subtotals and totals only

Note that this setting only matters if you have grouping enabled (more on that below).

Show both individual rows and subtotalsShow subtotals and totals only

Do note that the Show subtotals and totals only does not show all fields, since it collapses rows together. For a row to have a value in this view, you need to change the If grouping is used, what should be done with this column? field’s command.

Here is an example:

With Sales document total without a command.

With Sales document total with display total command.

Tables 

Here you can get down to the nitty-gritty of report generation. You can choose exactly what information is displayed to you.

Once you choose one of the rows in the Tables menu, others start lighting up.
When they light up, that means you can view those tables alongside the one you just chose.

You cannot choose tables that are not connected - the generator will refuse to display it by giving you this error: Report contains an error - selected tables cannot be joined.

For example, Customer and Product do not have anything in common and cannot be selected on their own.
In case you want to get a report “All items this customer has bought”, you will be building a report on sales data and you’ll actually need Sales Document + Sales Document Row instead.

Since you can select the Product column on Sales Document Row, and set a Customer filter on Sales Documents, you might not even need any other data objects.
Only if the report must contain further information about sold products — code, supplier etc. — it becomes necessary to incorporate Product as well.

Typical combinations are:
Sales Document + Sales Document Row – e.g. to report sales by product
Sales Document + Payment – e.g. to make a report that compares sales and payments
Sales Document + Customer – to report sales by customer group etc.

This is also a way you can filter the results. If, for example, you want to have a report of all gift cards that have been redeemed, but don’t want to see unredeemed gift cards, you can choose the table Gift card redeeming and Gift card. From the Gift card redeeming table, you can get information about the redemption of the gift card, but in order to get the gift card code, you need to activate the Gift card table as well.
However, you do not need to activate any fields from the Gift card redeeming table in order to only see redeemed gift cards from the fields in the Gift card table.

Fields

Every table has fields attached to it. These are the columns that you will see in the reports that are generated. 
In the report generator, you can choose which columns are visible to you when viewing the report by activating the Show in report box.

Group by

Fields in the report generator can be grouped in two ways, depending on which column you activate.
Group by I creates blocks, which can be used for subtotals, and group by II creates rows, where each row represents a total.

In these examples I will be grouping by document type and country.

Group Document Type by I (blocks) & Country by II (rows)Group Document Type by II (rows) & Country by I (blocks)

Show subtotals and totals only:

Show both individual rows and subtotals:

Show subtotals and totals only:

Show both individual rows and subtotals:

If grouping is used, what should be done with this column?

Select which columns you need to sum up – from a drop-down list select the option display totals. Generally fields need to have this applied for data to be shown in grouped view. This is especially so, when the display setting is set to show subtotals and totals only

Additionally, it may be interesting to know how many rows there are in each group.
For that, identify one field that is being displayed in the report but is not summed up.
From the same dropdown list you can select display row count.

Order by

Determine the ordering of rows in your report (by default no order is set). It is possible to have primary and secondary ordering.

Constraints and conditions (Filtering)

If you’d like to filter your report by, for example, specific clients or document types, you can use this column for that. There are many settings for filtering, and the best way to learn them is to simply play around with the program.
Do note that the column does not need to be visible on the report in order for the filtering to work.

For specific filtering, the “=” operator must be used. In this example, we will be filtering by sales document type (hold Ctrl key to select multiple).

For Date fields, the most useful filter operator is is within range.

Now, for reports you would like to keep using in the future, it’s smart to leave these value fields empty, that way you can set the filter every time you open the report.