Profit margin total does not tabulate correctly on Detailed Sales

When you have more than one item sold on an invoice the profit margin does not tally correctly on the Detailed Sales Report. Does anyone know how I can fix this??

Ok, RMS is calculating the profit margin correctly on each individual item on the report, taking the cost and dividing by the sale price, but if I sell more than one item, the (bolded) total is incorrect. What RMS is doing is adding up the profit margins for each of the items and dividing by the quantity of items sold, so it is taking an average total of the percentage shown for each item, when it should take the (bolded total cost and divide by the bolded total sales) Example,

I sell one item for say $59.95 with a cost of $24.98 so profit margin is

58.33% and I sell one item for say $29.95 with a cost of $17.97 so profit margin is 40.00% RMS will add 58.33 and 40.00 = 98.33 / 2 I.17 when really RMS should be taking the total cost of $24.98 + $17.97 = $42.95 and dividing that total by the total sales price of $59.95 + $29.95 = $89.90 or ($42.95 / $89.90)-1 = 52.22% which is the real actual total Profit margin.

So what I am looking for is how to accomplish dividing the total cost by the total sales to equal the "total Profit Margin" on the report, not just adding up the profit margins then dividing by the line items. I hope you understand what I am trying to do here.

Thanks!

"Jeff" wrote:

14.11%, which without calculating it, looks pretty close.

You can't do it in Active reports. That column should have it's grouping method set to None.

You could export the data to Excel (CSV) and do the analysis you wnat there...

Glenn Adams Tiber Creek C> When you have more than one item sold on an invoice the profit margin does

No, and it is annoying that we can't do that. We have no control over the calculations that are done when grouping withing the report engine.

When you run a report, do you see the part where it says "Generating Sub-Totals..." That is where the calculation that gives us the Bolded information when grouped happens. We have no control over that.

You are correct, the actual act of calculating that is pretty simple however in the RMS report engine, we do not have control over the place that calculation is performed nor do we have control over how that calculation is displayed. The reporting engine is OK for very simple stuff, but lacks real usefulness in analyzing our business. You may get the Crystal Reports suggestion, but I could never get the DEF files set up correctly to go along with them to run inside RMS. Reporting leaves much to be desired.

This is just the nature of Active Reports. Active reports are really pretty powerful, but ultimately they are just formatting and displaying data that has already been retrieved from the database. You can do limited calculations (SUM, AVG, MIN, MAX) on columns of grouped data, but none of those operations make sense for Profit Margin. You need to SUM two columns then do a computation on the SUMS. Active reports will allow you to generate a computed column value, but that happens during the data retrieval.

There are other reporting tools out there (Like SQL Server Reporting Services, Crystal Reports, Excel or Access) that would allow you to do the calculation that you are looking for. Active Reports just serve a different purpose.

Glenn Adams Tiber Creek C> Oh well....at least we can discuss this....maybe Microsoft will fix in the