How to make Compare Report Sold, Gift, Void

V5 Question
2

You can use Pivot Table for that but you’ll need SQL to be able to bring data to the shape you need. SQL can also help us to pivot our data. I prepared a sample for you to give an idea.

Pasted image2088×1208 163 KB

This is full custom report. You can modify that to fit to your needs.

[Report:3, 1, 1, 1]
>Name|Sold|Gift|Void
@{REPORT SQL DETAILS:Select distinct GroupCode as GroupCode from MenuItems:F.GroupCode::,}
>>$1
{REPORT SQL DETAILS:
Select GroupCode,MenuItemName,isnull(Sold,0) as Sold,isnull(Gift,0) as Gift,isnull(Void,0) as Void from
(select 
    M.GroupCode as GroupCode,
    O.MenuItemName as MenuItemName,
    O.Quantity as Quantity,
	CASE
     WHEN CalculatePrice = 0 and DecreaseInventory = 1 THEN 'Gift'
	 WHEN DecreaseInventory = 0 and CalculatePrice = 0 THEN 'Void'
	 Else 'Sold'
	End as OrderType
  from Orders as O
  inner join MenuItems as M on M.Id = O.MenuItemId 
  where GroupCode = '$1') as DataTable
  
  PIVOT(
    Sum(Quantity)
	For OrderType 
	IN ([Sold],[Gift],[Void])
  ) as PivotTable
  
  :
  F.MenuItemName,F.Sold;0;-0;-,F.Gift;0;-0;-,F.Void;0;-0;-
}
5 Likes