Using the Reporting API

The Reporting API provides endpoints for querying your payments history and extracting raw transaction data to a report in csv format. Extracting report data programmatically gives you some flexibility not available when creating a report through the Control Center UI. For instance, you can use the API to create more advanced filter criteria to increase the granularity of the data extracted to a report.

• Generating Reports
• Report Types
• Setting the Report Delivery Destination
• Scheduling Reports
• Report Column and Filter Values

Generating Reports

A report is always based on a template, which has all the details in place for generating a report. Details include the report name, the columns you want to add to the report, the time period to use for the report and any filters you want to apply to the data when the report is generated.

At a high-level, creating a report is a two-step process:

1. Create the report template.

2. Create a report request. This submits a request to generate a report based on a specific report template.

Depending on the amount of data you are requesting, reports may take some time to generate. Reports are therefore generated asynchronously and you will be notified by mail when the data is available for download. You can specify the email address for notifications when creating the report template. If not specified, we will use the email address associated with the account of the user for whom the authentication session was created.

Report Types

You can generate two types of reports: transaction reports and settlement reports:

• A transaction report provides a detailed breakdown of the transactions passing through your account.

• A settlement report provides a detailed breakdown of the payments settled to your bank account, including additional information such as fees and taxes that were deducted from the transaction.

You specify the type of report to create, by passing either payments (for a transaction report) or settlements (for a settlement report) as the value of the template_context field in the Create a Report Template request body:

{
   "template_data": {
   "columns": [],
   "filters": [],
   "user_defined": []
   },
   "template_context": "payments", // Pass either 'payments` or `settlements`
   "template_name": "Authorized Payments",
   "description": "Authorized payments for transactions in USD."
}

The columns and filter values you can pass when creating the report template will differ, depending on the type of report you are creating. Refer to the Create a Report Template API reference for a list of allowed values for each specific report type.

Setting the Report Delivery Destination

Reports can be delivered to either your email inbox or to a folder on the PaymentsOS SFTP server. If you want your reports to be delivered to your email inbox, simply specify your email address in the notification_email field when invoking the the Create a Report Request API.

{
  "date_range": {
    ...
  },
  "report_name": "payments_report",
  "report_template_id": "ec06b4e8-0260-4af6-9ff2-52910ed87c83",
  "notification_email": "nhoj@atlovart.com", // Deliver the reports to an email inbox
  ...  
}

To deliver a report to a folder on the PaymentsOS SFTP server, first make sure you already created the folder (see Storing Reports on the PaymentsOS SFTP Server below). Then specify the name of the folder in the request body of the the Create a Report Request:

{
  "date_range": {
    ...
  },
  "report_name": "payments_report",
  "report_template_id": "ec06b4e8-0260-4af6-9ff2-52910ed87c83",
  "sftp_folder": "reports", // Deliver the reports to a folder on the SFTP server
  ...  
}

Storing Reports on the PaymentsOS SFTP Server

To store reports on the PaymentsOS SFTP server, you need to create a folder on the SFTP server, as well as a user that can access the folder and download the reports. Access to the SFTP server uses SSH key authentication, so before creating the folder and user you will first need to create an SSH public and private key pair. The public key will be uploaded to our server; the private key remains with you and will allow your SFTP user to authenticate when connecting to the PaymentsOS SFTP server. There are several utilities out there for generating SSH key pairs, so just choose one to generate the keys. With that in place, proceed to invoke the Create an SFTP User and Folder request. In the request body, pass the public key you generated, as well the name of the folder you want to create:

{
  "public_key": "ssh-rsa AAAAB3NzbC1yc2EAAAADAQABAAABAQC5JctNQUxjja4D9o/QC0fAEZW6MmH9...",
  "folders": [
    "reports"
  ]
}

Notice that you do not pass a user name. That is because we will create a user for you, with a randomly generated user name. The user name is returned to you in the response of the Create an SFTP User and Folder request. Here's a sample response:

{
    "public_key": "ssh-rsa AAAAB3NzbC1yc2EAAAADAQABAAABAQC5JctNQUxjja4D9o/QC0fAEZW6MmH9...",
    "username": "3aw13htco6l",
    "folders": [
        "reports"
    ],
    "created": 1602664036465,
    "modified": 1602664036465
}

You can now use an SFTP client to connect to the PaymentsOS SFTP server. To setup the connection you will need the user name and private key (we already created those), as well as the following PaymentsOS SFTP server address:

sftp.paymentsos.com (port 22).

Once connected, you can download the generated reports from the folder on the SFTP server.

Updating the SFTP Storage and Access Details

If at any stage you want to rename the folder or update the SSH public key, you can do so using the Update the SFTP Storage and Access Details API request.

If needed, you can also delete a user by invoking the Delete an SFTP User request. Note that this request does not delete the user's folder, so you can always create a new user and specify the same folder name to regain access to that folder (the contents of the folder will not be visible unless there's a user connected to it).

Scheduling Reports

You can use the reporting API to define a schedule that will automatically create a report every day, week or month. Using the API to define a schedule is easy. Simply invoke the Create a Report Schedule request, passing in all required schedule information.

Filtering the Report Data

If you want to retrieve a subset of your data, you can do so by passing in a filters array in the template_data object of the Create a Report Template request. Here's an example:

{
  "template_data": {
    "columns": [
      {
        "column": "transaction_type", // Exports the column transaction_type to the .csv file
        "display_name": "Payment Status"
      },
      ...
    ],
    "filters": [
      {
        "column_name": "currency", // Places a filter on the currency column
        "filter_type": "equal",
        "filter_values": [
          "USD", "EUR" // Exports rows with a currency value of USD or EUR
        ]
      },
      {
        "column_name": "transaction_type", // Places a filter on the transaction_type column
        "filter_type": "equal",
        "filter_values": [
          "authorization" // Exports rows with a transaction type of authorization
        ]
      }
    ],
    ...
  },
  ...

The filer logic applies the OR operator between multiple filter values and the AND operator between multiple filter objects. In the example above, this will result in the following filter:

(currency == "USD" || "EURO") && (transaction_type == "authorization")

There are three additional items to note:

• You do not have to export the column to which you want to apply a filter. Notice that in our example above, we do not export the currency and transaction_type columns (we only apply a filter to them).

• Filter values must be valid values. A value is valid if it is applicable to the column to which the filter is applied. For instance, passing a filter value of checked for the transaction_type column will return an error since checked is not a valid payment status. Depending on the filter you specify, the value must also be formatted correctly (a currency, for example, must always be a three character code in ISO-4217 format).

• While you can apply a filter to data in any column, only the following columns have been optimized for querying: app_id, currency, country_by_ip_address, payment_method, provider_name, transaction_result_status and transaction_type. The report may take longer to generate if you apply data filters to other columns.

Report Column and Filter Values

Some columns and filters are self-explanatory and do not require much elaboration to be understood. Others, however, require that you follow specific guidelines to ensure you pass in valid filter values. For an explanation of the filter values you can pass in a transaction report, see Transaction Report API Filter Values. For an explanation of the columns you can include in a settlement report, see Generating the Settlement Report.