How to create Pivot table from CSV settlement report imported in Excel

Ratepay-provided Pivot templates

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
REFERENCEIDACCOUNTING REFERENCE_ID_ACCOUNTING
CHARGEBACK_REASON

How to use the template provided by Ratepay

  1. Select the Ratepay-provided Excel template that fits to your version of CSV settlement report
  2. Import your CSV settlement report as per our Guide

c11

  1. Copy the imported content into the "Input" sheet of our template. Use paste as "Values" to not corrupt the data

c00

  1. In the "Pivot" sheet under "Analyze", press "Refresh"

c01

  1. The data is now visualized in the Pivot table

c2

How to create a Pivot table from a CSV settlement report imported in Excel

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

c11 1

  1. In the sheet containing the imported settlement report, mark all data by pressing the Select All button (or use the keyboard shortcut CTRL + A)

c33

  1. Go to the Insert tab and from the Tables group select PivotTable

c44

  1. Select "OK"

c55

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

Tips for adjusting the Pivot table to your needs

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:

c6

  1. Click anywhere on the Pivot Table
  2. On the "Design" tab, in the "Layout" group, click "Report Layout", and click "Show in Tabular Form"

c7

  1. The data is now represented in a tabular format

c8

Removing sub-totals

By default, PivotTables calculate and show subtotals. If you want to disable them

  1. Click anywhere on the Pivot Table
  2. On the "Design" tab, in the "Layout" group, click "Subtotals", and click "Do not show subtotals"

c8 1

  1. The subtotals are now removed from the Pivot table

c9