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:

Prerequisites

  • Your merchant profile on the CommWeb payment 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.

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.

Reporting Transaction Data

Download Reports Using a Reporting API Integration

Create Reporting Request

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.
Times

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.

Columns

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 DirectAPI that you support. Note that the Reporting API version is distinct from the DirectAPI version. The fields available for selection using the latest version of DirectAPI can be found here.

The Reporting API only supports fields available in DirectAPI version 14 and later. You cannot retrieve, download, or store any data from array fields; for example, 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.

Column Headers

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.

Send Reporting Request

Once you have determined the fields you want to include in your request, you can forward it to the CommWeb payment gateway. The request is submitted to the payment gateway by HTTPS GET request. the CommWeb payment 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).

HTTP Headers

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".

Authenticating the Requests

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 DirectAPI 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 paymentgateway.commbank.com.au 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.

Download sample scripts

Timing Your Requests

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 CommWeb payment 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 CommWeb payment gateway rejects the request with a DATA_AVAILABLE_AFTER response and the latest end time allowed.

Sample Scripts

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.

Download sample scripts

Process Reporting Response
Success

In response to a valid, authenticated GET request, Reporting provides a CSV file containing all DirectAPI 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.

Reporting generates a transaction record when the transaction state changes. For example, if an Authorization transaction is assessed for risk and then settled, the transaction report will show records for all the three events — Authorization, Risk Response, Settlement Response.

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
Failure

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.

Configuring the URL for Reporting

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".

For more information on the reporting options available refer to the Retrieve Transaction API Reference.

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 DirectAPI password). See Generating Password for Reporting. For the username, enter merchant.default.

Generate Password for Reporting

Reporting requires a password-authenticated request to the CommWeb payment gateway. You can generate the password in Merchant Administration.

The operator must have "May Configure Reporting API Integration Settings" privilege to generate the password.
  1. Navigate to Admin > Reporting API Integration Settings > Edit.
  2. Click Generate New.
  3. Select the Enable Reporting API Integration Access Via Password box.
  4. Copy the password to the clipboard and/or a text file. You will need it later.
  5. 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.

Copyright © 2020 Commonwealth Bank of Australia