Order Reporting

Active Commerce PowerShell Extensions

For order reporting, we recommend using the Active Commerce PowerShell Extensions. This currently includes several order reports, including:

• Orders
• Sales Totals
• Product Sales Totals

With some basic knowledge of Sitecore Powershell, it is simple to modify these existing reports and add custom reports to suit your business requirements.

Active Commerce Database - SQL

Of course, you can also write SQL directly against the Active Commerce database.

For example, a simple query to show product aggregates including quantity sold and total revenue would look something like this:

SELECT li.[Item_Code] as Code
      ,li.[Item_Name] as Name
      ,li.[Item_AdditionalInformation] as VariantDetails
      ,SUM(li.[Quantity]) as QuantitySold
      ,SUM(li.[LineExtensionAmount_Value]) as TotalRevenue
FROM  [dbo].[LineItem] li
GROUP BY li.[Item_Code], li.[Item_Name], li.[Item_AdditionalInformation]

Here is an example for retrieving basic order information:

DECLARE @orderNumber VARCHAR(10) = '1234'

-- Customer Info
SELECT 
 cp.SupplierAssignedAccountID AS CustomerId,
 ct.ElectronicMail AS Email
FROM dbo.[Order] o
JOIN dbo.[CustomerParty] cp ON cp.Party_Id = o.BuyerCustomerParty_Id
JOIN dbo.[Party] pt ON pt.Alias = cp.Alias
JOIN dbo.[Contact] ct ON ct.Alias = pt.Contact_Id
WHERE o.OrderId = @orderNumber

-- Billing Address
SELECT 
 pt.Person_FirstName AS FirstName,
 pt.Person_FamilyName AS LastName,
 ad.AddressLine AS Address1,
 ad.AddressLine2 AS Address2,
 ad.CityName AS City,
 ad.CountrySubentity AS [State],
 ad.PostalZone AS PostalCode,
 ad.Country AS Country,
 ct.Telephone
FROM dbo.[Order] o
JOIN dbo.[CustomerParty] cp ON cp.Party_Id = o.BuyerCustomerParty_Id
JOIN dbo.[Party] pt ON pt.Alias = cp.Alias
JOIN dbo.[Contact] ct ON ct.Alias = pt.Contact_Id
JOIN dbo.[Address] ad ON ad.Alias = pt.PostalAddress_Id
WHERE o.OrderId = @orderNumber

-- Shipping Address
SELECT 
 pt.Person_FirstName AS FirstName,
 pt.Person_FamilyName AS LastName,
 ad.AddressLine AS Address1,
 ad.AddressLine2 AS Address2,
 ad.CityName AS City,
 ad.CountrySubentity AS [State],
 ad.PostalZone AS PostalCode,
 ad.Country AS Country,
 ct.Telephone
FROM dbo.[Order] o
JOIN dbo.[Delivery] dv ON dv.Order_Id = o.ID
JOIN dbo.[Party] pt ON pt.Alias = dv.DeliveryParty_Id
JOIN dbo.[Contact] ct ON ct.Alias = pt.Contact_Id
JOIN dbo.[Address] ad ON ad.Alias = pt.PostalAddress_Id
WHERE o.OrderId = @orderNumber

-- Shipping Provider
SELECT 
 pt.PartyIdentification AS Code,
 pt.PartyName AS Title,
 pt.EndpointID AS ServiceCode
FROM dbo.[Order] o
JOIN dbo.[Party] pt ON pt.Order_Id = o.ID
WHERE o.OrderId = @orderNumber

-- Payments
SELECT p.PaymentID,
 p.PaymentMeansCode AS PaymentSystem_Code,
 p.PaymentMeansTitle AS PaymentSystem_Title,
 p.PaymentStatus,
 p.PaymentAmount_Value AS Amount,
 p.PaymentAmount_CurrencyID AS Currency,
 p.PaymentDescription,
 p.TransactionNumber,
 p.ProviderStatus,
 p.ProviderMessage,
 p.ProviderErrorCode,
 c.*,
 r.*
 FROM [dbo].[Order] o
 join [dbo].[PaymentMeans] p on o.ID = p.Order_Id
 left join [dbo].[CreditCardPayment] c on c.PaymentMeans_Id = p.Alias
 left join [dbo].[ReservationTicket] r on p.Alias = r.Alias
WHERE o.OrderId = @orderNumber