Skip to main content

Report configuration

The Reporting API allows users to schedule reports to meet their specific needs. Options include daily, weekly, monthly or adhoc.

Scheduling reports

The Reporting API uses the standard UTC format for all times and timestamps. As such, if you want the report to run at 09:00am EST, you must schedule the report for 13:00 UTC.

You can also update the frequency on an existing report configuration at any time. The following are valid values for scheduleFrequency:

  • daily
  • weekly (e.g., running a report every Tuesday would be weekly|tue)
  • monthly (e.g., running a report on the 3rd day following the end of the month would be monthly|3)
  • adhoc

In addition to scheduleFrequency, you can also use:

  • reportFileScheduleTime — specifies when report generation should begin, not when the report has completed.
  • reportingPeriodStartTime — specifies the starting point for data collection.

To schedule a daily, weekly, or monthly report, you must consider your end of the day time as standard UTC format to configure the reportingPeriodStartTime field. The reportingPeriodStartTime should be equal to merchant’s end of the day time in UTC timezone.

For example, if the your end of the day is 10 PM EST, the reportingPeriodStartTime is 03:00:00.

Tip

You can configure a maximum of 20 unique report configurations per entity type via the Reporting API. 

Daily schedule

Schedule daily reports by setting scheduleFrequency to daily. You can control when the report runs and from when the data collection should begin using the fields reportFileScheduledTime and reportingPeriodStartTime respectively.

  • If the reportFileScheduledTime is greater than or equal to [reportingPeriodStartTime + 4 hours], then the report will contain data from T-1 (the previous day) through T (the current day).
  • If the reportFileScheduledTime is less than [reportingPeriodStartTime + 4 hours], the report will contain data from T-2 (2 days back) through T-1 (previous day).  

The following frequencyConfig example shows a scenario where the reportFileScheduledTime is greater than [reportingPeriodStartTime + 4 hours]. The report runs every day at 09:00 UTC (T) and includes 24 hours of data from yesterday at 01:00 UTC (T-1) until today at 00:59:59 (T).

Json
{  
     "reportConfigurationId":"58bc2d32-28db-43b1-9bc6-1072a72f4589",
     "frequencyConfig": { 
            "scheduleFrequency": "daily", 
            "reportFileScheduledTime": "09:00:00", 
            "reportingPeriodStartTime": "01:00:00" 
       }
}

The following frequencyConfig example shows a scenario where the reportFileScheduledTime is less than [reportingPeriodStartTime + 4 hours]. The report runs every day at 09:00 UTC (T) and includes 24 hours of data from the day before yesterday at 21:00 UTC (T-2) until yesterday at 20:59:59 (T-1). The ability to define custom time intervals this way accommodates for varying end-of-day funding cycles across global operations.

Json
{  
     "reportConfigurationId":"58bc2d32-28db-43b1-9bc6-1072a72f4589",
     "frequencyConfig": { 
          "scheduleFrequency": "daily", 
          "reportFileScheduledTime": "09:00:00", 
          "reportingPeriodStartTime": "21:00:00" 
      }
}

Weekly schedule

The following frequencyConfig example describes a report that runs every Tuesday at 9:00 UTC and includes 7 days of data timestamped between a week ago Monday (T-8) at 21:00:00 UTC and Monday (T-1) at 20:59:59 UTC:

Json
{  
     "reportConfigurationId":"58bc2d32-28db-43b1-9bc6-1072a72f4589",
     "frequencyConfig": {   
          "scheduleFrequency": "weekly|tue",  
          "reportFileScheduledTime": "09:00:00",  
          "reportingPeriodStartTime": "21:00:00"  
      }
}
Tip

For "WEEKLY", the report will run for the last 7 days (current day not included). If the reportFileScheduledTime is greater than or equal to [reportingPeriodStartTime + 4 hours], then the report will contain data from T-7 (days) through T (current day).

The following frequencyConfig example describes a report that runs every Monday at 9:00 UTC and includes 7 days of data timestamped between a week ago Monday (T-8) at 00:00:00 UTC and Sunday (T-1) at 23:59:59 UTC:

Json
{  
     "reportConfigurationId":"58bc2d32-28db-43b1-9bc6-1072a72f4589",
     "frequencyConfig": {   
          "scheduleFrequency": "weekly|mon",  
          "reportFileScheduledTime": "09:00:00",  
          "reportingPeriodStartTime": "00:00:00",  
      }
}

Monthly schedule

The following frequencyConfig example describes a report that runs on the third day at 09:00:00 UTC following the end of each month and reports a full UTC month from day 1 at 00:00:00 UTC through the last day at 23:59:59 UTC:

Json
{  
     "reportConfigurationId":"58bc2d32-28db-43b1-9bc6-1072a72f4589",
     "frequencyConfig": {  
         "scheduleFrequency": "monthly|3",  
         "reportFileScheduledTime": "09:00:00",  
         "reportingPeriodStartTime": "00:00:00"  
      }
}
Tip

For "MONTHLY|" the report will run on the day of the month identified. It cannot be greater than 28, meaning a report cannot be scheduled to run on the 29th day of every month. If the reportFileScheduleTime is less than [reportingPeriodStartTime + 4 hours], then the report will contain data from the last day of the month (before the previous month) through the last day of the previous month.

The following frequencyConfig example describes a report that runs on the third day and the report for September 3rd will contain data from July 31st at 11:00:00 through August 31st at 10:59:59:

Json
{  
     "reportConfigurationId":"58bc2d32-28db-43b1-9bc6-1072a72f4589",
     "frequencyConfig": {  
           "scheduleFrequency": "monthly|3",  
           "reportFileScheduledTime": "09:00:00",  
           "reportingPeriodStartTime": "11:00:00",  
       }
}

Adhoc

Adhoc can be used to run scheduled reports on custom date ranges instead of daily, weekly, or monthly. For example, the below adhoc report will include data for the first 15 days of January:

Json
{
    "reportConfigurationId":"58bc2d32-28db-43b1-9bc6-1072a72f4589",
     "frequencyConfig": {  
          "scheduleFrequency": "adhoc",  
          "reportingPeriodStartTimestamp": "2021-01-01 00:00:00",  
          "reportingPeriodEndTimestamp": "2021-01-14 23:00:00"  
     }
}

At any point, you can change the adhoc frequency configuration when triggering the report to run. This does not require updating the report configuration first.

The following is an example of the request body running this adhoc report using a POST call to the /reports endpoint:

Json
{  
     "reportConfigurationId":"58bc2d32-28db-43b1-9bc6-1072a72f4589",                            
     "frequencyConfig": {  
            "scheduleFrequency": "adhoc",  
            "reportingPeriodStartTimestamp":"2021-01-01 00:00:00",                  
            "reportingPeriodEndTimestamp":"2021-01-14 23:00:00"  
        }  
}

Advanced customizations

In addition to the standard attributes provided by the various report types, you can customize reports with the following capabilities:

  • alias — Assign a custom attribute name instead of using the name provided by default.
  • aggregateOperationText — Aggregate report data fields.
  • customCalculation — Calculate the percent of another field.
  • groupBy — Group the selected fields from configuration. This is only applied to calculate customCalculation.

Alias

Alias renames the column in the output report. This is particularly useful for backwards compatibility with systems expecting a different name, or to ensure compatibility with internal nomenclature. You can use an alias on regular attributes, with aggregate fields, or custom calculation fields to name them.

The following example renames the Payment Method Code column to MOP:

Json
{
    "alias": "MOP",
    "reportAttributeName": "Payment Method Code"
}

Aggregate operation text

The aggregateOperationText function provides the ability to aggregate values in the data set. It supports count, distinctCount, avg, min, max, and sum.

Tip

A "group by" is applied implicitly on any attribute added on the report configuration that does not have an aggregate operator applied to it. This means identical attributes without aggregate operators are grouped together.  

The following example sums the Transaction Amount column and renames to Transaction Amount Total:  

Json
{
    "alias": "Transaction Amount Total",
    "aggregateOperationText": "sum",
    "reportAttributeName": "Transaction Amount"
}

Aggregate condition text

The aggregateConditionText function can specify conditions for using the aggregateOperationText.

Tip

Conditional aggregation is only applied when the conditionalAggregateIndicator value is set to true.

The following example sums the Funds Transfer Amount column if the record's Charge Category Type Name equals either “Sale” or “Refund”:

Json
{
    "reportAttributeName": "Funds Transfer Amount",
    "alias": "Net Settled Deposit",
    "aggregateOperationText": "sum",
    "conditionalAggregateIndicator": true,
    "aggregateConditionAttributeName": "Charge Category Type Name",
    "aggregateConditionText": "IN(\"Sale\",\"Refund\")"
}

Conditional aggregate subsection

The conditional aggregate subsection function provides the ability to use multiple operators in conditional aggregation text. Operators supported are AND and OR.

This requires the additional following fields in the configuration:

  • conditionalAggregateInnerConditionList: Provides a list of sub-conditions. These sub - conditions are joined using conditionalAggregateInnerOperator specified in the configuration. Multiple conditionalAggregateInnerConditionLists are allowed and each innerConditionList can specify the operator (AND or OR) specific to that inner list.
  • conditionalAggregateGlobalOperator: Operator to be applied, either OR or AND. This is used as a global operator to join different inner sections.
  • conditionalAggregateInnerOperator: Operator to be applied, either OR or AND. This is used as a joining operator between filters within the inner section.
Json
{
    "reportAttributeName": "Settled Total Fee Amount",
    "alias": "Others",
    "aggregateOperationText": "sum",
    "conditionalAggregateIndicator": true,
    "conditionalAggregateSubSection": {
        "conditionalAggregateGlobalOperator": " OR ",
        "conditionalAggregateSubConditionList": [
            {
                "conditionalAggregateInnerOperator": " AND ",
                "conditionalAggregateInnerConditionList": [
                    {
                        "aggregateSubConditionAttributeName": "chargeCategoryCode",
                        "aggregateSubConditionText": "IN(\"IA\")"
                    },
                    {
                        "aggregateSubConditionAttributeName": "chargeSubCategoryText",
                        "aggregateSubConditionText": "IN (\"Assessment Fees\",\"Cross Border Assessment Fees\")"
                    }
                ]
            },
            {
                "conditionalAggregateInnerOperator": " AND ",
                "conditionalAggregateInnerConditionList": [
                    {
                        "aggregateSubConditionAttributeName": "chargeCategoryCode",
                        "aggregateSubConditionText": "IN(\"IA\")"
                    },
                    {
                        "aggregateSubConditionAttributeName": "chargeSubCategoryText",
                        "aggregateSubConditionText": "IN (\"Assessment Fees\",\"Cross Border Assessment Fees\")"
                    }
                ]
            },
            {
                "conditionalAggregateInnerOperator": " AND ",
                "conditionalAggregateInnerConditionList": [
                    {
                        "aggregateSubConditionAttributeName": "chargeCategoryCode",
                        "aggregateSubConditionText": "IN(\"IA\")"
                    },
                    {
                        "aggregateSubConditionAttributeName": "chargeSubCategoryText",
                        "aggregateSubConditionText": "IN (\"Assessment Fees\",\"Cross Border Assessment Fees\")"
                    }
                ]
            }
        ]
    }
}
Tip

The OR operation is allowed only when conditionalAggregateSubSection is available in configuration. If conditionalAggregateSubSection does not exist, the AND operator is used by default.

Custom calculation

The custom calculation feature currently supports the ability to provide "% of total" calculations. The following three fields are required in the configuration:

  • customCalculationText — Operator to be applied "% of"
  • customCalculationOn — Attribute to which the calculation is applied
  • groupBy — Attributes to group by for the % of calculation

The following example would read: "Calculate the transaction count percentage of total within the payment method code and settlement currency code."

Tip

The "txn count" field referenced below is also a custom field applying the "count" aggregate operator as previously shown.

Json
{
    "alias": "Transaction Count %",
    "customCalculationText": "% of",
    "customCalculationOn": "Txn count",
    "groupBy": [
        "Payment Method Code",
        "Settlement Currency Code"
    ]
}

Additional custom calculations

Additional custom calculation features supported are subtraction ("diff") and division ("div"). This requires the following two fields in the configuration

  • customCalculationText — Operator to be applied, either "diff" or "div".
  • customCalculationOn — Attribute to which the calculation is applied. 

The following example identifies the Count of First Chargebacks divided by Count of Sales to provide the percent of first chargebacks to sales:

Json
{
    "alias": "% of First Chargebacks to Sales",
    "customCalculationText": "Div",
    "customCalculationOn": "Count of First Chargebacks,Count of Sales"
}