Trying to find a way to display how many of a product has been sold

jason jorgenson asked on March 9, 2016 19:47

I am new to kentico and am trying to find something that would display the amount sold of a certain product in a transformation.

Correct Answer

Bryan Soltis answered on March 9, 2016 22:56

Hi Jason,

With transformations, you only have certain data available to you. While you would have product data (assuming you are working a Page Type that is a product), getting the sales of that product would be a pretty abstract concept, requiring a few API calls to calculate. For this reason, you would probably want to make a custom macro to get the values, then call that macro within your transformation.

You can find out more about custom macros here:

I also recently published a blog on creating a custom macro (in this case a Loren Ipsum generator), however, the steps would be the same for you.

Transformations do have a number of built-in functions you can call. You can find a list of them here:

Lastly, you may be able to get some inspiration from the Ecommerce reports, specifically the Reporting/E-commerce/Products/Top product by number of purchases report. This report uses SQL calls to calucalte the number of times a product has been bought. You could use this SQL as a guide if you wanted to have your own SQL call in your custom macro.

/* Variable determines whether global products should be displayed. */
DECLARE @GlobalProducts bit;
SET @GlobalProducts = {%Settings.CMSStoreAllowGlobalProducts?1:0|(identity)GlobalAdministrator%};

/* Selects the product meeting the conditions from the filter and 
   the number of orders in which it occures */
SELECT TOP (@Limit) 
   ISNULL(parent.SKUName + ': ', '') + product.SKUName + ISNULL(' (' + COM_OptionCategory.CategoryDisplayName + ')', '') AS '{$product_list.productname$}',
   product.SKUNumber AS '{$product_list.productnumber$}',
   ISNULL(Departments.DepartmentDisplayName, parentDepartment.DepartmentDisplayName) AS '{$objecttype.com_department$}',
   ISNULL(Manufacturers.ManufacturerDisplayName,parentManufacturer.ManufacturerDisplayName) AS '{$objecttype.com_manufacturer$}',
   ISNULL(Suppliers.SupplierDisplayName,parentSupplier.SupplierDisplayName) AS '{$objecttype.com_supplier$}',
   CAST(product.SKUPrice AS DECIMAL(38,2)) AS '{$com.inventory.price$}',
   ISNULL(COUNT(DISTINCT OrderID),0) AS '{$com.purchasedproductsreport.numberofpurchases$}'             
   COM_SKU product
   LEFT JOIN COM_OrderItem
      ON product.SKUID = OrderItemSKUID      
      ON OrderID = OrderItemOrderID  
      AND (ISNULL(OrderIsPaid,0) = 1 OR ISNULL(@OrderIsPaid,0) = 0)
      AND (OrderDate >= @FromDate OR @FromDate IS NULL)
      AND (OrderDate <= DATEADD(day,1,@ToDate) OR @ToDate IS NULL)
      AND (product.SKUOptionCategoryID IS NULL OR (((@GlobalOptions = 1 AND product.SKUSiteID IS NULL) OR product.SKUSiteID = @CMSContextCurrentSiteID)))
      AND (product.SKUOptionCategoryID IS NOT NULL OR ((@GlobalProducts = 1 AND product.SKUSiteID IS NULL) OR product.SKUSiteID = @CMSContextCurrentSiteID))
   LEFT JOIN COM_Department AS Departments 
      ON Departments.DepartmentID = product.SKUDepartmentID
   LEFT JOIN COM_Manufacturer AS Manufacturers
      ON product.SKUManufacturerID = Manufacturers.ManufacturerID
   LEFT JOIN COM_Supplier AS Suppliers
      ON Suppliers.SupplierID = product.SKUSupplierID
   LEFT JOIN COM_OptionCategory 
      ON CategoryID = product.SKUOptionCategoryID
      ON product.SKUParentSKUID = parent.SKUID
   LEFT JOIN COM_Department parentDepartment 
      ON parentDepartment.DepartmentID = parent.SKUDepartmentID
   LEFT JOIN COM_Manufacturer parentManufacturer
      ON parent.SKUManufacturerID = parentManufacturer.ManufacturerID
   LEFT JOIN COM_Supplier parentSupplier
      ON parentSupplier.SupplierID = parent.SKUSupplierID
   (product.SKUOptionCategoryID IS NULL OR ((@GlobalOptions = 1 AND product.SKUSiteID IS NULL) OR product.SKUSiteID = @CMSContextCurrentSiteID))
   AND (product.SKUOptionCategoryID IS NOT NULL OR ((@GlobalProducts = 1 AND product.SKUSiteID IS NULL) OR product.SKUSiteID = @CMSContextCurrentSiteID))
   AND (product.SKUOptionCategoryID IS NULL OR (CategoryType = 'PRODUCTS'))  
   AND ((product.SKUEnabled = 1 AND (parent.SKUEnabled IS NULL OR parent.SKUEnabled = 1))
         OR (product.SKUID IN (SELECT OrderItemSKUID FROM COM_OrderItem WHERE OrderItemOrderID IN (SELECT OrderID FROM COM_Order WHERE OrderSiteID=@CMSContextCurrentSiteID))))
   AND (@DepartmentID = product.SKUDepartmentID OR @DepartmentID < 0 OR (@DepartmentID IS NULL AND product.SKUDepartmentID IS NULL))
   AND (@ManufacturerID = product.SKUManufacturerID OR @ManufacturerID < 0 OR (@ManufacturerID IS NULL AND product.SKUManufacturerID IS NULL))
   AND (@SupplierID = product.SKUSupplierID OR @SupplierID < 0 OR (@SupplierID IS NULL AND product.SKUSupplierID IS NULL))
   AND (@PublicStatusID = product.SKUPublicStatusID OR @PublicStatusID < 0 OR (@PublicStatusID IS NULL AND product.SKUPublicStatusID IS NULL))
   AND (@InternalStatusID = product.SKUInternalStatusID OR @InternalStatusID < 0 OR (@InternalStatusID IS NULL AND product.SKUInternalStatusID IS NULL))
   AND (product.SKUName LIKE ('%'+@ProductName+'%') OR @ProductName IS NULL)
   AND (product.SKUNumber LIKE ('%'+@ProductNumber+'%') OR @ProductNumber IS NULL OR @ProductNumber = '')        
   AND COM_Order.OrderSiteID = @CMSContextCurrentSiteID
   ISNULL(parent.SKUName + ': ', '') + product.SKUName + ISNULL(' (' + COM_OptionCategory.CategoryDisplayName + ')', ''),
   ISNULL(Departments.DepartmentDisplayName, parentDepartment.DepartmentDisplayName),
   ISNULL(parent.SKUName + ': ', '') + product.SKUName + ISNULL(' (' + COM_OptionCategory.CategoryDisplayName + ')', '') ASC,
   product.SKUNumber ASC
  • Bryan
0 votesVote for this answer Unmark Correct answer

   Please, sign in to be able to submit a new answer.