Configure your CSV template

Before you can export the billing lines for a specific month (billing session), you need to configure the column layout and structure of your CSV export file.

To access the CSV template configuration, select Organization > Solution settings > CSV from the options. On the CSV settings page choose Configure your CSV template. On this page you can:

  • Configure regional settings
  • Configure CSV file
  • Download CSV preview file

 

mceclip1

Regional settings

The default values for the character used for decimal separator and the character used for delimiter in a CSV file may differ per region. Click the Edit-button to set the appropriate values. For decimal separator you can only choose between "." (dot) and "," (comma). The value for delimiter can be any character. The most commonly used characters are comma and semi-column.

Configure CSV file

If no CSV file configuration has been created, you can create a default configuration by clicking the corresponding button or you can start adding columns by clicking the Add-button. 

The following properties can be set for every column:

  • CSV Column name
  • CSV Line type
  • Mapped SpinPanel Key
  • CSV Column order

 

mceclip2

CSV Column name

The top row in the CSV export file will always be a header row containing the column names. The value you enter will appear in the column name header row.

CSV Line type

Apart from the Column name header row, rows in the CSV export file can be Invoice header lines or Detail lines. Detail lines will appear in the export file for every billing line to be exported. Invoice header lines will only appear in the export file once for every new customer (new invoice). 

For every column you can choose one of the following line type options:

  • Header: the column will only have a value on every invoice header row.
  • Detail: the column will only have a value on every detail row.
  • Both: the column will have a value on both invoice header rows and detail rows. 

The CSV export file will only have Invoice header lines if CSV Line type is set to Header or Both for at least one column.

Mapped SpinPanel Key

You can determine what value is placed in a column by selecting a SpinPanel Key from the drop-down list. Below you will find an overview of all possible SpinPanel Keys and what they represent:

  • None: the column will appear in the export file without a value.
  • BillingLineType: an indicator of the line type of a row. For Invoice Header lines the column will contain the value 0, for Detail lines it will contain the value 1. Typically this column is only useful if your export file has both Invoice header lines and Detail lines,
  • BillingMonth: the value for the column will be the month of the billing session for which the billing lines are being exported.
  • BillingYear: the value for the column will be the year of the billing session for which the billing lines are being exported.
  • ChargePeriodEndDate: the value for the column will be the end date of the charge period for the relevant billing line from your Microsoft invoice. *)
  • ChargePeriodStartDate: the value for the column will be the start date of the charge period for the relevant billing line from your Microsoft invoice. *)
  • Description: the column will contain the value set for Invoice service name in the settings for the service the given resource belongs to (Solutions > CSV > Services). *)
  • ExportDate: the value for the column will be the current date, i.e. the date on which the CSV file is created.
  • ExternalCustomerName: the column will contain the value set for CSV customer name in the settings for the relevant customer (Solutions > CSV > Customers)
  • ExternalCustomerNumber: the column will contain the value set for CSV customer No in the settings for the relevant customer (Solutions > CSV > Customers).
  • ExternalItemNumber: the column will contain the value set for CSV resource No in the settings for the relevant resource (Solutions > CSV > Resources). *)
  • HeaderLineText: the column will contain the value set for Headline text in the general settings (Settings > Solutions > CSV > Configure your CSV service).
  • ImportDate: the column will contain the date on which the billing lines for the relevant billing session where imported into CSV billing connector. 
  • InvoiceNo: the value in this column will change with every new customer in the export lines. It has the following format: “F{0}{1}.{2}”. Where {0} will be replace by the year of the relevant billing session, {1} will be replaced by the month of the billing session and {2} will be replaced by an ascending sequence number.
  • InvoiceServiceName: the column will contain the value set for Invoice service name in the settings for the service the given resource belongs to (Solutions > CSV > Services). *)
  • OrganisationDomainName: currently not in use, the column will remain empty.
  • Amount: the value for amount will be Quantity * UnitPrice. *)
  • Quantity:
    • For Office resources the value for quantity will be the usage from your Microsoft invoice for the specific billing period, except when overridden Quantity is set on the corresponding contract then that value will be used.
    • For Azure resources the value will always be 1. *)
  • StockKeepingUnit: the column will contain the Microsoft SKU for the relevant resource. *)
  • UnitPrice:
    • For Office resources, if no markup is set on the relevant contract line then the value for this column will be the relevant ERP price from your MS invoice. If a positive markup value is set then then the following formula is used to calculate the value for the column: 100 / (100 – markup value) * ERP Price. If a negative markup value is set then this formula will be used to calculate the value for the column: (100 + markup value) / 100 * ERP Price.
    • For Azure resources, the total amount for that resource will be used to calculate the UnitPrice using the following formula: 100 / (100 - markup value) * Total amount. *)
  • PaymentCondition: the column will contain the value set for Payment condition in the settings for the relevant customer (Solutions > CSV > Customers).
  • Currency: the currency from the relevant MS invoice.
  • ProviderCustomerName: the value for the column will be the relevant customer's name in SpinPanel.
  • ProviderInvoiceReference: the value for the column will be the Invoice ID from the relevant Microsoft invoice.
  • ProviderResourceName: the value for the column will be the name of the relevant resource in SpinPanel. *)

*) It makes no sense to use this key in Invoice header lines.

CSV Column order

You can enter a numeric value to determine the position the column will have in the CSV export file. The column having the lowest CSV Column order value will be the leftmost column, the column having the highest value will be the rightmost column. 

Download CSV preview file

You can check your CSV template configuration by clicking the Download CSV preview file link. The information in the preview will be based on predefined values.