Has anyone else noticed that when you do a detailed sales report and add in the profit margin column that the data is incorrect for any "groups" (ie categories, departments, etc.)?
Here's how I discovered this... We are a ski shop and in most cases when a customer purchases a ski package we do not charge for mounting (which is a $0 cost item). However, we do want to track how many mounts we do so we ring up a mount at $0. When you run a detailed sales report the margin for individual mounts will come up correctly but the "summed" margin for this category will be way off because of all the $0 transactions.
So I changed the margin portion of "Custom - Detailed Sales Report with Return Values" report with this:
Begin Column FieldName = "ProfitMargin" DrillDownFieldName = "" DrillDownReportName = "" Title = "Profit Margin" VBDataType = vbDouble Formula = "CASE WHEN TransactionEntry.Price 0 THEN CASE WHEN TransactionEntry.Quantity > 0 THEN (TransactionEntry.Price - TransactionEntry.Cost) / TransactionEntry.Price ELSE CASE WHEN TransactionEntry.Quantity < 0 THEN (TransactionEntry.Price - TransactionEntry.Cost) / (TransactionEntry.Price*-1) ELSE NULL END END ELSE NULL END" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodAverage ColFormat = "0.00%" End Column
The Nulls in place of 0 help with the $0 transactions; however, if there are any negative margins it throws off the "summed" margin for the category.
So my question is: Does anyone know a way to customize the report so that it will calculate "groupmethods" based on the other columns' "groupmethods" data??? It makes sense to do it this way since the individual margins are calculated this way. Currently I am exporting my data to exel to accoplish this which is a pain.