SQL Exporting Query

V5 Question
1

So, Here is the report i generated. It works well, but the problem is the total quantity it shows including the Voided Items.

Ex: If i place 6 Orders of Absolut, and i Void 1 Item, it should should show 5 as sales, But it shows 6.

Question 1: How to remove voided item from Quantity ?
Question 2: How to adjust the total sum of price minus the Gift item , right now it shows total sales * price ?
Question 3: How to show sales based on product tag ?

{REPORT SQL DETAILS:
   SELECT        
       MenuItemName,
       PortionName, 
       Price,
	   Quantity,       
       Price
   FROM Orders
   :F.MenuItemName.asc, F.PortionName, F.Price,  F.Quantity.sum, F.Price.sum
   ::{1}{2}{3}{4}{5}
}
2

Was there a perticular reason your using SQL rather than build in report expressions? Built in expresson for reporting orders would easily be able to exclude by state etc

3

I want this so that i can open and save all reports in excel.
PDF is good, but i cannot copy data to excel from it.

4

Sure you can set the trailing formatting {} bits on normal reports.
Seperate columns with commas so it’s a cav and will open in excel just just fine.

5

Any Report can be made into a Data Export, comma-separated, which when executed will create a CSV file that can be opened in Excel. Using REPORT SQL DETAILS Report Tag has nothing to do with this.

You should try to use the built-in Report Tags (not the SQL one) to get the results you want, then convert the Report to a Data Export.

The reason I recommend the built-in Report Tags in your case is because you are looking for information that is embedded in the DB as JSON strings (ie. Order State “GStatus”==“Void” or “Gift”), which is very difficult to parse using SQL, and “normal” Report Tags make access to that JSON data very easy. So you should try using {REPORT ORDER DETAILS:X} for this, and use the Constraint section of the report to filter out the GStatus States Void and Gift.

However, if there is some reason you absolutely need to use REPORT SQL DETAILS, then there is a way to access JSON data with that Tag which very few know about - here is a quick example to show how to read the JSON data in the [TicketStates] column of the [Tickets] table …

{REPORT SQL DETAILS:
select *
from Tickets:
 F.TicketStates(SN=Status)S
,F.TotalAmount.Sum
}

This is the “magic” part:

TicketStates(SN=Status)S

… what is the “S” for?

It is the “key” of the JSON value that we want to read. So in this case, it reads the value for the “S” key (state) where “SN” key (state name) == “Status” …

Returns Paid for this ticket.

image

If you need to read “SV” value, it becomes F.TicketStates(SN=Status)SV

6

Is this a feature of SambaPOS or a feature that only works if you use SQL Server 2016 ?

7

It is a sambapos feature.

8

Thank you so much for all the valuable input.
I will try testing with the suggestions you provided.