How to make Compare Report Sold, Gift, Void

Any Idea For make Report like this

 Item           | Qty Sold | Amount Sold | Qty Gift | Amount Gift| Qty Void | Amount Gift
 Group by Group Code of Item
 Chicken ====>
  Meat Ball    | 10        |  100        |  1        |   10       |  2      |  20

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.

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

How can I add code to create monthly notifications? This report captures the current data from the first date. I want to order month submission

can we display portion name in this ?

this report fetch data from wayy before the date filter parameters, how can i make the report just to show data according to the date filter.

Need a where clause with dates or {START} and {END}

2 Likes

hellow, can u give me a tip in how to implement the {START} and {END} please.

Here is an example from some SQL I wrote… its not exactly what you need but it demonstrates it.

WHERE 1=1
AND [PaymentData]!=''
AND DATE > '{Start}'
AND DATE < '{End}'
ORDER BY jsonData.[pdDateTime]
1 Like