Reporting
Reporting enables you to download transaction reports via the Reporting API.
- Reports show details of transactions created or updated within a specified time period. You can specify the fields to populate the report and the column headers in the output.
- You can download and store your transaction data in CSV format for analysis. For example, the data can be analyzed by a business intelligence or a spreadsheet application.
You can get order and transaction reports in three different ways:
- Using a Reporting API integration to download reports
- Using a web browser to download reports
- Using portals to download reports
You can download transactions contained in the search result list in the Merchant Administration portal in Comma Separated Values (CSV) format.
Prerequisites
- Your merchant profile on the Mastercard Gateway must be enabled for the Reporting service.
- You must generate a password to access the Reporting API. See Generating Password for Reporting.
Transaction data in a report
Reporting records all events associated with a transaction. When you request a transaction report, all events associated with a given transaction within the period you specify are inserted in the report. This includes any update events associated with the transaction. You also have the flexibility to filter data as per your business requirements.
- Payer authentication
- Authorization/Pay
The image below illustrates the reporting mechanism. A transaction report requested for the period '1-Feb-2012' to '29-Feb-2012' returns Event 1 and Event 2. Both of these are associated with the same transaction. Event 3, though also associated with the transaction, took place after the end of the specified period, and so will not be included in the report.
Download reports using a Reporting API integration
Reporting requires a http GET request, such as the one shown below.
GET /history/version/1/merchant/<merchant>/transaction?timeOfRecord.start=<starttime>&timeOfRecord.end=<endtime>&columns=<columns>&columnHeaders=<columnheaders>
Where:
<merchant>
is your Merchant ID.<starttime>
and <endtime> indicate the time period to cover.<columns>
are the fields to include in the report, separated by commas. The names are case sensitive.<columnheaders>
is the list of column headers to include in the report, separated by commas.
The start time is inclusive (transaction time >= start time) and the end time is exclusive (transaction time < end time).
The start and end times are passed as parameters in ISO 8601 format: yyyy-mm-ddThh:mm:ssZ
, where Z
indicates that the time is UTC.
If the time is not UTC, a timezone offset must be specified as +/-hh[:mm], for example, 12:31+10
(UTC + 10 hours), or 12:31:30-06:30
(UTC minus 6 hours 30 minutes).
Time values that appear in the report (if any) are in UTC.
The first instant of a day occurs at time 00:00. The end time of the report (timeOfRecord.end) is exclusive, so to report up to the end of a day, use the first instant of the next day (00:00) as the end time. Similarly, if you want hourly data, specify a window as the start of one hour and the start of the next.
Time zone and time format
You can specify two optional parameters in the request:
csv.timeZone=<+ or ->HH:MM
If specified, the time of records is output in the indicated time zone and without a time zone indicator. Note that the + and – sign must be UTF-8 encoded. For example, if you set
csv.timeZone=%2B10:00
(i.e.,+10.00
), times of events appear in the report as UTC+10 hours.If omitted, the output is UTC with a 'Z' appended to indicate UTC time zone.
csv.timeFormat=<iso8601 or iso8601-T>
If specified as
iso8601-T
, the output uses a space instead of a T as the date-time separator.If omitted, or specified as
iso8601
, the date-time separator is 'T'.
Daylight savings
We recommend that you ignore daylight saving for report downloads. However, if you want report downloads to be windowed by daylight saving times, you need to manage that by:
- modifying the time zone that you specify
- accounting for the fact that you will lose one hour of records at the start of daylight saving, and will get one hour of duplicate records at the end of daylight saving.
To avoid daylight savings complications, use the UTC timezone to specify your time window.
The columns parameter specifies the fields that appear in the report. The fields that can be specified are described in the Retrieve Transaction operation for the version of API that you support. Note that the Reporting API version is distinct from the API version. The fields available for selection using the latest version of API can be found here.
order.item[n].detail
.If you process transactions using multiple API versions, you can specify fields that are either common across different versions or unique to a particular version. Data that does not exist for a particular record will show as an empty value in the CSV. However, any field name must be valid for at least one API version.
If you supply your own column headers in the columnHeaders parameter, the list of names corresponds to the list of fields in the 'columns' list. Both &columnHeaders and &columns must have the same number of members.
If there are no headers included in the request (&columnHeaders is omitted), the column headers will be the field names. If an empty header parameter is passed (&columnHeaders=) the output file will not contain any header rows.
Once you have determined the fields you want to include in your request, you can forward it to the Mastercard Gateway. The request is submitted to the payment gateway by HTTPS GET request. the Mastercard Gateway assumes UTF-8 encoding for the URL request, and supports all useful Unicode characters. You must respect the URL encoding rules (see RFC 3986).
By default, the download content type is CSV with ISO8859-1 encoding. You can specify any of the following using the appropriate mime type in the Content-Type header:
- ASCII
- UTF-8
- Big5
- GB18030
- Shift-JIS
- KOI8-R
- EUC-JP
- EUC-KR
- ISO8859-1
For example, to download in UTF-8, specify "Accept-Charset : UTF-8".
The merchant ID is in the URL. Password authentication occurs through basic HTTP authentication. The password is the merchant profile's Reporting password configured in Merchant Administration (note: this is not the same as the API password). See Generating Password for Reporting.
The basic authentication username is 'merchant.default'.
Bash Script Example
The Bash script examples use curl to download reports (see http://curl.haxx.se/). Curl expects the password to be set in the .netrc file. This is to be created in the user's home directory. You need to add the following line to the .netrc file; <password> is to be substituted with your password:
machine test-nbkpayment.mtf.gateway.mastercard.com login merchant.default password <password>
Windows PowerShell Example
The api password is stored encrypted in the reportingPassword file in your home folder ($HOME). To generate the reportingPassword file run the setpassword.ps1 script included in the download bundle.
For more information on how to set credentials, look here.
If you do not submit a password with your request or download script, you will be prompted (if your https client/browser supports it) to supply a password when the call is made.
To automate downloads, your system might issue a series of download calls using a scripting language triggered by a timer process (for example, cron on Linux or Scheduled Tasks on Windows). Managed File Transfer products can also do this.
You can choose how often you download. Ten minute intervals will keep your data close to real time, but daily intervals might be more efficient. Downloads should be often enough to prevent the files getting too big or too far behind – both problems raise the significance of a failed download and the urgency of fixing it.
Ideally, you set the start time (timeOfRecord.start) of each call to be the end time (timeOfRecord.end) of the previous successful call. This guarantees that you get all records exactly once. Use the HTTP status code to check if the last call failed. On failure, the script should wait a few minutes and try again. the Mastercard Gateway has a few minutes delay from the time it processes a transaction to the time it is available for download. If you attempt to download records from that period, the Mastercard Gateway rejects the request with a DATA_AVAILABLE_AFTER response and the latest end time allowed.
The sample scripts bundle comprises the following scripts:
- basicreportexample.sh / basicreportexample.ps1 - A simple script configured by command line parameters; it downloads a report between a specified time range.
- downloadreport.sh / downloadreport.ps1 - A script designed to run unattended from a scheduler; it downloads any new transaction events since the last time it was run.
- setpassword.ps1 - A Windows PowerShell script that prompts for a password and encrypts it in the reportingPassword file. It uses an encryption feature only available to Windows.
With the Bash script example, the script assumes that you have configured your Reporting password in the .netrc file.
In response to a valid, authenticated GET request, Reporting provides a CSV file containing all API transaction records, filtered by all the fields you specified in the request, that have been created or updated within the time frame defined by the start and end date. The records are ordered ascending from oldest transaction to most recent.
Card numbers are masked in the report.
An example transaction report is below:
time,order id,transaction id,card number,card expiry month,card expiry year,amount,currency,type,acquirer,acquirerCode 2012-05-14T06:23:10.859Z,11336976611,1,345678xxxxx4564,5,13,60.00,USD,AUTHORIZATION,AMEXGWS,000 2012-05-16T01:25:19.694Z,11337131511,1,345678xxxxx4564,5,13,36.00,USD,AUTHORIZATION,AMEXGWS,000 2012-05-16T06:33:31.709Z,11337150032,1,345678xxxxx4564,5,13,36.00,USD,AUTHORIZATION,AMEXGWS,000 2012-05-16T22:15:53.276Z,11337206569,1,345678xxxxx4564,5,13,75.00,USD,AUTHORIZATION,AMEXGWS,000 2012-05-17T00:22:14.516Z,11337214154,1,345678xxxxx4564,5,13,72.00,USD,AUTHORIZATION,AMEXGWS,000 2012-05-17T01:41:17.447Z,11337218888,1,345678xxxxx4564,5,13,60.00,USD,AUTHORIZATION,AMEXGWS,000 2012-05-17T01:41:43.533Z,11337218921,1,345678xxxxx4564,5,13,78.00,USD,AUTHORIZATION,AMEXGWS,000 2012-05-21T11:15:00.093Z,11337598863,1,345678xxxxx4564,5,13,39.00,USD,AUTHORIZATION,AMEXGWS,000 2012-05-21T12:29:52.954Z,11337603409,1,345678xxxxx4564,5,13,39.00,USD,AUTHORIZATION,AMEXGWS,000 2012-05-22T01:51:09.996Z,11337651486,1,345678xxxxx4564,5,13,36.00,USD,AUTHORIZATION,AMEXGWS,000
If the request is invalid, or if the merchant ID and password you submitted are invalid or not authorised to access the reporting API, an error message with details is returned.
If you submit two requests, and the End Date/Time of the first request is the Start Date/Time of the second request, no transaction records will be missed or duplicated. If there are no records up to the End Date/Time, an error message is returned, indicating the last possible end date/time.
If no records exist for the report period you specified, a CSV file with the header and no entry lines will be returned.
Download reports using a web browser
You can directly request reports using a web browser. The reports can be configured in the URL provided in the request.
Create a URL from the template below:
https://YOUR_GATEWAY_SERVER/history/version/1/merchant/
YOUR_MERCHANT_ID/transaction?REPORT_CONTENTS_AND_FORMAT
REPORT_CONTENTS_AND_FORMAT
example:columns=merchant,order.id,transaction.id, transaction.amount,transaction.amount& columnHeaders=Merchant,OrderID,TransactionID,Currency,Amount
&timeOfRecord.end=2015-01-24T18:43:54
&timeOfRecord.start=2015-01-12T18:38:40
This retrieves a report containing merchant, order ID, transaction ID, transaction currency and the transaction amount for transactions between 15 Jan 2015 18:43:54h and 24 Jan 2015 18:38:40h. The column headers will show "Merchant", "OrderID", "TransactionID", "Currency" and "Amount".
Paste the URL into your browser. You will be prompted to insert the password. The password is the merchant profile's Reporting password configured in Merchant Administration (note: this is not the same as the API password). See Generating Password for Reporting. For the username, enter merchant.default.
Generate password for Reporting
Reporting requires a password-authenticated request to the Mastercard Gateway. You can generate the password in Merchant Administration.
- Navigate to Admin > Reporting API Integration Settings > Edit.
- Click Generate New.
- Select the Enable Reporting API Integration Access Via Password box.
- Copy the password to the clipboard and/or a text file. You will need it later.
- Click Submit.
The system-generated password is a 16 byte, randomly generated value that is encoded as a hex string. Though it is of sufficient length and quality to resist brute force guessing, it should be secured in the same manner as user passwords and other sensitive data.
You must always have at least one password generated and enabled but you may have up to two passwords set up. For security, you should change the password periodically. To do this, generate a new password and update the report download script. Both passwords will work during this changeover.