Understanding Reports Module

What can you do?

• Create custom reports by selecting relevant fields.
• Filter data based on dynamic and static values.
• Apply aggregation functions to summarize data.
• Group and order results for better readability.
• Schedule reports to be generated at specific times.

Key Concepts

Filtering

Filtering allows you to extract specific data by setting conditions. For example, if you want only payments made in the last 7 days:

json

{
    "filters": [
        {
            "field": { "id": 3 },
            "filter_operator": ">=",
            "value": "LAST_7_DAYS"
        }
    ]
}

This filter ensures only transactions that happened in the last 7 days are included in the report.

Aggregation

Aggregation helps summarize data by using functions like:

• SUM: Adds all values in a column.
• AVG: Calculates the average value.
• COUNT: Counts the number of occurrences.
• MIN: Returns the smallest value in a column.
• MAX: Returns the largest value in a column.
• STDDEV: Calculates the standard deviation of values in a column.

Example: To get the total amount of payments received:

json

{
    "fields": [
        {
            "alias": "Total Amount",
            "field": { "id": 1 },
            "aggregation_operator": "SUM"
        }
    ]
}

This will return a single value representing the sum of all payments.

Grouping

Grouping organizes data by a particular field. If you want to see the total amount per merchant:

json

{
    "group_by": ["merchant_id"],
    "fields": [
        {
            "alias": "Merchant ID",
            "field": { "id": 92 }
        },
        {
            "alias": "Total Amount",
            "field": { "id": 1 },
            "aggregation_operator": "SUM"
        }
    ]
}

This groups transactions by merchant and calculates the total amount per merchant.

Wrapping Functions

Wrapping allows modifying text fields, for example:

• UPPER: Converts text to uppercase.
• LOWER: Converts text to lowercase.
• TRIM: Removes leading and trailing spaces.
• LENGTH: Returns the number of characters in a string.
• DATE: Extracts a date from a datetime. (2024-12-06 08:10:00 => 2024-12-06).
• YEAR: Extracts the year from a date or datetime. (2024-12-06 08:10:00 => 2024).
• QUARTER: Extracts the quarter from a date or datetime. (2024-12-06 08:10:00 => 4).
• MONTH: Extracts the month from a date or datetime. (2024-12-06 08:10:00 => 12).
• WEEK_OF_YEAR: Extracts the week of the year from a date or datetime. (2024-12-06 08:10:00 => 49).
• DAY_OF_MONTH: Extracts the day of the month from a date or datetime. (2024-12-06 => 6).
• DAY_OF_WEEK: Extracts the day of the week from a date or datetime. (2024-12-06 => 5).
• DAY_OF_YEAR: Extracts the day of the year from a date or datetime. (2024-12-06 => 341).
• HOUR: Extracts the hour from a time or datetime. (2024-12-06 08:10:00 => 08).
• MINUTE: Extracts the minute from a time or datetime. (2024-12-06 08:10:00 => 10).
• AGE_IN_MINUTES: Calculates the elapsed time from a given date to the current date-time, returning the result in minutes. (2024-12-06 08:10:00, 2024-12-06 07:00:00 => 70).
• AGE_IN_HOURS: Calculates the elapsed time from a given date to the current date-time, returning the result in hours. (2024-12-06 08:10:00, 2024-12-06 07:00:00 => 1.16).
• AGE_IN_DAYS: Calculates the elapsed time from a given date to the current date-time, returning the result in days. (2024-12-06 08:10:00, 2024-10-01 07:00:00 => 65.01).
• AGE_IN_YEARS: Calculates the elapsed time from a given date to the current date-time, returning the result in years. (2024-12-06 08:10:00, 2023-10-01 07:00:00 => 1.13).

Example: To get merchant names in uppercase:

json

{
    "fields": [
        {
            "alias": "Uppercase Merchant Name",
            "field": { "id": 68 },
            "wrap": "UPPER"
        }
    ]
}

Aliases

Aliases allow renaming columns for better readability. Instead of displaying raw field names, you can define user-friendly names. Example:

json

{
    "fields": [
        {
            "alias": "Transaction Amount",
            "field": { "id": 1 }
        }
    ]
}

This changes the column name from amount to Transaction Amount in the report.

Arithmetic Operations

You can perform calculations between fields, such as:

• Subtracting fees from the total amount.
• Summing multiple columns.

Available Arithmetic Operations

You can perform different types of calculations, such as:

• Addition (+): Summing multiple fields.
• Subtraction (-): Calculating differences between fields.
• Multiplication (*): Multiplying values for weighted calculations.
• Division (/): Finding ratios or percentages.
• Percentage (%): Calculating percentage values based on fields.

Example: To calculate net revenue (Amount - Fee):

json

{
    "fields": [
        {
            "alias": "Net Revenue",
            "arithmetic_operator": "-",
            "field": { "id": 1 },
          },
          {
   "field": { "id": 10 }
           }
    ]
}

This returns the difference between Amount and Fee.

Calculated Field

A Calculated Field allows you to perform arithmetic operations between multiple fields and return the result as a new field. This is useful for generating custom values in reports, such as net revenue, total sales, or profit margins. The result is exported in a CSV sheet for further analysis. This will return a new column in the CSV sheet that represents the difference between Amount and Fee.

Available Arithmetic Operations

You can perform different types of calculations, such as:

• Addition (+): Summing multiple fields.
• Subtraction (-): Calculating differences between fields.
• Multiplication (*): Multiplying values for weighted calculations.
• Division (/): Finding ratios or percentages.
• Percentage (%): Calculating percentage values based on fields.

With Calculated Fields, you can generate dynamic reports tailored to your specific business needs. Example Usage: Calculating Net Revenue

If you want to calculate the Net Revenue by subtracting the Fee from the Amount, you can use:

json

{
    "fields": [ 
		{
			"arithmetic_operator": "-", 
			"field": { "id": 1 }
		},
		{
			"alias": "Net Revenue",
			"field": { "id": 10 }
		}
	]
}

Output CSV (After Calculation):

CSV

Transaction ID,Amount,Fee,Net Revenue
1001,500.00,20.00,480.00
1002,750.00,30.00,720.00
1003,1200.00,50.00,1150.00

Table example:

Transaction ID	Amount	Fee	Net Revenue
1001	500.00	20.00	480.00
1002	750.00	30.00	720.00
1003	1200.00	50.00	1150.00

Dynamic Values

Dynamic Values allow you to work with relative time-based data, automatically adjusting to the current date or predefined periods. These values help create flexible filters and reports without needing manual updates.

Available Dynamic Values:

• CURRENT_YEAR: Data from the current calendar year.
• CURRENT_MONTH: Data from the current month.
• CURRENT_WEEK: Data from the current week.
• CURRENT_DAY: Data from today.
• NOW: The exact current date and time.
• LAST_90_DAYS: Data from the last 90 days.
• LAST_60_DAYS: Data from the last 60 days.
• LAST_45_DAYS: Data from the last 45 days.
• LAST_30_DAYS: Data from the last 30 days.
• LAST_15_DAYS: Data from the last 15 days.
• LAST_7_DAYS: Data from the last 7 days.
• LAST_DAY: Data from the last full day before today.

Example Usage: Filtering Transactions from a Dynamic Time Range If you want to retrieve only transactions from the current month, you can use:

json

{
    "filters": [ 
        { 
            "field": { "id": 3 }, 
            "filter_operator": ">=", 
            "value": "CURRENT_MONTH"
        }
    ]
}

These values provide a flexible way to filter data dynamically, ensuring reports remain up to date without manual adjustments.