⏱ 5 min
Download: Ratepay_pivot_v1.xlsx
Download: Ratepay_pivot_v2.xlsx
Download: Ratepay_pivot_v3.xlsx
Download: Ratepay_pivot_v4.xlsx
Included columns per version
| v1 | v2 | v3 | v4 |
|---|---|---|---|
| SHOP_ID | SHOP_ID | SHOP_ID | SHOP_ID |
| PAYMENTDATE | PAYMENTDATE | PAYMENTDATE | PAYMENTDATE |
| PAYMENTMONTH | PAYMENTMONTH | PAYMENTMONTH | PAYMENTMONTH |
| SHOPNAME | SHOPNAME | SHOPNAME | SHOPNAME |
| AMOUNT | AMOUNT | AMOUNT | AMOUNT |
| DESCRIPTOR | DESCRIPTOR | DESCRIPTOR | DESCRIPTOR |
| SHOPINVOICE_ID | SHOPINVOICE_ID | SHOPINVOICE_ID | SHOPINVOICE_ID |
| SHOPSORDER_ID | SHOPSORDER_ID | SHOPSORDER_ID | SHOPSORDER_ID |
| INVOICENUMBER | INVOICENUMBER | INVOICENUMBER | INVOICENUMBER |
| DESCRIPTION | DESCRIPTION | DESCRIPTION | DESCRIPTION |
| FEETYPE | FEETYPE | FEETYPE | FEETYPE |
| ORDERDATE | ORDERDATE | ORDERDATE | ORDERDATE |
| SENTDATE | SENTDATE | SENTDATE | SENTDATE |
| TRANSACTION_ID | TRANSACTION_ID | TRANSACTION_ID | TRANSACTION_ID |
| CUSTOMERGROUP | CUSTOMERGROUP | DEALER_ID | |
| KNOWNCUSTOMER | KNOWNCUSTOMER | ||
| PRODUCT | PRODUCT | ||
| REFERENCE_ID_ACCOUNTING | REFERENCE_ID_ACCOUNTING | ||
| CHARGEBACK_REASON |
- Select the Ratepay-provided Excel template that fits to your version of CSV settlement report
- Import your CSV settlement report as per our Guide
- Copy the imported content into the "Input" sheet of our template. Use paste as "Values" to not corrupt the data
- In the "Pivot" sheet under "Analyze", press "Refresh"
- The data is now visualized in the Pivot table
Problem: When the CSV settlement report is imported into Excel it does not show information on transaction level
Solution: Use the integrated Pivot functionality of Excel to adjust the report to your needs
- In the sheet containing the imported settlement report, mark all data by pressing the Select All button (or use the keyboard shortcut CTRL + A)
- Go to the Insert tab and from the Tables group select PivotTable
- Select "OK"
- Adjust the Pivot table to fit your needs. Take a loot at our tips below or use one of our templates at the top of the page
Multiple identifiers on same row
When adding multiple identifiers to the "row" PivotTable-Fields part, they are represented hierarchically. To avoid this issue switch the table to Tabular form:
- Click anywhere on the Pivot Table
- On the "Design" tab, in the "Layout" group, click "Report Layout", and click "Show in Tabular Form"
- The data is now represented in a tabular format
Removing sub-totals
By default, PivotTables calculate and show subtotals. If you want to disable them
- Click anywhere on the Pivot Table
- On the "Design" tab, in the "Layout" group, click "Subtotals", and click "Do not show subtotals"
- The subtotals are now removed from the Pivot table
