Locating Profit Margin in SQL

WebStore
1

Hi,
I am trying to find the “Profit Margin” table in SQL I can’t seem to find the column in the “Item” table or any of the others. Thoughts? The field is under Item Properties >General>Profit Margin in RMH. Thanks

2

Hi John,

I think it might not be a single column but rather a calculation done on the fly based on the existing data.

If I understand the functionality correctly you might try running something like:
select (item.price-item.cost)/item.price*100 as ‘profit margin’ from item where item.price > 0
I added the where clause because the query will fail if it encounters a ‘0.00’ value in price or cost.
It might be more complicated than this but I don’t know how exactly RMH calculates this.

Regards,

Peter

3

Hi,

You just need to be slightly careful as the price is stored in the database with tax included, the cost is stored without tax. So to calculate profit margin correctly you need to remove the tax from the price in Peters calculation.

Emma

4

The value is definitely calculated on the fly for RMH, and looking through the default ‘Active Reports’ supplied with the software (there is a directory of “Profit Margins” reports under “Custom Reports”) for inspiration I am assuming the formulas used, taking account of VAT, are:

Profit Value:

Formula = “SUM(((TransactionEntry.Price * TransactionEntry.Quantity) - (TransactionEntry.Cost * TransactionEntry.Quantity)) - TransactionEntry.SalesTax)”
ColFormat = “$#,###.##”

Profit Margin:

Formula = “CASE WHEN SUM(TransactionEntry.Price * TransactionEntry.Quantity - TransactionEntry.SalesTax) <> 0 THEN SUM((TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity - TransactionEntry.SalesTax)/SUM(TransactionEntry.Price * TransactionEntry.Quantity - TransactionEntry.SalesTax) ELSE 0 END”
ColFormat = “##.##%”

1 Like