Reports Read

pixiReportOrderOverview

Returns an overview of orders with billing address, order totals, net gross profit and currency, optionally filtered by order date and order number.

Returns one row per order from OrderHeader, joined with the linked address (AddrRef = AddrKey) and the order currency.

Filters

Date range: if OrderDateFrom is set, only orders whose OrderDate lies between OrderDateFrom and OrderDateTo (inclusive) are returned. If OrderDateTo is empty, it is set to the current date and time. If OrderDateFrom is empty, no date filter is applied, even if OrderDateTo is set.
Order number: if OrderNrStart is set, only orders with OrderNr >= OrderNrStart are returned.
Both filters are combined with AND.

Row limit and sort order

The number of rows is limited to RowCount (default 1000).
Rows are sorted by OrderNr descending, so the newest orders (highest order numbers) come first.

Calculated value

GrossProfit_Netto is the sum of Qty * (Price_Net - SupplPrice) over all order lines of the order.

Notes

According to the code comment, the gross profit is calculated from the standard supplier price stored on the order line at the time of order (SupplPrice). It is not comparable with other gross profit reports in Pixi.
GrossProfit_Netto is NULL for orders without order lines.
Orders without a matching address or currency are excluded because of the inner joins.
The date comparison uses BETWEEN on a datetime value. A date-only OrderDateTo (e.g. 2024-03-31) means midnight, so orders later on that day are not included. Pass the following day or a time of 23:59:59 instead.
Because rows are sorted descending and cut off by RowCount, OrderNrStart does not work as a forward paging cursor: you always get the highest order numbers that meet the filters. If more than RowCount orders match, the older ones are not returned.
RowCount is applied with SET ROWCOUNT. A value of 0 removes the limit.
All tables are read with NOLOCK, so uncommitted data may appear.
The call only reads data.

Tables used

OrderHeader · Read Addresses · Read Currency · Read Orderlines · Read

Parameter	Type	Default	Description
`OrderDateFrom`	datetime dateTime	NULL	Start of the order date range (inclusive). If `NULL`, no date filter is applied. Example: `2024-03-01T00:00:00`
`OrderDateTo`	datetime dateTime	NULL	End of the order date range (inclusive). If `NULL` while `OrderDateFrom` is set, the current date and time is used. Ignored if `OrderDateFrom` is `NULL`. Example: `2024-03-31T23:59:59`
`OrderNrStart`	int	NULL	Lowest order number to return (`OrderNr` >= value). If `NULL`, no order number filter is applied. Example: `150000`
`RowCount`	int	1000	Maximum number of rows to return. `0` means no limit. Example: `500`

Result set 1 · Always (may be empty)

One row per matching order, sorted by OrderNR descending, limited to RowCount rows.

Column	Type	Description
`OrderNR`	int	Order number (`OrderHeader.OrderNr`).
`Anrede` Nullable	string	Salutation of the address.
`Name` Nullable	string	Name of the address.
`CareOf` Nullable	string	Care-of / additional address line.
`Address` Nullable	string	Street and house number.
`City` Nullable	string	City.
`ZIP` Nullable	string	Postal code.
`Country` Nullable	string	Country of the address.
`eMail` Nullable	string	E-mail address.
`Shop_Note` Nullable	string	Note from the shop on the order.
`GiftMessage` Nullable	string	Gift message of the order.
`Address_Remarks` Nullable	string	Address remarks stored on the order.
`PaymentCode` Nullable	string	Payment method code of the address (`Addresses.APayment`).
`OrderStatus` Nullable	string	Status of the order.
`OrderLinesTotal` Nullable	decimal	Total of the order lines.
`OrderLinesDiscount` Nullable	decimal	Discount on the order lines.
`ShipCost` Nullable	decimal	Shipping cost of the order.
`Discount` Nullable	decimal	Order discount.
`OrderTotal` Nullable	decimal	Total amount of the order.
`GrossProfit_Netto` Nullable	decimal	Net gross profit: sum of `Qty * (Price_Net - SupplPrice)` over all order lines. `NULL` if the order has no lines.
`OrderDate` Nullable	datetime dateTime	Order date.
`ShopID` Nullable	string	Shop ID of the address (`Addresses.AddrShopID`).
`CurrencyCode` Nullable	string	Currency code of the order (`pixi.Currency.CodeA`).

Response example

<SqlRowSet1>
  <row>
    <OrderNR>150231</OrderNR>
    <Anrede>Herr</Anrede>
    <Name>Max Mustermann</Name>
    <CareOf/>
    <Address>Hauptstraße 12</Address>
    <City>München</City>
    <ZIP>80331</ZIP>
    <Country>DE</Country>
    <eMail>max@example.com</eMail>
    <Shop_Note/>
    <GiftMessage/>
    <Address_Remarks/>
    <PaymentCode>VK</PaymentCode>
    <OrderStatus>AUF</OrderStatus>
    <OrderLinesTotal>119.90</OrderLinesTotal>
    <OrderLinesDiscount>0.00</OrderLinesDiscount>
    <ShipCost>4.90</ShipCost>
    <Discount>0.00</Discount>
    <OrderTotal>124.80</OrderTotal>
    <GrossProfit_Netto>38.45</GrossProfit_Netto>
    <OrderDate>2024-03-15T10:22:31</OrderDate>
    <ShopID>SHOP1</ShopID>
    <CurrencyCode>EUR</CurrencyCode>
  </row>
</SqlRowSet1>

POST https://apigateway.descartes.com/tms/pixi/
Content-Type: text/xml; charset=utf-8
Authorization: Basic BASE64(USERNAME:PASSWORD)

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiReportOrderOverview>
      <!-- <OrderDateFrom>2024-03-01T00:00:00</OrderDateFrom> -->
      <!-- <OrderDateTo>2024-03-31T23:59:59</OrderDateTo> -->
      <!-- <OrderNrStart>150000</OrderNrStart> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReportOrderOverview>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

curl -X POST 'https://apigateway.descartes.com/tms/pixi/' \
  --user 'USERNAME:PASSWORD' \
  --header 'Content-Type: text/xml; charset=utf-8' \
  --data-binary @- <<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiReportOrderOverview>
      <!-- <OrderDateFrom>2024-03-01T00:00:00</OrderDateFrom> -->
      <!-- <OrderDateTo>2024-03-31T23:59:59</OrderDateTo> -->
      <!-- <OrderNrStart>150000</OrderNrStart> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReportOrderOverview>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
XML

<?php

$soapClient = new SoapClient(null, [
    'login' => 'USERNAME',
    'password' => 'PASSWORD',
    'location' => 'https://apigateway.descartes.com/tms/pixi/',
    'uri' => 'https://apigateway.descartes.com/tms/pixi/',
]);

$arguments = [
    // 'OrderDateFrom' => '2024-03-01T00:00:00',
    // 'OrderDateTo' => '2024-03-31T23:59:59',
    // 'OrderNrStart' => '150000',
    // 'RowCount' => '500',
];

$soapArguments = [];
foreach ($arguments as $name => $value) {
    $soapArguments[] = new SoapVar($value, XSD_STRING, '', '', $name);
}

$response = $soapClient->__soapCall('pixiReportOrderOverview', $soapArguments);

SQL object:: dbo.pipiReportOrderOverview