Stock take report by cashier

[voltamp123]

ZIP.zip (82.1 KB)

hello
I would like to know if there is a way to have a Stock Take Report by Ingredient and by Cashier.
For example, attached is the report we use end of the day for each departments stock take. However, it is a consolidated report for the department, and does not separate the sales by cashier, which would be ideal scenario i’m looking for.

We have a second report which is ITEM SALES BY CASHIER, but it only tracks products, which is only helpful upto that point.

Kindly assist

[josephrussell]

So you just want the same report grouped by user? Can you provide the report details and we can modify from there?

[voltamp123]

Hi Joseph
Yes, we would like the Report to be grouped by Cashier
Please see attached the ZIP FILE which contains the report snapshot as we as the corresponding Script file.
Many thanks and looking forward to your solution

ZIP.zip (82.1 KB)

[josephrussell]

Can you just post up your report? You’ll need to do something like this:

Custom Report Group by User

I have void report like this [Voids Detail:1,1,1,1,1.8, 0.7, 1,1,1,0.6] >User|Ticket|Date|Time|Product|Quantity|Total|Reason|Department {REPORT ORDER DETAILS:OSU.GStatus,T.TicketNumber,OSD.GStatus,OST.GStatus,O.MenuItemName,O.Quantity,O.TotalPrice,OSV.GStatus,O.Department:(OS.GStatus=Void)} How to Group report byUser / OSU.GStatus?

[voltamp123]

The Below is my End of Day Stock Take Report - Consolidated across all cashiers and tracks all inventory items

[Grills Stock Take Report:2,2, 1, 1, 1, 1, 1, 1, 1, 1,1]
>>WP Ended On|Item|Op Stock|Sold|Received|Expected|Count|Variance|U-Cost|Var-Value
{REPORT CONSUMPTION DETAILS:C.EndDate,C.Name.asc,C.InStock,C.Consumption,C.Added,C.Prediction,C.PhysicalInventory;#0.000,[($7-$6)];#0.000,C.Cost;$0.00,[(($7-$6)$9)];$0.00:(PCW=Grills) }*

The Below is my Item Sales by Cashier Report - Which only tracks Products

[Item Sales By User:5, 2, 2,1]
@{REPORT ORDER DETAILS:O.User.asc:(ODI=True):,}
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True) and (OU=$1):{0}:,}

$1
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and (OU=$1) and O.MenuItemName=“$2”}
{REPORT ORDER DETAILS:’ '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and (OU=$1) and O.MenuItemName=“$2”:2}
Totals|{REPORT ORDER DETAILS:O.Quantity.Sum,O.ExactTotal.Sum}

The objective is to have a sort of combined report of the above, whereby we have a Stock take Report by Cashier, which tracks inventory items as well.
This way, at the end of every shift (which corresponds to a change in cashier), each cashiers cashup report corresponds with their own stock take by inventory item.
This would make it extremely easy and quick to track any stock variances by cashier.

I look forward to your kind feedback

Regards

[josephrussell]

I don’t run warehouses for consumption, but you could try filtering the consumption report based on user (if thats an option). Something like:

@{REPORT ORDER DETAILS:O.User.asc:(ODI=True):,}
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True) and (OU=$1):{0}:,}
[Sales Report for $1:1,1,1]
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and (OU=$1) and O.MenuItemName="$2"}
{REPORT ORDER DETAILS:' '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and (OU=$1) and O.MenuItemName="$2":2}
[Stock Take Report for $1:2,2, 1, 1, 1, 1, 1, 1, 1, 1,1]
{REPORT CONSUMPTION DETAILS:C.EndDate,C.Name.asc,C.InStock,C.Consumption,C.Added,C.Prediction,C.PhysicalInventory;#0.000,[($7-$6)];#0.000,C.Cost;$0.00,[(($7-$6)$9)];$0.00:(PCW=Grills) and (OU=$1)}*

If that works you only need to play with your formatting.

[voltamp123]

Tried this report
however it does not track the sales by Inventory Item.
Its still tracking by product

any alternative?

[voltamp123]

As Seen, the Item Sales by Cashier only tracks the PRODUCTS
And the Stock Track Report Tracks the Inventory Items…but this report is a consolidated report of all the cashiers combined.
I want this same report but by cashier; not consolidated

[Jesse]

There is not a relation for that. Users sell products and inventory uses recipes to tack inv. You would need to do custom sql to determine the recipe for product and then use sales of product to determine inv and filter that.

[josephrussell]

Thought that might be the case. I think I’ll setup a new db for playing with inventory/recipes. Its just not something we do, so questions on inventory consumption always elude me.

[voltamp123]

Could I get assistance from you for a custom sql as it’s a very important report for me and I’m sure it would help alot of others also

[josephrussell]

Most people in the forum are happy to help you build out reports, but you should first give it a try. This is the only way to learn. You can start by looking at examples or tutorials and go from there.You’ll want to have a database management tool in order to look at your DB as you’re building your report. For SQL most everyone is using SQL Server Management Studio (SMMS - https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15).

Here is the knowledge base tutorial on SQL Reports:

https://kb.sambapos.com/en/9-1-2-creating-custom-reports-with-sql/

One thing it doesn’t reference is in-line sql querys which allows you to create a SQL report without using the scripts section such as {REPORT SQL DETAILS:;<parameters,1,2>:::}.

I would add that the SQL Query Editor is really helpful in building your query visually.

Helps you join tables by dragging one column name to the related one. Then click the checkbox to select what columns you want to receive in the query. See below:

[voltamp123]

Thanks for the informative feedback.
Id REALLY REALLY appreciate if someone could do a report for me.

Please let me know?