We are excited to announce that the Calculated Columns feature in the CRM Residuals Reports has just received a major new enhancement!
The feature enables ISOs to automatically add additional columns to a CSV or Excel file being imported into the system for residuals reporting.
Up until now, adjustments to columns could only be made on numeric data using basic calculation tools such as addition (+), subtraction (-), division (/), and multiplication (*) as the available operations.
With the new update it’s now possible to use Excel-like formulas to manipulate the data being imported!
ISOs with custom report requirements will no longer need to pre-process their residuals data using Excel templates saved on disk, and then import into the CRM.
Instead, the calculation ability is provided right on the residuals import page, which makes the whole procedure way quicker and easier!
Let’s take a closer look at the new update, and how the new enhancements can be used to solve some real-life scenarios.
Adding a Calculated Column
To add a new calculated column on the residuals reports mapping page, click the Add button in the Calculations row:
In the popup which appears, enter the new Column Name (in this example it’s ‘Volume’):
Next, select the columns from your CSV or Excel file that will be used in your formula (i.e. Equation):
As you select the columns they will be added below the Column(s) dropdown:
After you’ve selected the columns that you need for your formula, enter the formula in the Equation field and click Add.
In the example below, we’re evaluating the “Commission Code” column from the imported file.
If the “Commission Code” is set to ‘2’, then the new “Volume” column will show the “Quantity Billed” value from the original file (otherwise it shows zero):
Quick Tip: Clicking on any variable automatically inserts the variable’s code in the Equation field.
Once your calculated column is saved it will be displayed in the Calculations row. If you need to delete it, click on the X icon in the top right corner of the formula widget:
The calculated column now appears in the list of mappable columns together will all of the original report columns.
To map a calculated column to a CRM field simply drag and drop the column to the appropriate field:
Creating Formulas
The new functions built into the CRM allow you to create formulas in the same way as you would create them in MS Excel, and you can also nest multiple functions.
Unlike MS Excel though, when you add a new formula in the CRM you should not add the “=” character at the beginning of the formula.
The column formulas typically take an input value and transform it into a new output value.
The input values are fed into the formula using variables. Each column in the CSV or Excel file you’re importing represents a single variable.
To add a variable to your formula enclose it in curly braces like this: {column_name}
The variable names are case-sensitive, and the function names must be capitalized.
Here is a table with the descriptions of the available functions and their syntax:
In this table, we provide some basic usage examples using sample variables:
Now let’s take a look at some real world scenarios and how they can be solved using calculated columns.
Scenario #1
Challenge: The Merchant Accounts in the ISO’s residuals report are not in a format that can be used for residual mapping.
The accounts are designated by a Site ID which is in text format (for example “RPMEZZ”) but the CRM only allows numeric MIDs (for example 682807178).
Solution: Add a new calculated column to the residuals report import called “Merchant ID” which will automatically convert the Site IDs to numeric codes that the CRM can accept.
Here is an image showing the newly created “Merchant ID” column which were then mapped to the CRM Merchant ID field:
Scenario #2
Challenge: An ISO has a residuals report which bundles the number of transactions and the volume amounts in a single column called “Quantity Billed” in the CSV report (along with other items). However the CRM requires transactions and volumes to be listed in separate columns.
Another column called “Commission Code” in the CSV file contains codes which determine what type of information is shown in the “Quantity Billed” column such as transactions (code 3), volume (code 2), or other.
Solution: Add two new calculated columns to the residuals report import called “Transactions” and “Volume” as follows:.
- “Transactions” — this column evaluates the “Commission Code” column in the imported CSV file. If a cell in the column contains code 3, then the number of transactions for that merchant is pulled from the “Quantity Billed” column. Otherwise the number of transactions is set to zero.
- “Volume” — this column also evaluates the “Commission Code” column in the CSV file. If a cell in the column contains code 2, then the Volume information for that merchant is pulled from the “Quantity Billed” column. Otherwise the volume is set to zero.
Here is an image showing the newly created “Transactions” and “Volume” columns which were then mapped to the CRM fields:
Scenario #3
Challenge: An ISO does not share the revenue on certain fees with their agents. The agents can view the Details tab on the residual reports, and they compare the revenue fees with the fees that appear on merchant statements.
Since this is the case, the usual way of removing the revenue for these items from the income will not work for the client. The client requires the CRM to display the income and add a pass-through expense for the fee.
Solution: Use calculated columns to create corresponding expense fees for each revenue amount as a negative item in the mapping.
Here is an image showing the newly created expense columns which were then added to the residuals expenses:
And there you go! We hope you’ll enjoy using the new update.
Stay tuned for more updates coming soon!
Don’t have IRIS CRM?
Join over 200 ISOs using IRIS CRM to grow their business.
Schedule a demo to learn more.
For sales tips and the latest electronic payment news check out The IRIS CRM Daily Bulletin and for the latest product updates and company news please join our newsletter.