Top Buyer Of Products

V5 Question
1

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

2

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.

1 Like
3

Sorry, I always seem to be asking complex questions…

4

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.

2 Likes
5

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

1 Like
6

Very interesting and cool - I will watch this space if working examples start to get built, good way to get into GQL.

7

Well @GreatShakesBar , I can already tell you the top buyer of products with my system :slight_smile:
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

1 Like
8

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

9

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.

2 Likes
10

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}}
2 Likes
11

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

12

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

1 Like
13

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