Problem with Product Report

[JammyJay]

Hi,

We have recently purchased ACT! 2010 but we're having some trouble creating custom reports.  We want a basic report which will show us our total profit per product, so the report I've edited (was originally Gross Margin by Product) is laid out like this:

Product

Total Sales    Total Cost    Total Gross Margin

I haven't actually changed anything from the original report I edited, I've only deleted the fields/titles I didn't want.  The problem I'm having is that ACT! keeps adding product quantities to the Total Cost.  For example, one product we sell costs us £0.20, but when I run the report ACT! lists the cost as £1.20.  I tried changing the quantity for that Opportunity to 2, and then the cost became £2.20 in the report.  I even encountered this issue when running the Gross Margin by Product, which is built in to ACT!

Any help regarding this matter would be much appreciated.

[Roy_Laudenslager]

In my experience with the reports, I seldom try to work from one of the standard reports, rather ai typically start from scratch unless the changes are very minor which your's aren't.  I don't work from the standard reports for major changes because to do so requires that I basically have to reverse engineer the report template to find out what it is doing before it is possible to successfully modify the report, Because most of the report template also include VD program script programming, you also need to track down that programming an understand how it integrates with the report template. 

As to the report you want to create, I think it's possible but I wouold need more information about exactly what you want. 

[JammyJay]

Well, I managed to create the same report by slightly editing the 'Gross Margin by Record Manager' report, which actually works out the cost correctly.  I'm not sure what makes it so different from any of the other 'Gross Margin by...' reports, but it does what we want it to anyway.  Surely the default reports should work correctly though, no?  Even running the other 'Gross Margin by...' reports without changing them at all, it still adds the quantity on to the cost, and doesn't multiply the cost according to quantity.  Surely I shouldn't need to use script to fix the built-in reports so they do what they already should anyway?

[doesitmatter]

This error has puzzled me a bit as well.

It seems like in a Product Report (e.g. Gross Margin by Product), the quantity is added to the cost.

Opportunity Report (e.g. Gross Margin by Record Manager) does not use the same fields, so this doesn't happen.

I edited the Gross Margin by Product's script so it'll show the correct margin. I just added the red part; I'm adding back the $1/product that  ACT! is taking away from my gross margin!

'Calculate Gross Margin (un-formatted)
Custom1 = Total1-TotalCost1+TotalQuantity1

'Calculate Gross Margin as a %
If Custom1 = 0 Then
  Custom2.Value = "0%"
Else
  Custom2.Value = FormatPercent(Custom1/Total1)
EndIf

'Format Gross Margin
Custom1 = FormatCurrency(Total1-TotalCost1+TotalQuantity1, 2, -2, -2, -2)

But we really shouldn't have to do this. ACT! should be showing the fields correctly. From seeing the first line in the script, "'Calculate Gross Margin (un-formatted)", it seems like ACT! knows the error?