# Evaluate a sum as <= 0 in report

Use case:

Staff receive commission on cigar sales. Staff may sell for less than the sale price so long as it is not less than the minimum sales price. Anything above the minimum price is paid in commission. Rarely, with approval, cigars can be sold below the minimum price, however there is no commission.

Product Tags are set for cigars for minimum price (MT.Minimum Price).

In a report for one column, I would like to do something like the following:

IF (O.Price - MT.Minimum Price) <= 0 THEN commission = 0 ELSE commission = (O.Price - MT.Minimum Price)

The above would be placed where āMATH LOGIC GOES HEREā in the following report:

``````[Commission Details:4, 1, 1, 2, 2]
@{SETTING:CURRENTUSER}
@{REPORT ORDER DETAILS:O.MenuItemName,MT.Minimum Price,O.ExactTotal.Sum.desc:(ODI=True) AND (OU=\$1)AND (MG=Cigars):{0}:,}
>|Qty|Total Commission \$
>>*** \$1 ***
``````

Iāve been playing around a bit:

`[=F(TN("{REPORT ORDER DETAILS:O.Price}") - TN("{REPORT ORDER DETAILS:MT.Miniumum Price}"))]`

The above returns the value Iām expecting. When I plug it into where I want, it returns 0.

Any help is most appreciated.

TIA

1 Like

Iāve not seen something like that done before.
What might be easier and also more logical would be to calculate this within the ticket and store as a custom order state or state value.
Then you would simply do sum of that.
Doing that math is easy in automation.
Update the state as part of your change price rule.
Either calculate a value that is the <= number and fo / for % in report or even just calculate the commission value directly and sum in report.

I thought about automation initially and disperse to staff accounts for tracking. Oh, well. Iāll keep poking around. I might see what I can do at the db level. Iāll let you know if I find something that works.

Thanks, JTR.

Anyone know to what the Tag column in Orders table correlates?
Then Samba Wiki says itās āThe tag linked to the product orderedā but nothing Iāve changed in any field for a product appears in this field. A year of production data and that field is NULL for every order. That field also doesnāt show up in reports under O.x.

Are you using any Custom Tags?

I have ticket tags that store Name and tax number for receipts. Some products have order tags. None of those tags appear in the Tag column in Orders table.

I was thinking I could use this column instead of altering the table or creating a new table to hold commission data.

If your using sql query you could probably active what you want but likely to be a quite complex query.
Using cusom state values wont be anywhere near as complex.

For now Iāve just decided to have the minimum sale amount in the āTagā field (the field under Group Code) as it isnāt stored in json (no native json support with 2014) and go with a view in lieu of triggers and modifying tables that may be overwritten in the future. Even if the view gets deleted on an update, the raw data that was aggregated will still be available.

However, Iāve run into an issue in reporting. When calling the script, the report displays the following: ExecuteReader: CommandText property has not been initialised. Iāve only seen this error in the forum relating to crash reports. There is no data in the log file relating to this.

When I used just a simple select query in a jscript function and call it, I returns the sum of the required column (of course, without date, user filters).

Here is the script if it helps:

``````SELECT SUM(Commission) FROM v_cigarcommission
WHERE TicketDateLong > '{Start}' AND TicketDateLong < '{End}' AND OrderUser = '{UserName}'``````

Well firstly, I forgot the `@@` in the handler field. Script is working as needed.

I donāt know if it could be of use to anyone else, but hereās what Iāve done:

NOTE: THIS REQUIRES DIRECT MODIFICATION OF THE DATABASE. PROCEED AT YOUR OWN RISK.

For now, Iām using the āTagā field in product setup to store the minimum amount for commissioned sale (one could use this for commission amount as well).

Next, open your favourite IDE (SQL Server Management Studio, DataGrip, etc.)
Iām using SQL Server Management Studio (SSMS)

When your database is selected, go to File->New->Query with current connection (or CTRL+N).

This will open a window with which one may execute commands.

Next is to create the view. A view is a like a virtual table. It doesnāt physically hold any data; itās more of a stored query that can be queried itself.

Copy the following into the new SQL query window:

``````CREATE VIEW v_cigarcommission
AS
SELECT
T.Id                                      AS TicketId,
O.Id                                      AS OrderId,
O.Quantity                                AS Quantity,
M.CustomTags                              AS Tags,
O.Price                                   AS SalePrice,
CAST(M.Tag AS DECIMAL(16,2))              AS MinimumPrice,
CONVERT(DATE, T.Date)                     AS TicketDateShort,
T.Date                                    AS TicketDateLong,
CAST((CASE WHEN (O.Price - CAST(M.Tag AS DECIMAL(16,2))) <= 0
THEN 0 * O.Quantity
ELSE ((O.Price - CAST(M.Tag AS DECIMAL(16,2))) * O.Quantity) END) AS DECIMAL(16,2)) AS Commission
FROM dbo.Tickets T
INNER JOIN dbo.Orders O ON O.TicketId = T.Id
WHERE (T.IsClosed = 1) AND (M.GroupCode = 'Cigars') AND (O.OrderStates NOT LIKE '%Cstatus%');
``````

Select the execute button or press F5. You should see "Command(s) completed successfully.
"

I use two scripts (Manage->Settings->Automation->Scripts)

The first is used to calculate the sum of commission per user:

Name: Cigar Commission Sum
Handler: `@@CommissionSum`

``````SELECT SUM(Commission) AS Commission
FROM v_cigarcommission
WHERE TicketDateLong > '{Start}' AND TicketDateLong < '{End}' AND OrderUser = '@1'
``````

The second is for reporting total commission of all users for the report period:

Name: Cigar Commission Total
Handler: `@@CommissionTotal`

``````SELECT SUM(Commission) AS Commission
FROM v_cigarcommission
WHERE TicketDateLong > '{Start}' AND TicketDateLong < '{End}'
``````

I use two reports:

The first shows only the current user and is available to all users via a report widget on the navigation screen:

``````[Commission Details:4, 1, 2, 2]
@{SETTING:CURRENTUSER}
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True) AND (OU=\$1)AND (MG=Cigars):{0}:,}
>|Qty|Total Sales
>>*** \$1 ***
>>Total Commission:||@@CommissionSum:\$1
``````

The second will list all users, cigar sales, and total commission:

``````[Sales by User:4, 1, 2, 2]
@{REPORT ORDER DETAILS:O.User.asc:(ODI=True):,}
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True) AND (OU=\$1)AND (MG=Cigars):{0}:,}
>|Qty|Total Sales
>> *** \$1 ***