SD Report Custom development ALV report

Export Sales Exchange Rate Effect and Margin by Currency Report

Short answer: A custom ALV report that splits the change in local currency revenue from foreign currency billing items into volume, price and exchange rate effects, and shows the gross margin by customer, country and currency. Exporters see how much of the revenue growth really comes from sales and how much from currency movements.

Estimated effort
M · 6–8 working days
System
ECC and S/4HANA
Tables
7
Scope
Global
Built by
NeKuDos Teknoloji
Last updated

Why is the standard not enough?

VF05 lists billing documents; KE30 profitability reports can show revenue in two currencies depending on configuration. There is no standard key figure structure that splits the revenue difference between two periods into volume, price and exchange rate effects, so this is usually done by hand in Excel. The custom report makes the split at billing item level with the posted rates.

Who uses it? Sales and export managers, finance and controlling teams, company management.

Closest standard alternatives

  • KE30 Execute Profitability Report. Profitability reports can show revenue in two currencies; they do not split it into volume, price and exchange rate effects.
  • VF05 List Billing Documents. Lists billing documents; there is no period comparison or exchange rate effect.

Tables used

The tables the report reads and their join fields. ECC and S/4HANA differences are marked separately.

TableRoleJoinSystem
VBRKBilling Document HeaderBilling header: document currency, accounting rate, billing date (driving table)—ECC + S/4
VBRPBilling Document: Item DataBilling item: quantity, net value, costVBRP-VBELN = VBRK-VBELNECC + S/4
KNA1Customer Master (General Section)Customer name and countryKNA1-KUNNR = VBRK-KUNAGECC + S/4
T001Company CodesCompany code currencyT001-BUKRS = VBRK-BUKRSECC + S/4
TCURRExchange RatesAverage rate for the selected exchange rate typeTCURR-KURST = P_KURST AND TCURR-FCURR = VBRK-WAERK AND TCURR-TCURR = T001-WAERSECC + S/4
MAKTMaterial DescriptionsMaterial descriptionMAKT-MATNR = VBRP-MATNR AND MAKT-SPRAS = SY-LANGUECC + S/4
I_BillingDocumentItemBilling Document ItemIn S/4HANA it can replace VBRP with a clean core approachBillingDocument = VBRK-VBELNS/4HANA only

Selection screen parameters

ParameterDescriptionTypeReference fieldRequiredExample
S_BUKRSCompany codeRange / multipleVBRK-BUKRSYes1000
S_VKORGSales organizationRange / multipleVBRK-VKORGNo1000
S_FKDATCurrent period billing dateRange / multipleVBRK-FKDATYes01.07.2026 – 30.09.2026
S_FKDA0Comparison period billing dateRange / multipleVBRK-FKDATYes01.07.2025 – 30.09.2025
S_WAERKDocument currencyRange / multipleVBRK-WAERKNoEUR, USD, GBP
S_LAND1Customer countryRange / multipleKNA1-LAND1NoDE, NL, GB
P_KURSTAverage rate type (if empty, the billing rate is used)Single valueTCURR-KURSTNoM
P_LEVELDetail level: C = customer, M = material, D = currencySingle value—NoM

Output columns

#ColumnSource
1CustomerKNA1-NAME1
2CountryKNA1-LAND1
3CurrencyVBRK-WAERK
4Quantity (current)Calculated Σ VBRP-FKIMG, credit memos negative
5Revenue (document currency)Calculated Σ VBRP-NETWR
6Average rate (current)Calculated Local revenue / revenue in document currency
7Revenue (local, current)Calculated Σ VBRP-NETWR × VBRK-KURRF
8Revenue (local, comparison)Calculated Σ VBRP-NETWR × VBRK-KURRF in the comparison period
9Volume effectCalculated (Q1 − Q0) × P0 × R0
10Price effectCalculated (P1 − P0) × Q1 × R0
11Exchange rate effectCalculated Q1 × P1 × (R1 − R0)
12CostCalculated Σ VBRP-WAVWR
13Gross margin %Calculated (Local revenue − cost) / local revenue × 100

Sample output

CustomerCountryCurrencyQuantity (current)Revenue (document currency)Average rate (current)Revenue (local, current)Revenue (local, comparison)Volume effectPrice effectExchange rate effectCostGross margin %
Nordic Apparel GmbHDEEUR42,000252,00047.8012,045,6009,126,0001,162,800516,6001,240,2009,154,80024
Demo Retail Ltd.GBGBP18,500129,50055.607,200,2006,105,000349,20072,600673,4005,616,40022
Holland Textiles B.V.NLEUR9,60061,44047.902,943,0003,168,000-712,800115,200372,6002,384,00019
Atlantic Garments Inc.USUSD26,000135,20041.205,570,2004,416,000368,000197,600588,6004,511,00019
A view built with fictional sample data; not real system data.

How does the report work?

  1. Billing documents and credit memos that are not cancelled and fall into the current or comparison period are read from VBRK for the selected company code and sales organization; only those whose document currency differs from the company code currency (T001-WAERS) remain.
  2. Quantity (FKIMG), net value in document currency (NETWR) and cost (WAVWR) come from VBRP; credit memos are counted with a negative sign.
  3. Revenue in local currency = NETWR × VBRK-KURRF (the rate passed to accounting). Depending on the selection, the average rate in TCURR can be used instead.
  4. For each customer, material and currency, the average unit price (P) and average rate (R) are calculated for the current (1) and comparison (0) periods.
  5. Volume effect = (Q1 − Q0) × P0 × R0; price effect = (P1 − P0) × Q1 × R0; exchange rate effect = Q1 × P1 × (R1 − R0). The three effects add up to the local revenue difference.
  6. Gross margin = local revenue − cost; subtotals are given by customer, country and currency.

Performance notes

Limit VBRK by company code, billing date and document currency and read VBRP for the selected documents. Buffer TCURR rates once per period. With large volumes, run the report in the background.

Features

  • ALV list (sort, filter, subtotals)
  • User-specific layout variants
  • Double-click drill-down to the document Double-clicking a line opens the related billing documents in VF05; double-clicking a document opens VF03.
  • Export to Excel
  • Runs as a background job

Authorization objects

  • V_VBRK_VKOBilling document display authorization by sales organization
  • V_VBRK_FKADisplay authorization by billing type

Frequently asked questions

Which exchange rate is used?

By default the rate with which the invoice was passed to accounting (VBRK-KURRF) is used, so the figures match revenue in the general ledger. Optionally the period average of a selected exchange rate type can be used for the comparison.

Are exchange differences on collection included?

No. The report shows the exchange rate effect on the sales side. Exchange gains and losses realized on collection are tracked separately in accounting; a separate report can be used for the valuation of open items.

Does it work on S/4HANA?

Yes. VBRK and VBRP are the same in S/4HANA; if clean core is preferred, billing items are read via I_BillingDocumentItem.

Related reports

SD Report Custom ALV report

Sales Order Gross Margin Report by Item

A custom ALV report that compares the net value of every sales order item with the cost determined in pricing and shows the gross profit and margin …

VBAK · VBAP · KNA1 · KONV

Let us build this report for your system

Briefly describe your system and expectations. We will clarify the scope and come back with an effort and price quote.

Related report: Export Sales Exchange Rate Effect and Margin by Currency Report