Developer forum

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)
                        Else
                            .Add("AND EcomOrders.OrderCurrencyCode = {0} ", Currency)
                        End If
                    End If

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

Hi Nicolai

Thanks a lot