Report Generator

 

ERPLY Books has a report generator that allows to compile specific user-created queries and create required reports.

The report generator can be opened at Reports -> Report Generator.

 

 

1. Simple query

 

You will need to fill in a minimum of two cells for adapting a query: ‘’Tables’’ and ‘’Outputs‘’.

Tables – determines the database table from which the queries are made. You can select information about invoices, payments and receipts, contacts, the account plan, entries, and other data. You can select one table or, if you wish to see more data in the report you have created, multiple tables.

 

 

Outputs – allows choosing various outputs for previously selected tables(invoices, payments/receipts, contacts, etc.). Only the data selected in the Outputs cell will be displayed.

 

 

The images above illustrate a search for the purpose of preparing a report about invoices and issue the following information about them:

  • date of creation,
  • client/supplier,
  • date
  • deadline
  • invoice number
  • type

 

When you press ”Search”, a report with all the selected data will be displayed, as shown on the image below:

 

 

Example 1: You want to display information on all dates of creation.

  • Tables: Invoices
  • Outputs: Invoices. Creation Date

Example 2: You want to see information on transactions sizes and dates.

  • Tables: Transactions
  • Outputs: Transactions.Creation Date and Transactions.Sum

 

 

2. Specifying a query

 

If you wish to specify your query, you can set conditions for this. For example, you wish for information on all invoices created after a certain date or would like to find all clients with a similar first name.

Too add a new search parameter, press ”Add Filter”. This will create a new row, where you will need to fill in the following fields:

  • Parameter – choose the parameter you wish to specify. For example, you can specify the date, sum, registry code, etc.
  • Type – choose the operation you wish to carry out with the parameter
  • Value – choose the value with which you wish to compare the parameter

 

 

Example 1: You wish to display the dates of creation of purchase invoices with the names of the suppliers. The search should be formulated as follows:

  • Tables: ‘Invoices’
  • Outputs: ‘Invoices. Creation Date’, ‘Invoices. Customer/Vendor’

Then, add a new parameter with the following values:

  • Parameter: ‘Invoices.Type’
  • Type: ‘Equals’
  • Value: ‘Purchase Invoice’

 

Example 2: You only want to display the dates of creation of invoices created before 4 March. The search should be formulated as follows:

  • Tables: ‘Invoices’
  • Outputs: ‘Invoices. Creation Date’

Then, add a new parameter with the following values:

  • Parameter: ‘Invoices. Creation Date’
  • Type: ‘Less than’
  • Value: ‘4 March’

 

Example 3: You wish to display only the contacts whose names contain ‘OÜ’:

  • Tables: ‘Contacts’
  • Outputs: ‘Contacts.Name’

Then, add a new parameter with the following values:

  • Parameter: ‘Contacts.Name’
  • Type: ‘Contains’
  • Value: ‘OÜ’

 

Example 4: Similarly to example 3, you can also display transactions:

  • Tables: ‘Transactions’
  • Outputs: ‘Transactions.Description’

Then, add a new parameter with the following values:

  • Parameter: ‘Transactions.Description’
  • Type: ‘Contains’
  • Value: ‘Arvutid OÜ’

 

 

3. Multiple parameters

 

You can specify your queries by adding more parameters. In such cases, you will only see the results for which all the chosen parameters are true.

 

Example 1: You only wish to display invoices created between 3 March and 6 June (after 3 March AND before 6 June)

  • Tables: ‘Invoices’
  • Outputs: ‘Invoices. Creation Date and ‘Invoices.Type’ and ‘Invoices.Sum’

Then, add a new parameter with the following values:

  • Parameter: ‘Invoices. Creation Date’
  • Type: ‘More than’
  • Value: ‘3 March’

Then, add the second parameter by pressing ”Add filter”:

  • Parameter: ‘Invoices. Creation Date’
  • Type: ‘Less than
  • Value: ‘6 June’

 

Example 2: You wish to only display the transactions that contain the words ‘Arvutid OÜ’ AND are carried out after 6 June:

  • Tables: ‘Transactions’
  • Outputs: ‘Transactions.Creation Date’ and ‘Transactions.Description’ and ‘Transactions. Sum’

Then, add a new parameter with the following values:

  • Parameter: ‘Transactions.Description’
  • Type: ‘Contains’
  • Value: ‘Arvutid OÜ’

Add the second parameter as follows:

  • Parameter: ‘Transactions.Creation Date’
  • Type: ‘More than’
  • Value: ‘6 June’

 

 

3. OR parameter

 

Queries can also be specified by adding alternative ”OR” parameters to existing parameters. This means that if one or more of the set parameter values apply to the result, it will be displayed.

 

Example 1: This example is similar to Example 2 above. You wish to see transactions for which at least one of two statements applies: they either contain the words ‘Arvutid OÜ’ OR are carried out after 6 June. If the query is made correctly, the number of resulting queries should equal or exceed the number shown in Example 2.

Please note! If a parameter contains OR parameters, the parameter is true also if all OR parameters are true.

  • Tables: ‘Transactions’
  • Outputs: ‘Transactions.Creation Date’ and ‘Transactions.Description’ and ‘Transactions. Sum’

Then, add a new parameter with the following values:

  • Parameter: ‘Transactions.Description’
  • Operation: ‘Contains’
  • Value: ‘Arvutid OÜ’

Press OR:

  • Parameter: ‘Transactions.Creation Date’
  • Operation: ‘More than’
  • Value: ‘6 June’

 

 

4. Making queries through the API

 

To use the Report Generator via the API, you will need to send POST queries to the address:

You will also need an API key, which you will find if you open Settings -> API settings on ERPLY Books. For example, with the key ‘25is6ho0tld7kft9b56rg6l3feeqr6ckiaatjuu’, the full query is as follows:

https://accounting.erply.com/api/report_generator?token=25is6ho0tld7kft9b56rg6l3feeqr6ckiaatjuu

More information: Report Generator API Documentation

 

Report to several organisations at once

You can add multiple organisations for one report if you choose “Other options”.

Next you will see that several fields have been added. Here choose organisations you want to include in the report.

 

Further information about Report Generator you can find from the following video: