Reporting: weighted amount or query modification?

[tvandesande]

Hi,

i need Your help again Guys!

basically what i need to have in the report is a weighted amount of an opportunity (so amount * probability, so an opportunity with amount of 100, and 5% probability would have a weighted value of 5).
my reporting has to be based on a weighted value.
so i was wondering if either:
-its possible to create a custom report with custom calculations
-its possible to modify a query that report is driven by - i added a calculated field in the opportunities table that is automatically calculating weighted amount - so changing a reference to a column would be just enough here

i appreciate all help
thanks
peter

ps. using zucker reports for us at the moment is out of question. this will be implemented next year, and reports are needed asap

[eggsurplus]

The quickest way to do something like this is to write a quick SQL statement that'll do the weight calculation you need. I don't know of another way in CE to accomplish this without a third party reporting tool or code customization.

[andopes]

You would create a new custom field float into Opportunities module to store the weighted amount.
Then you may create a logic_hook before_save to save into this field the probability * amount.
You may create a simple php script to update the existing opportunities adding the correct value for this field.
This way you can view this new field into DetailView/EditView/ListView.

Regards

[eggsurplus]

I highly suggest this method if you want to get your elbows a bit dirty. We do exactly this for Leads so that we can sort on the weight and target those that are the best fit for us.

[tvandesande]

The quickest way to do something like this is to write a quick SQL statement that'll do the weight calculation you need

where would be the place to edit it? i understand You're talking about modifying the sql statement in a report?

Then you may create a logic_hook before_save to save into this field the probability * amount.

is there any tutorial about using/creating logic hooks? (preferably before_save)?

greetings
peter

[andopes]

Hi Peter

Take a look at this thread: http://www.sugarcrm.com/forums/showt...culated+fields