REPORT SQL DETAILS adding expressions and pivots

[pauln]

Continuing the discussion from REPORT SQL DETAILS to create a Simple Menu Price List:

Hey @QMcKay - I was trying to add expressions into this syntax but could only get it to work as part of the SQL? I wanted to remove all “0” prices (items with no entries for Price Tags). My Your version below modified by changing the WHERE clause:

[Price List:5 ,3,3, 1, 2]
@{REPORT SQL DETAILS:SELECT DISTINCT [GroupCode] FROM [MenuItems] ORDER BY [GroupCode]:F.GroupCode::{0},}
>Product | Portion | Band | Price
$1 ==============
{REPORT SQL DETAILS:
    SELECT
        product.[GroupCode] AS [GC],
        product.[Name] AS [Product],
        portion.[Name] AS [Portion],
        price.[PriceTag] AS [Price Band],
        price.[Price] AS [Price]
    FROM [MenuItems] product 
    LEFT JOIN [MenuItemPortions] portion ON product.[Id] = portion.[MenuItemId] 
    LEFT JOIN [MenuItemPrices] price ON price.[MenuItemPortionId] = portion.[Id]
    WHERE price.Price <> 0 and product.GroupCode='@1'
    ORDER BY product.[GroupCode], product.[Name], price.[Price];$1
    :F.Product,F.Portion,F.Price Band,F.Price::{0} |{1}| {2}| {3}
}

I thought after F.Price: I could include F.Price:(F.Price<>0): ?

Then while filtering out Unused Price Tags then maybe pass a 2nd parameter to:

1. Only Print report for a GroupCode i.e. Drinks
2. Only Print report for a Price Tag

Using your example I gues I could as you say “feed” in "@2" & "@3" after the initial ;$1. But I just want to know if I can use a traditional expression after the fields statement?

Oh dear me from the same thread!

@QMcKay

:<expressions> - normally used with other Report Tags, for example (TY=Delivery) which would match ‘Delivery’ Ticket Types. This is a good place to use $1 from a preceding @parameterList, for example (TY=$1). AFAIK, the <expressions> field is not valid for the {REPORT SQL DETAILS:X} Tag, and is always left empty.

DUH!
I know I could sav embarrassment and not post that BUT it was such a great original reply it worth a Bump

[pauln]

Answer Example:

[Price List:5 ,3,3, 1, 2]
@{REPORT SQL DETAILS:SELECT DISTINCT [GroupCode] FROM [MenuItems] ORDER BY [GroupCode]:F.GroupCode::{0},}
@BA Ciders
>Product | Portion | Band | Price
$1 ==============
{REPORT SQL DETAILS:
    SELECT
        product.[GroupCode] AS [GC],
        product.[Name] AS [Product],
        portion.[Name] AS [Portion],
        price.[PriceTag] AS [Price Band],
        price.[Price] AS [Price]
    FROM [MenuItems] product 
    LEFT JOIN [MenuItemPortions] portion ON product.[Id] = portion.[MenuItemId] 
    LEFT JOIN [MenuItemPrices] price ON price.[MenuItemPortionId] = portion.[Id]
    WHERE price.Price <> 0 and product.GroupCode='@1' and product.GroupCode='@2'
    ORDER BY product.[GroupCode], product.[Name], price.[Price];$1,$2
    :F.Product,F.Portion,F.Price Band,F.Price::{0} |{1}| {2}| {3}
}

Output:

Now the question @emre how do I pivot this report around to show:

Product Portion    | Price Staff  | Price Happy Hour   | Price Normal
Bulmers MIDDY      |       $1.72  |        $3.90       |      $5.00

I would have to include all price tags include unused tags for products.
How to I substitute “Regular” in for Normal Price as it has no Band Name?

[JTRTech]

Wow, staff price 1.72 down from 5.00, can I come work for you

[JTRTech]

If this just price list not sales right?

I think you would have to have multiple report expressions per line…

[JTRTech]

PS. That report looks very familiar

[JTRTech]

Am getting there;

SELECT
	product.[GroupCode] AS 'Group',
	product.[Name] AS 'Product',
	portion.[Name] AS 'Portion',
	(SELECT price.[Price] WHERE price.[PriceTag] is null) Normal,
	(SELECT price.[Price] WHERE price.[PriceTag] = 'REG') REG
FROM [MenuItems] product 
LEFT JOIN [MenuItemPortions] portion 
ON product.[Id] = portion.[MenuItemId] 
LEFT JOIN [MenuItemPrices] price
ON price.[MenuItemPortionId] = portion.[Id]
ORDER BY product.[GroupCode]

Q will be the best person probably for this, hes the SQL buff.

[JTRTech]

THINK I HAVE IT

 SELECT
	product.[GroupCode] AS 'Group',
	product.[Name] AS 'Product',
	portion.[Name] AS 'Portion',
	MAX(CASE WHEN price.[PriceTag] is null THEN price.[Price] END) Normal,
	MAX(CASE WHEN price.[PriceTag] = 'REG' THEN price.[Price] END) REG
FROM [MenuItems] product 
INNER JOIN [MenuItemPortions] portion 
ON product.[Id] = portion.[MenuItemId] 
INNER JOIN [MenuItemPrices] price
ON price.[MenuItemPortionId] = portion.[Id]
GROUP BY product.[GroupCode],product.[Name],portion.[Name]

@QMcKay any thought for improvement?

[QMcKay]

Nope, because I don’t know what you’re trying to do

Maybe use isnull([field],'replacement') ?

Why are you using MAX()? What is that for? Do you really want that?

[JTRTech]

Not really, was in the sample on site i read and couldnt find an alternative which didnt break it LOL

Aim is to have price tags listed as columns rather than row per tag

[JTRTech]

Believe it is to escape the group by…

Need a quick hand… almost there…

This works;

[Price List:3 ,2, 1, 1]
@{REPORT SQL DETAILS:SELECT DISTINCT [GroupCode] FROM [MenuItems] ORDER BY [GroupCode]:F.GroupCode::{0},}
>Product | Portion | Band | Price
{REPORT SQL DETAILS:
 SELECT
	product.[Name] AS 'Product',
	portion.[Name] AS 'Portion',
	MAX(CASE WHEN price.[PriceTag] is null THEN price.[Price] END) Normal,
	MAX(CASE WHEN price.[PriceTag] = 'REG' THEN price.[Price] END) REG
FROM [MenuItems] product 
INNER JOIN [MenuItemPortions] portion 
ON product.[Id] = portion.[MenuItemId] 
INNER JOIN [MenuItemPrices] price
ON price.[MenuItemPortionId] = portion.[Id]
WHERE product.[GroupCode] = 'Draught'
GROUP BY product.[GroupCode],product.[Name],portion.[Name]
ORDER BY product.[GroupCode],product.[Name],portion.[Name]
	:F.Product,F.Portion,F.Normal,F.REG::{0} |{1}| £{2}| £{3}
}

But would like to ad GroupCode header like @pauln but seems to break…

 [Price List:3 ,2, 1, 1]
@{REPORT SQL DETAILS:SELECT DISTINCT [GroupCode] FROM [MenuItems] ORDER BY [GroupCode]:F.GroupCode::{0},}
>Product | Portion | Band | Price
$1 ========
{REPORT SQL DETAILS:
 SELECT
	product.[Name] AS 'Product',
	portion.[Name] AS 'Portion',
	MAX(CASE WHEN price.[PriceTag] is null THEN price.[Price] END) Normal,
	MAX(CASE WHEN price.[PriceTag] = 'REG' THEN price.[Price] END) REG
FROM [MenuItems] product 
INNER JOIN [MenuItemPortions] portion 
ON product.[Id] = portion.[MenuItemId] 
INNER JOIN [MenuItemPrices] price
ON price.[MenuItemPortionId] = portion.[Id]
WHERE product.[GroupCode] = '@1'
GROUP BY product.[GroupCode],product.[Name],portion.[Name]
ORDER BY product.[GroupCode],product.[Name],portion.[Name];$1
	:F.Product,F.Portion,F.Normal,F.REG::{0} |{1}| £{2}| £{3}
}

[pauln]

@JTRTech

PS. That report looks very familiar

Yea might of been wearing balaclava at the time of reviewing your code…

I realized my first example did not support portions and price tags, not to mention it was selecting random prices! I won’t worry too much about the Pivoting of the price bands as have too much other stuff to try and solve, thanks for all your input.

[JTRTech]

The solution is there only thing I couldnt work out was dividing groupcodes into sections but is sorted by group/product/portion which is 99% there.
Just need to change headers and add extra lines for your bands.