ERPLY Books’s Report Generator

ERPLY Books’s Report Generator

User Manual

 

support@erplybooks.com

Last edit: 24.09.2015

Table of Contents

Table of Contents

  1. Introduction
  2. Queries

2.1. Simple Queries

2.2. Detailed Queries

2.3. Several Parameters

2.4. The “OR” Parameter

  1. Creating Queries Through the API

3.1 General

1. Introduction

ERPLY Books features a report generator. It lets you create user specific queries. Also, easily generate reports suitable for your needs. Start by clicking Reports > Report Generator.

report-generator-en

2. Queries

2.1. Simple Queries

To make the simplest queries, you only need to fill in 2 fields – tables and output.

Tables – This field specifies, which tables are addressed. For example, if we wish to get info about invoices, we need to choose the “Invoice” option here.

Output – Tables supported by the report generator contain lots of information. Including most of which we may not wish to display. However, the data we do need to display has to be selected from this field. For example, when creating a report about invoices, we may not want info about the invoice’s currency type or it’s VAT percent. But, we may need info about the invoice’s creation date and sum. In that case, these options should be selected from this field.

NB! Regardless of which fields are selected in the “Output” field, the query remains the same. The output selection only specifies the amount of data that is actually displayed to the user from each table. Please take this into consideration when reading the examples provided in this manual.

Example 1: We create a query that outputs the creation date of all the invoices within our organisation.

  • Tables: “Invoices”
  • Output: “Invoices.Creation Date”

Example 2: We create a query that displays data about bank transactions. It shows us only the creation date and the sum of each transaction.

  • Tables: “Transactions”
  • Output: “Transactions.Creation Date” and “Transactions.Sum”

2.2. Detailed Queries

In most cases, we do not want to display all of the data that a table contains (like in the previous example). For example, we may only need invoices that were created after a certain date, transactions, that were connected to a specific invoice, etc. In these cases, we would have to specify our queries. To add a new search parameter, click “Add”. This creates a new row, which has the following fields:

Parameter – Choose the parameter you wish to specify.

Operation – Choose an operation.

Value – And finally, choose a value to compare the parameter with.

Example 1: We create a query that searches for the creation date of sales invoices. All other invoices are ignored.

  • Tables: “Invoices”
  • Output: “Invoices.Creation Date” and “Invoices.Type”

Now we add a new parameter with the following values:

  • Parameter: “Invoices.Type”
  • Operation: “Equals”
  • Value: “Sales Invoice”

Example 2: We create a query that only returns invoices created before the 4th of March.

  • Tables: “Invoices”
  • Output: “Invoices.Creation Date”

Now we add a new parameter with the following values:

  • Parameter: “Invoices.Creation Date”
  • Operation: “Less than”
  • Value: “4th of March”

Example 3: We only find the organisations that have the word “sport” inside their name.

  • Tables: “Contacts”
  • Output: “Contacts.Name”

Now we add a new parameter with the following values:

  • Parameter: “Contacts.Name”
  • Operation: “Contains”
  • Value: “sport”

Example 4: We create a query similar to example 3, but this time searching for transactions:

  • Tables: “Transactions”
  • Output: “Transactions.Description”

Now we add a new parameter with the following values:

  • Parameter: “Transactions.Description”
  • Operation: “Contains”
  • Value: “ABC Sports Inc.”

2.3. Several Parameters

Adding several parameters to a query will return only the results that satisfy all of the parameters.

Example 1: We create a query that returns invoices that were created between the 3rd of March and the 6th on June:

  • Tables: “Invoices”
  • Output: “Invoices.Creation Date” and “Invoices.Type” and “Invoices.Sum”

Now we add a new parameter with the following values:

  • Parameter: “Invoices.Creation Date”
  • Operation: “Larger than”
  • Value: “3rd of March”

Now we add the second parameter:

  • Parameter: “Invoices.Creation Date”
  • Operation: “Less than”
  • Value: “6th of June”

Example 2: We want to find transactions that have a description containing “ABS Sports Inc.” and were created after the 3rd of March:

  • Tables: “Transactions”
  • Output: “Transactions.Creation Date” and “Transactions.Description” and “Transactions.Sum”

Now we add a new parameter with the following values:

  • Parameter: “Transactions.Description”
  • Operation: “Contains”
  • Value: “ABS Sports Inc.”

Now we add the second parameter:

  • Parameter: “Transactions.Creation Date”
  • Operation: “More than”
  • Value: “3rd of March”

2.4. The “OR” Parameter

You can also specify your queries by adding alternative “OR” parameters to already existing  parameters. This returns the results that satisfy one or more of the “OR” parameters.

NB! This query also returns results that satisfy both of the “OR” parameters. It does not have to be one or the other.

Example 1:

This example is similar to the previous example 2. We create a query that returns transactions that either contain “ABC Sports Inc.” OR were created after the 6th of June. If created correctly, this query should return an equal or larger amount of results than in example 2. It should definitely not return less.

.

  • Tables: “Transactions”
  • Output: “Transactions.Creation Date” and “Transactions.Description” and “Transactions.Sum”

Now we add a new parameter with the following values:

  • Parameter: “Transactions.Description”
  • Operation: “Contains”
  • Value: “ABC Sports Inc.”

Now we click the “OR” button and enter:

  • Parameter: “Transactions.Creation Date”
  • Operation: “More Than”
  • Value “6th of June”

3. Creating Queries Through the API

3.1 General

In order to use ERPLY Books’s Report generator through the API, you need to send POST requests to https://accounting.erply.com/api/report_generator. Additionally, you have to provide a token. For example, if your token is 25is6ho0tld7kft9b56rg6l3feeqr6ckiaatjuu, then the complete URL will look like this: https://accounting.erply.com/api/report_generator?token=25is6ho0tld7kft9b56rg6l3feeqr6ckiaatjuu.

Find out more at http://erplybooks.com/report_generator_dictionary.php