Evaluate a sum as <= 0 in report

V5 Question
1

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 ***
{REPORT ORDER DETAILS:O.MenuItemName,MT.Minimum Price,O.Quantity.Sum,(MATH LOGIC GOES HERE).Sum.desc:(ODI=True) AND (OU=$1) AND O.MenuItemName="$2"}

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
2

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.

3

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.

4

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.

5

Are you using any Custom Tags?

6

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.

7

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.

8

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}'
9

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).
1_tag

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

Navigate to your database:
2_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,
    O.MenuItemName                            AS Name,
    M.CustomTags                              AS Tags,
    O.Price                                   AS SalePrice,
    MIP.Price                                 AS MenuPrice,
    CAST(M.Tag AS DECIMAL(16,2))              AS MinimumPrice,
    O.CreatingUserName                        AS OrderUser,
    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
    INNER JOIN dbo.MenuItems M ON O.MenuItemId = M.Id
    INNER JOIN dbo.MenuItemPortions MIPOR ON MIPOR.Name = O.PortionName AND MIPOR.MenuItemId = O.MenuItemId
    INNER JOIN dbo.MenuItemPrices MIP ON MIPOR.Id = MIP.MenuItemPortionId
  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 ***
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum,O.TotalPrice.Sum.desc:(ODI=True) AND (OU=$1) AND O.MenuItemName="$2"}
>>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 ***
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc.Sum.desc:(ODI=True) AND (OU=$1) AND O.MenuItemName="$2"}
{REPORT ORDER DETAILS:'     '+[O.PortionName],O.Quantity.Sum.desc,(([O.Price] * [O.Quantity]) - ([MT.Minimum Price] * [O.Quantity])).sum.desc:(ODI=True) AND (OU=$1) AND O.MenuItemName="$2":2}
>Total Commission for $1:||@@CommissionSum:$1
>>Total Cigar Commissions||@@CommissionTotal

The plan is in the future to use product tags to store said information, which is why the view contains custom tags.

1 Like