Developer forum

Forum » Ecommerce - Standard features » Product sale report - where does data come from?

Product sale report - where does data come from?

Lars Larsen
Lars Larsen


In the Ecommerce you can get the report "Product sale" under "Statistics". Where does data for the report come from? Is it from a table in the database or is it based on a query? I would like to know because I would like to get programmatic access to the data.


Nicolai Pedersen
Nicolai Pedersen

It comes from the database. All the reports are ecommerce statistics add ins. Find all of them attached for reference. You can create your own as well.

This is the SQL from the report you refer to:

With sqlBuilder
                    .Add($"SELECT DISTINCT {top} EcomOrderLines.OrderLineProductID, MIN(EcomOrderLines.OrderLineProductName) AS ProdName, Sum(EcomOrderLines.OrderLineQuantity) AS ProductAmount ")
                    .Add("FROM EcomOrderLines ")
                    .Add("LEFT JOIN EcomProducts ON EcomProducts.ProductID = EcomOrderLines.OrderLineProductID ")
                    .Add("INNER JOIN EcomOrders ON EcomOrders.OrderID = EcomOrderLines.OrderLineOrderID ")
                    .Add("INNER JOIN EcomOrderStates ON EcomOrderStates.OrderStateID = EcomOrders.OrderStateID ")
                    .Add("WHERE ")
                    .Add("(EcomProducts.ProductVariantID = '' OR ") 'Only count main product, otherwise the productcount is multiplied with the number of variants, - LDE 28/4-08
                    .Add("EcomProducts.ProductVariantID is null) AND ") 'Sometimes this field can be null
                    If Not String.IsNullOrEmpty(Language) Then
                        .Add("EcomProducts.ProductLanguageID = {0} AND ", Language)
                    End If
                    If Not String.IsNullOrWhiteSpace(Shop) Then
                        .Add("EcomOrders.OrderShopID = {0} AND", Shop)
                    End If
                    .Add("EcomOrders.OrderComplete = {0} AND ", True)
                    .Add("EcomOrders.OrderDeleted = {0} AND ", False)
                    .Add("EcomOrderStates.OrderStateDontUseInstatistics = {0} AND ", False)
                    .Add("(EcomOrders.OrderCompletedDate Between {0} AND {1}) ", DateFrom, DateTo)

                    If Not String.IsNullOrEmpty(Currency) Then
                        If Currency = Application.DefaultCurrency.Code Then
                            .Add("AND (EcomOrders.OrderCurrencyCode IS NULL OR EcomOrders.OrderCurrencyCode = '' OR EcomOrders.OrderCurrencyCode = {0}) ", Currency)
                            .Add("AND EcomOrders.OrderCurrencyCode = {0} ", Currency)
                        End If
                    End If

                    .Add("GROUP BY EcomOrderLines.OrderLineProductID, EcomProducts.ProductNumber ")
                    .Add("ORDER BY ProductAmount DESC ")
                End With
Lars Larsen
Lars Larsen

Hi Nicolai

Thanks a lot smiley


You must be logged in to post in the forum