Products Report

V5 Question
1

Is it possible to write a report the shows products and prices but only products that are currently active?

2

Define active? In menu?

3

Yes, by active I mean in the menu :+1:

4

Never looked for menu items in samba reports but should be posible with sql if not

5 
DECLARE @Items	TABLE
(
	Menu			NVARCHAR(MAX),
	Category		NVARCHAR(MAX),
	SubCategory		NVARCHAR(MAX),
	Product			NVARCHAR(MAX),
	SortOrder		INT,
	Barcode			NVARCHAR(MAX),
	Tag				NVARCHAR(MAX),
	Portion			NVARCHAR(MAX),
	Normal			DECIMAL(16,2),
	HH				DECIMAL(16,2),
	DP				DECIMAL(16,2)
)

INSERT INTO @Items
(
	Menu,
	Category,
	SubCategory,
	Product,
	SortOrder,
	Barcode,
	Tag,
	Portion,
	Normal,
	HH,
	DP
)
(

SELECT
	SM.Name																		AS Menu,
	SMC.Name																	AS MenuCategory,
	ISNULL(SMI.SubMenuTag, '')													AS SubCategory,
	MI.Name																		AS Product,
	SMC.SortOrder							,
	ISNULL(MI.Barcode, '')														AS Barcode, 
	ISNULL(MI.Tag, '')															AS Tag,
	MIP.Name																	AS Portion,
	SUM(CASE WHEN P.[PriceTag] is null OR P.PriceTag = '' OR P.PriceTag = '[]'	THEN p.[Price] ELSE 0 END) AS Normal,
	SUM(CASE WHEN P.[PriceTag] = 'HH'											THEN p.[Price] ELSE 0 END) AS HH,
	SUM(CASE WHEN P.[PriceTag] = 'DP'											THEN p.[Price] ELSE 0 END) AS DP
FROM ScreenMenuCategories SMC
	INNER JOIN ScreenMenus SM
		ON SM.Id = SMC.ScreenMenuId
	INNER JOIN ScreenMenuItems SMI
		ON 
		SMI.ScreenMenuCategoryId = SMC.Id
	INNER JOIN MenuItems MI
		ON SMI.MenuItemId = MI.Id
	INNER JOIN MenuItemPortions MIP
		ON MIP.MenuItemId = MI.Id
	INNER JOIN MenuItemPrices P
		ON P.MenuItemPortionId = MIP.Id
WHERE
	P.PriceTag IS NULL
GROUP BY 
	SM.Name,
	SMC.Name,
	SMI.SubMenuTag,
	MI.name,
	MI.Barcode,
	MI.Tag,
	MIP.Name,
	SMC.SortOrder,
	P.Price
)


SELECT * FROM @Items
ORDER BY Category, SortOrder
1 Like
6

Wow, that is awesome!!! I’ll try this tomorrow when I go to work.

Thanks heaps for your help :+1::+1::+1:

7 
DECLARE @Items	TABLE
(
	Menu			NVARCHAR(MAX),
	Category		NVARCHAR(MAX),
	SubCategory		NVARCHAR(MAX),
	Product			NVARCHAR(MAX),
	SortOrder		INT,
	Barcode			NVARCHAR(MAX),
	Tag				NVARCHAR(MAX),
	Portion			NVARCHAR(MAX),
	Normal			DECIMAL(16,2),
	HH				DECIMAL(16,2),
	DP				DECIMAL(16,2)
)

INSERT INTO @Items
(
	Menu,
	Category,
	SubCategory,
	Product,
	SortOrder,
	Barcode,
	Tag,
	Portion,
	Normal,
	HH,
	DP
)
(

SELECT
	SM.Name																		AS Menu,
	SMC.Name																	AS MenuCategory,
	ISNULL(SMI.SubMenuTag, '')													AS SubCategory,
	MI.Name																		AS Product,
	SMC.SortOrder							,
	ISNULL(MI.Barcode, '')														AS Barcode, 
	ISNULL(MI.Tag, '')															AS Tag,
	MIP.Name																	AS Portion,
	SUM(CASE WHEN P.[PriceTag] is null OR P.PriceTag = '' OR P.PriceTag = '[]'	THEN p.[Price] ELSE 0 END) AS Normal,
	SUM(CASE WHEN P.[PriceTag] = 'HH'											THEN p.[Price] ELSE 0 END) AS HH,
	SUM(CASE WHEN P.[PriceTag] = 'DP'											THEN p.[Price] ELSE 0 END) AS DP
FROM ScreenMenuCategories SMC
	INNER JOIN ScreenMenus SM
		ON SM.Id = SMC.ScreenMenuId
	INNER JOIN ScreenMenuItems SMI
		ON 
		SMI.ScreenMenuCategoryId = SMC.Id
	INNER JOIN MenuItems MI
		ON SMI.MenuItemId = MI.Id
	INNER JOIN MenuItemPortions MIP
		ON MIP.MenuItemId = MI.Id
	INNER JOIN MenuItemPrices P
		ON P.MenuItemPortionId = MIP.Id

GROUP BY 
	SM.Name,
	SMC.Name,
	SMI.SubMenuTag,
	MI.name,
	MI.Barcode,
	MI.Tag,
	MIP.Name,
	SMC.SortOrder
)


SELECT * FROM @Items
ORDER BY Category, SortOrder

delete

WHERE
	P.PriceTag IS NULL

and

,
	P.Price
1 Like
8

Cheers, thanks for that. :+1:

1 Like
9

Hi…
You can do it with sql codes.

SQL Codes:

– in Menus
select mi.Name,mi.GroupCode,mip.Name as ‘Portion Name’,mip1.Price,mip1.PriceTag
from MenuItems mi
join MenuItemPortions mip on mi.Id=mip.MenuItemId
join MenuItemPrices mip1 on mip1.MenuItemPortionId=mip.Id
where mi.Id in (select distinct smi.MenuItemId from ScreenMenuItems smi)

–Not in Menus

select mi.Name,mi.GroupCode,mip.Name as ‘Portion Name’,mip1.Price,mip1.PriceTag
from MenuItems mi
join MenuItemPortions mip on mi.Id=mip.MenuItemId
join MenuItemPrices mip1 on mip1.MenuItemPortionId=mip.Id
where mi.Id not in (select distinct smi.MenuItemId from ScreenMenuItems smi)

10

Cheers, this worked perfectly :+1::+1::+1: