1. Home
  2. Point-Of-Sale Software
  3. Report Profit Margin

Report Profit Margin

Hello,

Does anyone know how to customize the sales report to calculate the profit margin correctly at a group level? Prior to customization, the cost and profit columns were not multiplied by the sold quantity. I have updated those columns, so that it displays total cost and total profit, and the profit margin at a transaction level is correct. At the group level (dept or overall), all columns are totaled correctly, but the profit margin is not recomputed based on those totals. The computation assumes that each transaction is a quantity of 1 and of equal value (it totals each transaction's profit margin, then divides by number of transactions).

Other than exporting to excel, anyone have any ideas on how to get the report to calculate the profit margin correctly?

Thanks, Greg Shepherd FRGUSA

Reply to
gregfrgusa
Loading thread data ...

I was under the assumption that the grouping computation was hard coded and unable to be corrected at a report level customization. If there is a fix, I would love to know as well!!

Reply to
root

Hello:

Yes you can do this but modifying the Sales - Detailed Sales Report.qrp file. Go to \Program Files\Microsoft Retail Management System\Store Operations\Reports And open the file. Then Go on Profit and Profit Margin column

The things you need to do is only Enable the property of unhide column from true to false and then it start working: below is the code. ColHidden = False

Begin Column FieldName = "Profit" DrillDownFieldName = "" DrillDownReportName = "" Title = "Profit" VBDataType = vbCurrency Formula = "TransactionEntry.Price - TransactionEntry.Cost" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodSum ColFormat = "" End Column

Begin Column FieldName = "ProfitMargin" DrillDownFieldName = "" DrillDownReportName = "" Title = "Profit Margin" VBDataType = vbDouble Formula = "CASE WHEN TransactionEntry.Price 0 THEN ((TransactionEntry.Price - TransactionEntry.Cost) / TransactionEntry.Price) ELSE 0 END" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodAverage ColFormat = "0.00%" End Column

If works please rate it.

"gregfrgusa" wrote:

Reply to
Akber Alwani

Hi Akber,

It is not that I can't see the profit margin, it is that the profit margin is not calculated properly at the group level.

Greg

"Akber Alwani" wrote:

Reply to
gregfrgusa

Sorry , may be I not clearly understood, I have look at the .grp file and agree with you. what I have found that RMS people has not calacuate any of the transaction total correctly i.e

  1. Cost Column should be: FieldName = "(TransactionEntry.Cost * TransactionEntry.Quantity)"

  1. Profit Column Formula should be: Formula = "(TransactionEntry.Price * TransactionEntry.Quantity) - (TransactionEntry.Cost*TransactionEntry.Quantity)"

  2. ProfitMargin Column Formula should be: Formula = "CASE WHEN TransactionEntry.Price 0 THEN (((TransactionEntry.Price*TransactionEntry.Quantity) - (TransactionEntry.Cost*TransactionEntry.Quantity)) / (TransactionEntry.Price * TransactionEntry.Quantity)) ELSE 0 END"

The above will not solve the whole issue as still Group level roll up is not working, But the cost which was not coming correctly now it will come, also profit and trasnaction line level profit margin are coming correctly. (see formula above)

However I have done one more thing for testing. I created one database level formula field ProfitMargin2 and given same formula as specified above. Then added the field in .grp file with GroupMethod =groupmethodAverage but problem not solved. I agree with you that it is bug/issue with reporting engine of Active report.

If you want I can develop for you same report in Crystal let me know.

"gregfrgusa" wrote:

Reply to
Akber Alwani

Hi Akber,

Thanks...I had the first 2 formulas, but not the last one...let me know if MS fixes the bug...who do we report the bug to?

Greg

"Akber Alwani" wrote:

TransactionEntry.Price)

Reply to
gregfrgusa

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.