In fact, as I write the topic heading I realise this is something that would probably sort of impossible…
But what if we could have a drop down box of all the products, then select it and it gives us a list of the top purchasers of that item?
For example, Steve… my friend who has his 3 till system, wants to know who the top buyers of slushies are… etc.
Is there a way to do this?
Matt
Yes we could do this. It would not be easy and would require skillsets to design. We could get a basic one working fairly easy though.
Sorry, I always seem to be asking complex questions…
It won’t be that difficult.
The first step is to design the Report. Use a hard-coded value for the Product to start.
Then you create an Entity Screen with a Report Viewer Widget on it, and to start, an Editor Widget to type in the name of the Product which will be used as a Parameter to the Report.
Get that far first, then we can figure out how to go about making a pick-list (drop-box) instead.
If you’re willing, doing this in GQL and some JS in a HTML Viewer Widget would give you loads of flexibility.
day off tomorrow, plan on doing lots of stuff on the tills at home.
Will get what you said above done first then will be back
Very interesting and cool - I will watch this space if working examples start to get built, good way to get into GQL.
Well @GreatShakesBar , I can already tell you the top buyer of products with my system 
Say I wanted to see who buys the most Chocolate Milkshakes I can just go.
SELECT `Student Name` , COUNT( * ) AS '# of Choc MS' FROM `Orders` WHERE `MenuItemName` = 'Chocolate Milkshake' GROUP BY `Student Name` ORDER BY `# of Choc MS` DESC And get this result which I can graph using JavaScript
I’ve blurred last names and staff orders are for you guessed it staff orders
good job, thats within SQL though isnt it?
If this was a system for a client, it would have to be a bit more user friendly i think? unless I’m missing something.
Thanks, its exactly whats needed, just in a different format?
Matt
You can try and put similar syntax into the {REPORT SQL DETAILS:x} tag. An example by Emre some time ago:
[Top 5 Orders:3, 1]
>Item Name|Total
{REPORT SQL DETAILS:
SELECT Top 5 MenuItemName,Sum(Price*Quantity) as Amount FROM ORDERS
Where CreatedDateTime > '{Start}' and CreatedDateTime < '{End}'
Group by MenuItemName
Order by Amount desc:F.MenuItemName,F.Amount::{0} |${1}}
You can also use the {REPORT ORDER DETAILS:x}
{REPORT ORDER DETAILS:EC.Customer.Full Name,O.ExactTotal.sum.desc:(ODI=True) && (TCET=Customers):{0}|${1}}
Remember EC.Customer.Full Name is a Customer Field of Entity called Customer.
Your trick is to feed it a MenuItem parameter and that’s why you were referred to a Report Widget as they can pass parameters to the Syntax.
SQL is always my first go-to for things, but if this can be done using regular Report Tags (not SQL DETAILS), that is generally encouraged because it teaches people how to use built-in features, so I was waiting for someone to come up with this instead…
So now get it work with a parameter for the Product. Start by using a @list with a few products (or 1 product), and make reference to that list with $parm (ie. $1) in the expression/constraint/filter section. It might be like this (not tested) …
@Bagel,Croissant
{REPORT ORDER DETAILS:EC.Customer.Full Name,O.ExactTotal.sum.desc:(ODI=True) && (TCET=Customers) AND O.Name=$1:{0}|{1}}
didn’t mnage to get into it today, as you all know a day off is never a day off in business…
Have a few errors tomorrow but will try squeeze it in.Failing that ill have to wait till I’m back from Cornwall next week.
Thank for the help though, i actually understand the above
Matt
Thats from my website SambaAdmin, I’ve made everything so I can get really easy reporting on a website. PM me if you want some more info on my system, I think you once said you were going to play around with it
yeah i did, never got chance with summer holidays.
Ill get back into it again when were quietened down a bit at work. If its gonna be as easy as that, then ill defy do it for all POS systems I have in place.
Matt