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 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 . The only other method I have seen is a lot more work such as “Q”'s post beow:
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)