REPORT SQL DETAILS to create a Simple Menu Price List

Hello all back form Melbourne after spending some time with my team training in what I know in SambaPOS so far. Of course during training I get “How do I print a product price list?”

So funny thing, there is not a lot on this topic so I used it as a training exercise using the concepts in the link below. To conclude this I have some questions at the end to wrap up.

From the discussion from How do I print a product list?:

#{REPORT SQL DETAILS} report tag
So we decided to use the {REPORT SQL DETAILS:<sql or handler>:<fields>:<expression>:<line format>} as it is a simple implementation to include all the SQL syntax as well. The Report will print MENU ITEM PRICES which is useful for simple POS installations not utilizing full Inventory Control.

NOTE: These Steps are for a intermediate level User, but if you trying the SQL Printer Tag then it is assumed some more than basic knowledge is known. Therefor Steps to find SQL Server Studio and Create a New Samba Report have been omitted.

STEP 1: LAUNCH SQL Server 2014 Management studio and create a new query.
Use the handy “Design Query in Editor…” as it will assist in building the T-SQL!

STEP 2: Pick the Tables you wish to use.
In this case I wanted the MenuItems & MenuItemPrices.
TIP: You can use the “Object Explorer” - Left most window above to right click and “Select Top nn Rows” to see actually what is in the Table. WARNING: Don’t going playing with your data as you can mess things up!

Picking 2 Tables will enforce the join rule.

STEP 3: Pick the Fields you wish to use.
Click the checkboxes of the fields you want, in this case MenuItems.GroupCode, MenuItems.Name, &MenuItemPrices.Price.

STEP 4: Try it!.
If your happy with the result then copy & paste the SQL syntax generated into the SambaPOS Report your are building.

STEP 5: Apply the SQL into a SambPOS Report using {REPORT SQL DETAILS}

RESULT:

[Prices:1,2,1]

{REPORT SQL DETAILS:
  	SELECT
  	MenuItems.GroupCode, 
  	MenuItems.Name, 
  	MenuItemPrices.Price
	FROM MenuItems INNER JOIN MenuItemPrices ON MenuItems.Id = MenuItemPrices.Id
	:F.GroupCode,F.Name,F.Price::{0} |{1}|${2}
}

QUESTIONS
I hope your not grimacing :grimacing: over home many databases are going to be blown up @emre but I have a few questions please:

1. Where is the reference to "F." usage and why do we use it in the {REPORT SQL DETAILS} command?

2. How come the "MenuItems.GroupCode as Group" does not work? I also have substituted the “F.GroupCode” for “F.Group” when trying the “AS” syntax.

3. Is it more efficient to do simple SQL Calls like this instead of using a Handler as the original reference post above?

4. With the syntax ":<fields>:<expression>" can you always omit this and just use the SQL WHERE clause?

5. If I wanted to Filter the result by “GroupCode” can I somehow incorporate an “ASK QUESTION” in a REPORT TEMPLATE :open_mouth:. The only other method I have seen is a lot more work such as “Q”'s post beow:

http://forum.sambapos.com/t/request-fulfilled-report-viewer-widget-date-range-filtering-printing/4430?u=pauln

Emre you may wish to edit my mistakes or use as a Tutorial if you feel appropriate. Using SQL Management Studio, however, is not really for begginers or non-IT users.

REQUIREMENTS:
SQL Express 2014 (2012 should work)
SQL Server Management Studio (selected on install)

3 Likes

The F. simply means “Field”. The reference to why it is used was in a private Staff discussion.

The word “Group” is a reserved keyword in SQL (as in GROUP BY). There are many other keywords that you will come across as well (i.e. [Date]). This is why you often see people (very often me) use the following instead, since it then ignores the [keyword] being used:

[MenuItems].[GroupCode] as [Group]

This then works:

[Prices:2,2, 1]

{REPORT SQL DETAILS:
  	SELECT
  	MenuItems.GroupCode as [Group], 
  	MenuItems.Name, 
  	MenuItemPrices.Price
	FROM MenuItems
        INNER JOIN MenuItemPrices ON MenuItems.Id = MenuItemPrices.Id
	ORDER BY [GroupCode], [Name]
	:F.Group,F.Name,F.Price::{0} |{1}| {2}
}

Only @emre can answer this definitively but I would expect “yes” it is more efficient to use it as you have done rather than using a handler.

In many cases, the WHERE clause will suffice. But sometimes, you will need to pass a value into the SQL that is coming from within SambaPOS (i.e. {ENTITY NAME}), in which case, the expression is the simplest way to do this.

Interesting … need to look into this - it might be possible with a {CALL:X} to a script containing the dlg.Question() helper. Or we need to store a Program Setting then have the SQL query read the value and use it during execution.

4 Likes

Very informative post @QMcKay - in fact excellent! Really makes it worth while taking the time documenting as this should lay the foundation for many others seeking to understand the new SQL Tag - much appreciated :smiley:

Had to put together a report to easily print a price list for reference when deciding April price increases.

I couldnt find any documentation on using the {REPORT PORTION DETAILS:x} which I expected would be required for multi portion products and nothing came up in the report helper.

Anyway working on from the SQL report above put together this if of any use to anyone;

[Price List:5 ,3,3, 1, 2]

>Product | Portion | Group | Band | Price

{REPORT SQL DETAILS:

    SELECT

        product.[GroupCode] AS [Group],

        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]

    ORDER BY product.[GroupCode]

    :F.Product,F.Portion,F.Group,F.Price Band,F.Price::{0} |{1}| {2}| {3}| £{4}

}

1 Like

I would have liked to have grouped them into GroupCode tables and not have group code in the columns like I did on my improved item sales report but couldnt work out how to use the $1 from a @ on 1st line when using {REPORT SQL}

Nice Work JTR’ - I thought you just use the $1 on the next line (or inside) the [Table Format] line then you can reference it in the TAG.

It can be passed or listed as @SomeID, ID2, ID3 - but I reckon you know far more than me :smile:

That would be is using the @@SQL to SQL script but wouldn’t work that way in a REPORT SQL DETAILS tag.
Am happy with that for now, have got the report printer in the office on an A4 laser so plenty of width vs using a ticket printer.

@pauln, it doesn’t work that way when it comes to SQL. And JTRTech, it does not matter if it is a SQL Statement or a @@SQLHandler - they both work the same way.

A @parameterList is a comma-separated set of values. This does not translate into SQL in the same way as it does with other Report Tags.

The actual full syntax for this Report Tag is:

{REPORT SQL DETAILS:<handler or sql>;<parameters,1,2>:<fields>:<expressions>:<format>}
                                    ^
this is NOT a typo - this IS a semi-colon (;), while the rest are colons (:)

Breakdown:

  • <handler or SQL> - this can be a SQL Statement or it can be a @@SQLHandler for a Script

  • ;<parameters> - values listed here will be passed into the SQL Statement and be held in variables like @1, @2, @3, etc. NOTE: this field is the only one preceded by a semi-colon (;) and it is optional so it can be omitted in the syntax.

  • :<fields> - the field names from the SQL Statement, in the format F.FieldName1, F.FieldName2, F.FieldName3

  • :<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.

  • :<format> - used to format the output of the Fields. Usually takes this form: {0}|{1}|{2}

Example:

{REPORT SQL DETAILS:SELECT '1' as [Field1]:F.Field1::{0}}

{REPORT SQL DETAILS:SELECT '@1' as [EntityName];{ENTITY NAME}:F.EntityName::<L00>{0}}

{REPORT SQL DETAILS:SELECT '@1' as [EntityName], '@2' as [Balance];{ENTITY NAME},{ENTITY BALANCE}:F.EntityName, F.Balance::<J00>{0}| {1}}

The last example could also be written something like this:

@John,15
{REPORT SQL DETAILS:SELECT '@1' as [EntityName], '@2' as [Balance];$1,$2:F.EntityName, F.Balance::<J00>{0}| {1}}

But the following will not work as you might expect when it comes to SQL:

@{TICKET TYPE LIST}
{REPORT SQL DETAILS:SELECT '@1' as [TicketType];$1:F.TicketType::{0}}

So here is the problem: lets say we have a @parameterList defined at the beginning of the Report, such as:

@{TICKET TYPE LIST}

This translates to: TicketType1,TicketType2,TicketType3. These values can then be used in $variables.

This works well, when used within an <expression>, because it will produce output for each Ticket Type in sequence, for example:

$1|{REPORT TICKET COUNT:(TY=$1)}|{REPORT TICKET TOTAL:(TY=$1)}

That will produce a row of data for each Ticket Type. But notice how the $1 is used in the <expression> portion of the Report Tag. It will evaluate and produce output like this:

TicketType1|{REPORT TICKET COUNT:(TY=TicketType1)}|{REPORT TICKET TOTAL:(TY=TicketType1)}
TicketType2|{REPORT TICKET COUNT:(TY=TicketType2)}|{REPORT TICKET TOTAL:(TY=TicketType2)}
TicketType3|{REPORT TICKET COUNT:(TY=TicketType3)}|{REPORT TICKET TOTAL:(TY=TicketType3)}

When it comes to feeding $variables into SQL Statements or a @@SQLHandler, it works differently. When we feed a @parameterList into SQL, it is evaluated as a single $1 variable, and that variable contains the entire list.

So in this case, @{TICKET TYPE LIST} is fed into the SQL as:

@@SQLHandler:$1

Then inside the SQL we read $varables as @variable, so it evaluates like this:

@1 (from $1 which contains= `TicketType1,TicketType2,TicketType3`)

So you can see, it does not “cycle” through the @parameterList replacing $1 with each Ticket Type in succession as it does with other Report Tags. Instead, it assigns a single $variable of $1 containing the entire @parameterList.


On the other hand, if you use a typed list instead of a @parameterList, for example:

@@SQLHandler:TicketType1,TicketType2,TicketType3

It comes out like this:

$1 (evaluates to TicketType1)
$2 (evaluates to TicketType2)
$3 (evaluates to TicketType3)

In SQL, we read these as @variables which are assigned in the order they are given. So in this case, within the SQL, we can read 3 values:

@1 (from $1 which contains TicketType1)
@2 (from $2 which contains TicketType2)
@3 (from $3 which contains TicketType3)

So you can see, it does not “cycle” through the @parameterList replacing $1 with each Ticket Type in succession as it does with other Report Tags. Instead, it assigns 3 unique $variables of $1,$2,$3 each containing a single Ticket Type.

6 Likes

@QMcKay if we can use expressions could be not do column = expression…
So as an example from the origional tag I posted put this for the last line;

:F.Product,F.Portion,F.Group,F.Price Band,F.Price:(F.Group=$1):{0} |{1}| {2}| {3}| £{4}

This would as I see it then run the REPORT SQL tag for each value of $1 filtering each run to F.Group=$1 each time?

I have not tried yet as not been on that system today and not got a backup at home.

I tried that - it does not work.

As I mentioned, I do not think REPORT SQL DETAILS Tag supports expressions. It is part of the syntax simply as a placeholder so that it conforms to a common syntax layout used by other Report Tags.

EDIT: it does seem to support the expression field. As shown, I have a simple expression (1=1) which does not affect the output at all. But as soon as I try to put anything else in the expression, no results are returned, because it does not seem to know how to evaluate anything else. That is, these do not work:

(F.GC=$1)  // I don't expect this to work, since $1 contains 'AccountingAlcohol BeerAlcohol Highball ...'
(F.GC=Merch)  // 'Merch' is one of my GroupCodes
(F.GC==Merch)
(=[F.GC]=Merch)
(=[F.GC]==Merch)
(=[F.GC]='Merch')
(=[F.GC]=='Merch')

Notice how the ‘Band’ Column is being fed $1, which is assigned to @1 in the SQL, and $1 actually contains the entire GroupCode list. This is what I was trying to explain in my previous post.

1 Like

I got it to work with a very small tweak!

@JTRTech The elusive secret mystery of the terminating comma has finally been revealed!

[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 1=1   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}
}

The Trick is the comma in the list (at the end of the first line in the format section). Once I realized that the “list” was not comma-separated as I thought, I decided to try the comma at the end of the format output. VOILA! It works!

@JTRTech The elusive secret mystery of the terminating comma has finally been revealed!

The only other thing I did was add a WHERE clause in the SQL, which I had tried before, but without the COMMA, we were not working with list items, we were working with concatenated GroupCodes.

3 Likes

Nice one Q. Thanks, will amend tomorrow.

One thing I was stuck on was trying to work out how to have it so that the price ‘bands’/tags were listed in single row rather than extra rows.

Will struggle to demonstratit as on phone but the query I posted would list;

Draught - Carling - Print - (blank = normal price) - 3.85
Draught - Carling - Print - REG - 3.75

But would be cleaner to list as;
Group - Product - Portion - Normal Price - Regular Price
Draught - Carling - Print - 3.85 - 3.75

Does that make sence?
I would invission some form of constraint/where on the select/column but not sure if that is Posible.

Google demonstrated use of CASE WHEN on the selects but never used that expression

hello JTR

If i want to link inventory items in this report in order to get cost price, how can i get it?
I tried to do it but only 1 error m getting, can you please look into it?

SELECT
product.Id,
product.[GroupCode] AS [Group],
product.[Name] AS [Product],
portion.[Name] AS [Portion], price.[Price] AS [Price], MenuItemId,
InventoryItems.DefaultBaseUnitCost
FROM [MenuItems] product, InventoryItems
LEFT JOIN [MenuItemPortions] portion
ON product.[Id] = portion.[MenuItemId]
LEFT JOIN [MenuItemPrices] price
ON price.[MenuItemPortionId] = portion.[Id]
where product.Name = InventoryItems.Name
ORDER BY product.[GroupCode]

Guessing you have multiple portions on some products?

Yes, your report is working good nd its giving me all different portions but when i edit ur report to show inventory cost its just giving a error.

That just doesn’t look right to me, not a sql pro so strugling to pick out issue.
Have you just taken someone elses dql and crudely tried to force inventory items in.
Doesnt look like you have joined it, just tried to aadd the table, and add a where.
where product.Name = InventoryItems.Name doesnt make sense like that.
So your trying to say join menuitem name and price where menuitem name = inventory item name? Does that sound logical to you?