This topic describes how to download reporting data as Comma Separated Values (CSV) by submitting a HTTPS POST query string. By implementing the CSV download functionality outlined here, your system can be configured to process requests on your behalf, allowing you to automate the process of downloading the records required.
CSV files of transaction data can also be downloaded by signing in to Portal and using the Transaction Search page.
The maximum number of records that can be downloaded is 75,000.
If you have greater than 75,000 records, we recommend requesting smaller timeframes and performing multiple sequential requests
Process overview
Reports allow you to keep track of the number of requests processed on your account during a specified period of time. This information is grouped by criteria specified in the POST.
Example use cases:
- Download the number of transactions authorised.
- Download the number of transactions suspended.
- Download the total funds settled.
- Download the number of transactions processed with PayPal.
The following is an example of a POST for requesting a report download CSV report:
wget --output-document=output.csv --user=example@example.com --ask-password --post-data="sitereferences=test_site12345&startdate=2017-01-01&enddate=2017-01-31&settlestatuss=100&sqlgroupbys=currencyiso3a&sqlgroupbys=paymenttypedescription" https://myst.securetrading.net/auto/transactions/reporting
The request above consists of four main parts:
You will need to provide a Trust Payments API username with sufficient user privileges to perform the download for the given site reference(s).
--user=example@example.com --ask-password
The CSV file can be downloaded using a HTTPS POST query string with Basic Access Authentication. Basic Access Authentication is a method for a web browser client program to provide a username and password when processing a request.
Supported user roles
You can use your Webservices username and password to download transaction records and reports. If you do not already have a Webservices username, you can create a new API user using Portal.
We also support two additional user roles for CSV downloads. These are “Transaction download” and “Report download”, for downloading transaction records and reports respectively. To set up users with these roles, please contact our Support Team.
Authorisation header
A username and password, separated by a colon, then base64 encoded must be included in the authorisation header when performing the request. Most URL libraries will construct the basic authentication header automatically, but if you need to perform this manually, please follow this example:
Username: csvdownload@example.com
Password: pa55word
Separated by a colon: csvdownload@example.com:pa55word
Base64 encode: Y3N2ZG93bmxvYWRAZXhhbXBsZS5jb206cGE1NXdvcmQ=
Therefore, the authorisation header should be:
Authorization: Basic Y3N2ZG93bmxvYWRAZXhhbXBsZS5jb206cGE1NXdvcmQ=
Filters can be included in the request so that only records meeting certain criteria are returned.
sitereferences=test_site12345&startdate=2017-01-01&enddate=2017-01-31&settlestatuss=100
By default, all transactions within the date range for the specified site reference(s) will be returned in the download. Filters can be applied to the download so only certain records are returned. In the example above, the following filters have been applied:
- Site reference: test_site12345
- Transaction authorisation date: 1st January 2017 – 31st January 2017
- Settle status: 100
Multiple values for the same filters can be included in the call separated by an “&”.
For example, for downloading e-commerce and MOTO transactions, the accounttypedescriptions filter can be included twice with two different values:
accounttypedescriptions=ECOM&accounttypedescriptions=MOTO
Required filters
The following fields must be included in the POST data:
| Filter name | Comment |
| sitereferences | The site reference/s to download data from. |
| startdate | The start date for the specified records (in the format YYYY-MM-DD). |
| enddate | The end date for the specified records (in the format YYYY-MM-DD). |
Data can be downloaded for multiple site references by including additional site references fields in the call separated by an “&”.
For example, if transactions are needed for site1 and site2, the call would include the following:
sitereferences=site1&sitereferences=site2
Please note that even if downloading transactions for one site reference, the field must always be called sitereferences.
Optional filters
Useful filters
The following is a list of the most frequently-used filters by our merchants:
(Contact our Support Team to enquire about availability of additional filters)
| Filter name | Comment |
| accounttypedescriptions |
Filter by account type, e.g. “ECOM” for e-commerce. You can view the account types enabled on your account in Portal by navigating to your site details page and clicking “Accounts”. |
| currencyiso3as |
Filter by currency. |
| errorcodes |
Filter by error codes. |
| paymenttypedescriptions | Filter by payment type. e.g. “VISA”, “MASTERCARD” etc. |
| requesttypedescriptions |
Type of request, for example “AUTH”, “REFUND” etc. |
| settlestatuss |
Filter by status. e.g. “100” for all settled transactions. |
Note that to search by settlestatus, the field must be called settlestatuss.
Filter by time
Use the following filters to download data on transactions within a specified range of time:
| Filter name | Comment |
| starttimehour | To find transactions between HH:mm:00 and HH:mm:59 *Required if end time specified |
| starttimeminute | To find transactions between HH:mm:00 and HH:mm:59 * Required if starttimehour specified |
| endtimehour | To find transactions between HH:mm:00 and HH:mm:59 * Required if start time specified |
| endtimeminute | To find transactions between HH:mm:00 and HH:mm:59 * Required if endtimehour specified |
For example, for downloading transactions between 13:30:00 and 14:29:59:
starttimehour=13&starttimeminute=30&endtimehour=14&endtimeminute=29
Date type
By default, dates and times specified are filtered based on when the transaction was initially processed. However, you can explicitly specify the type of date/time that is filtered by including the following additional filter:
| Filter name | Comment |
| datetypes | Specify “settledtimestamp” to filter by date/time transactions were settled.
Specify “transactionstartedtimestamp” to filter by date/time requests were processed. |
If your system is processing a high volume of transactions, we recommend downloading CSV files of transactions on an hourly basis (instead of daily), for optimum performance and to reduce the size of the files downloaded. Your system can submit a request every hour, specifying the time filters as described above, to ensure only transactions processed in the last hour are included.
This is where you can specify the data fields to be returned.
As a minimum, all report download CSVs will contain the acquirer name and number of transactions settled for each of these acquirers within the given timeframe.
Example output:
acquirertypedescription,settledcount
BARCLAYS,1916
PAYPAL,249
PAYSAFE,18
TEST,4
Grouping
The output returned in the report download can be grouped by the following options:
- day
- sitereference
- currencyiso3a
- requesttypedescription
- accounttypedescription
- paymenttypedescription
- errorcode
- settlestatus
- livestatus
- transactionactive
- enrolled
- fraudcontrolshieldstatuscode
- orderreference
You can apply preferred grouping to the report download by submitting the optional sqlgroupbys field in the request.
The following example request would return output grouped by currency first and then payment type:
wget --output-document=output.csv --user=example@example.com --ask-password --post-data="sitereferences=test_site12345&startdate=2017-01-01&enddate=2017-01-31&settlestatuss=100&sqlgroupbys=currencyiso3a&sqlgroupbys=paymenttypedescription" https://myst.securetrading.net/auto/transactions/reporting
The order in which the sqlgroupbys are submitted affects the output. The output is always grouped in the order submitted in the request.
For instance, you could instead choose to group the output by payment type first and then currency, by swapping the two fields around in the example above.
When submitting the example request above, the output would be grouped as shown below. Because the data was requested to be grouped by both currency and payment type, the result is that there is a row in the output for each combination of currency and payment type.
acquirertypedescription,currencyiso3a,paymenttypedescription,settledcount,sumsettlemainamount
TEST,GBP,VISA,1,10.99
TEST,GBP,MASTERCARD,3,33.96
TEST,EUR,VISA,17,214.10
TEST,EUR,MASTERCARD,7,72.80
Download the CSV from the following URL:
https://myst.securetrading.net/auto/transactions/reporting
Use of this URL is unaffected by the migration from MyST to Portal
Please continue to process requests using the MyST URL shown above.
Output
Providing your request is successful, the CSV output has the following structure:
acquirertypedescription,currencyiso3a,paymenttypedescription,settledcount,sumsettlemainamount
TEST,GBP,VISA,1,10.99
TEST,GBP,MASTERCARD,3,33.96
TEST,EUR,VISA,17,214.10
TEST,EUR,MASTERCARD,7,72.80
