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.

API Reference

Click here to view the Reporting API Reference.

Implementation Details

The sections that follow provide some more information about using the reporting API.

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.

Settlement Reports Timezone

When generating a settlement report, you have the option of specifying a timezone. PaymentsOS will use this timezone to convert the timestamps in the report to the timezone you select. Beware that the timezone you specify does not affect the data range: it only changes the date displayed in the report to reflect the specified timezone. This applies both to the timezone you specify in the Reporting API (where the field you pass is appropriately called display_timezone) and the timezone you select when generating a report through the PaymentsOS Control Center.

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.

Report Generation and Delivery Time

The following table lists the report generation and delivery times for transaction and settlement reports.

Report Type Generation and Delivery Times
Transaction Reports When scheduling a report, it will always be generated at 01:00 AM in the timezone you specified.
Settlement Reports Currently, you can only generate a settlement report for transactions processed through PayU India. For PayU India, the settlement data will always be available at 22:30 UTC for settlement data of the current day. When scheduling a report, it will always be generated at 01:00 AM in the timezone you specified.

Scheduled Reports File Names

Scheduled reports will have the following file names when generated:

  • Archive name: {year-month-day}_sch_{daily/weekly/monthly}_{report template name}

  • File name: sch_{daily/weekly/monthly}_{report template name}

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 Columns 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 Settlement Report Columns.

Transaction Report API Filter Values

Some filters values require that you follow specific guidelines to ensure you pass in valid filter values. We listed those guidelines in the table below.

Columns Guidelines
currency Must be a three character code in ISO-4217 format
  • transaction_amount
  • installments_first_payment_amount
  • installments_remaining_payments_amount
  • payment_amount
  • transaction_cost
Must be formatted in minor units.
card_country_code Must be formatted in in ISO 3166-1 alpha-3 format.
  • card_type
  • card_issuer
  • card_level
Must be one of the values returned in the relevant field of the Create Token request.
transaction_result_status Valid values: pending, succeed, failed, cancelled.
  • transaction_result_category
  • transaction_result_sub_category
For an overview of valid values, see Response Category and Response Sub-category in the Payments API Reference.
transaction_type Valid values: created, authorization, charge, capture, void, refund, dispute Note: a transaction has a status of "created" when you invoke the Create Payment API request.
transaction_three_d_secure_result_type Valid values: internal or external.
transaction_three_d_secure_auth_status Valid values:
  • A: Attempts Processing Performed; Not Authenticated/Verified, but a proof of attempted authentication/verification is provided
  • Y: Authentication/ Account Verification Successful
  • N: Not Authenticated /Account Not Verified; Transaction denied
  • R: Authentication/ Account Verification Rejected; Issuer is rejecting authentication/verification and requests that authorisation not be attempted.
  • R: Authentication/ Account Verification Could Not Be Performed; Technical or other problem
transaction_three_d_secure_result_version Valid values: 1.x.x or 2.x.x. Perform a payment search to see the version values returned in the transaction requests passing through your environment.
transaction_three_d_secure_result_eci Valid values:
  • 00: Returned by MasterCard. Authentication is unsuccessful or not attempted. The credit card is either a non-3D card or card issuing bank does not handle it as a 3D transaction.
  • 01: Returned by MasterCard. Either cardholder or card issuing bank is not 3D enrolled. 3D card authentication is unsuccessful.
  • 02: Returned by MasterCard. Both cardholder and card issuing bank are 3D enabled. 3D card authentication is successful.
  • 05: Returned by Visa ,JCB and Amex. Both cardholder and card issuing bank are 3D enabled. 3D card authentication is successful.
  • 06: Returned by Visa, JCB and Amex. Either cardholder or card issuing bank is not 3D enrolled. 3D card authentication is unsuccessful.
  • 07: Returned by Visa, JCB and Amex. Authentication is unsuccessful or not attempted. The credit card is either a non-3D card or card issuing bank does not handle it as a 3D transaction.
cof_transaction_indicators_card_entry_mode For an overview of valid values, see the cof_transaction_indicators.card_entry_mode field in the request body of a Create Authorization or Create Charge request.
user_email A support agent can initiate a transaction request on behalf of a customer (using the Virtual Terminal or through the Payment Search functionality). The user_email is the email address of the agent who initiated the transaction request.

Settlement Report Columns

Regardless of how you choose to generate the settlement report (through the PaymentsOS Control Center or using the Reporting API), you will have to let us know what data you want to include in the report. You do so, by providing the names of the columns holding the data to be exported. There’s a lot to choose from and not all column names are self-explanatory, so we listed the columns for you in the table below.

Column Name Data Type Description Can Use in Filter Supported Providers
Additional Income Tax decimal Additional income tax withheld on the payment. Applicable to Chile. No PayU Latam
Authorization Code Text The authorization code issued by the acquirer. No PayU Latam
Available Balance decimal The total amount that you can transfer to your bank account. Calculated by deducting the debit movements from the credit movements. No PayU Latam
Business Unit text The identifier of the business unit handling the transaction. Will be empty if the transaction was handled directly by the provider (that is, not through PaymentsOS). No PayU India, PayU Latam
Card Issuer text A code identifying the card issuer. Will be empty for non-card payments and for transactions that were handled directly by the provider (that is, not through PaymentsOS). No PayU India, PayU Latam
Commission Fee (-) decimal A fee charged for facilitating transactions through your account. Charged as a percentage of the transaction amount.. No PayU Africa providers
Currency text The currency in which the payment was made. This is a three character currency code in ISO-4217 format. For example, INR. Yes PayU India, PayU Latam, PayU Africa providers
Fee (-) decimal Transaction fees charged by the provider. Always shown with two decimal places. A period (.) is used as the decimal separator. Note that multiple fees can be applied to one transaction, such as fixed fees and percentage fees. No PayU India, PayU Latam, PayU Africa providers
Fee Tax (-) decimal Tax (such as VAT) applied to the fee. Always shown with two decimal places. A period (.) is used as the decimal separator. No PayU India, PayU Latam, PayU Africa providers
Gross Amount (+/-) decimal The payment amount. This is either an addition or a subtraction, depending on the operation type. The amount is always shown with two decimal places. A period (.) is used as the decimal separator. For example, 1130.74. No PayU India, PayU Latam, PayU Africa providers
ICA Withholding text ICA tax withheld on the payment. Applicable to Colombia. No PayU Latam
IIBB Merchant Commission text IIBB merchant commission. Applicable to Argentina. No PayU Latam
IIBB Payer Commission text IIBB payer commission. Applicable to Argentina. No PayU Latam
Income Withholding text Withholding tax applied to the payment. Applicable to Colombia. No PayU Latam
Installments decimal The number of installments used in the purchase. No PayU Latam
Interest decimal The interest rate applied to the amount used to finance the purchase. No PayU Latam
Interest Tax decimal Tax withheld from the interest charged for financing the purchase. No PayU Latam
IVA Withholding text IVA tax withheld on the payment. Applicable to Colombia. No PayU Latam
Local/International Transaction text Whether the transaction is considered local or international. A local transaction refers to a transaction made with a credit card issued in the same country, while an international transaction involves processing the payment in a different country from where the card was issued. No PayU Africa providers
Merchant Id text The identifier of your account in PaymentsOS. No PayU India, PayU Latam
Merchant Name text The name of your account in PaymentsOS. No PayU India, PayU Latam
Months Without Interest Fee decimal Fee charged for Months Without Interest usage. Applicable to Argentina and Mexico. No PayU Latam
Months Without Interest Tax decimal Tax withheld from the charged Months Without Interest fee. Applicable to Argentina and Mexico. No PayU Latam
Net Amount decimal The amount transferred to your bank account after deduction of fees and imposed taxes. The amount is always shown with two decimal places. A period (.) is used as the decimal separator. For example, 987.72. No PayU India, PayU Latam, PayU Africa providers
Operation Date timestamp The date when the operation (an event such as a refund, chargeback or capture of funds) was registered in the system. For example, 2020-03-03 00:16:15.000. Yes PayU India, PayU Latam, PayU Africa providers
Operation Type text The type of operation registered in the system. Can be one of the following:
  • CAPTURE (+): Funds were transferred to the acquiring bank
  • PAYOUT (-): Funds were transferred to your bank account
  • REFUND (-): Funds were voluntarily returned to the shopper
  • CHARGEBACK (-): The customer disputed the charge, after which the transaction amount was deducted from your balance.
  • CHARGEBACKREVERSAL (+): Funds were credited to your account following a reversal of the chargeback. Applicable to PayU India only.
  • FREEZE (-): Funds were blocked pending a refund or a lost dispute. Applicable to PayU Latam only.
  • UNFREEZE (+): Funds were returned to the card holder in the event of a refund or a lost dispute. Applicable to PayU Latam only.
  • FREEZE (-): Funds were blocked pending a refund or a lost dispute. Applicable to PayU Latam only.
  • PAYOUT REFUND (+): Funds were returned to your balance after the bank transfer was rejected (for example, due to incorrect bank information). Applicable to PayU Latam only.
  • PAYU TRANSFER (-): Funds were transferred between PayU's virtual accounts. Applicable to PayU Latam only.
  • POSITIVE ADJUSTMENT (+): The PayU virtual account was credited in order to handle any discretional movement related to the settlement of your funds. Applicable to PayU Latam only.
  • NEGATIVE ADJUSTMENT (-): The PayU virtual account was debited in order to handle any discretional movement related to the settlement of your funds. Applicable to PayU Latam only.
  • RR_CREDIT (+): Funds released from the rolling reserve. Applicable to PayU Africa providers only.
  • RR_DEDUCT (-): Funds added to the rolling reserve. Applicable to PayU Africa providers only.
  • SUPPLIER MONEY TRANSFER (-): Funds were transferred to the bank account of a third party. Applicable to PayU Latam only.
  • SUPPLIER MONEY TRANSFER REFUND (+): Balance was credited after the transfer to the bank account of a third party was rejected. Applicable to PayU Latam only.
  • RELEASE RESERVE (+): Amount previously reserved as collateral has been released. Applicable to PayU Latam only.
  • PAYMENT CARDS: A coupon/reference was used by the payer to make a cash payment at a payment point (only in Argentina and Colombia). Applicable to PayU Latam only.
Yes PayU India, PayU Latam, PayU Africa providers
Order Id text Identifier of the order. If you configured webhooks, then the order Id is returned in the webhook notification as well. Yes PayU India, PayU Latam, PayU South Africa, PayU Kenya, Magellan, PayU Nigeria
Payment Id text The identifier of the initial payment request processed by PaymentsOS. Will be empty if the transaction was handled directly by the provider (that is, not through PaymentsOS). No PayU India, PayU Latam
Payment Method text The payment method used by the shopper. For example, Visa or MasterCard. Will be empty if the transaction was handled directly by the provider (that is, not through PaymentsOS). No PayU India, PayU Latam
Payment Method Type text The type of payment method used by the shopper. For example, credit card or bank transfer. Will be empty if the transaction was handled directly by the provider (that is, not through PaymentsOS). No PayU India, PayU Latam
Promotion Id Text The ID of a promotion that was used in a transaction. No PayU Latam
Provider Acquirer text The acquirer that handled the payments processed by the provider. No PayU India
Provider Configuration Id text The identifier of the provider configuration in your PaymentsOS account. Yes PayU India, PayU Latam
Provider Configuration Name text The name of the provider configuration in your PaymentsOS account. Yes PayU India, PayU Latam
Provider External Id text The identifier of the provider that processed the transaction, as registered in PaymentsOS. Will be empty if the transaction was handled directly by the provider (that is, not through PaymentsOS). No PayU India, PayU Latam
Provider Name text The name of the provider that processed the transaction. Yes PayU India, PayU Latam
Provider Payment Id text The identifier of the initial payment request as registered with the provider that handled the transaction. No PayU India, PayU Latam
Provider Reconciliation Id text A unique identifier that you provided in the transaction request to be used for reconciliation purposes, as registered with the provider that handled the transaction. If the transaction was routed via PaymentsOS, then this Id will be the same as the value of Reconciliation Id. No PayU India, PayU Latam
Provider Transaction Id text The identifier of the transaction as registered with the provider that handled the transaction. No PayU India, PayU Latam
Reconciliation Id text A unique identifier that you provided in the transaction request, used for reconciliation purposes. This identifier is the same as the value of Provider Reconciliation Id. Will be empty if the transaction was handled directly by the provider (that is, not through PaymentsOS). No PayU India, PayU Latam
Retentions decimal Amount held back from the payout, as applicable by the law of each specific country. No PayU Latam
Sales Date timestamp The date when the transaction was completed and is available for payout. No PayU India, PayU Latam
Settlement Admin Fee (-) decimal A fee charged for managing and facilitating the transfer of funds to your bank account. No PayU Africa providers
Settlement Batch Reference text Funds of multiple transactions are grouped and transferred to your bank account in batches. The Settlement Batch Reference is a reference number identifying the batch to which the transfer of funds belongs. Yes PayU India, PayU Latam, PayU Africa providers
Settlement Date timestamp The date on which the funds were transferred to your bank account. For example, 2020-01-03 00:15:14.000. Yes PayU India, PayU Latam, PayU Africa providers
Stamp Duty Fee (-) decimal Tax that is levied on transactions by the local government. No PayU Africa providers
Supplier Money Transfer Refund (+) decimal Funds credited after the transfer to the bank account of a third party was rejected. No PayU Latam
Timezone text The timezone for which the report was generated. No PayU India, PayU Latam, PayU Africa providers
Total Balance decimal The total amount on your balance before deducting all debit movements. No PayU Latam
Transaction Fixed Fee (-) decimal A flat fee charged for a specific financial transaction or service. This fee remains constant regardless of the transaction’s size, value, or other variables. No PayU Africa providers
Transaction Id text The identifier of the transaction processed by PaymentsOS. Will be empty if the transaction was handled directly by the provider (that is, not through PaymentsOS). No PayU India, PayU Latam
Transaction Percentage Fee (-) decimal A fee that is calculated as a percentage of the total transaction amount. No PayU Africa providers
VAT Fee (-) decimal Value Added Tax that is added to the cost of goods or services being sold. No PayU Africa providers
Last modified June 29, 2022