# Crystal Reports formula field

Hi,
I have a starange problem with crystal reports regarding a formula field.

I am simply trying to calculate the bonus amount paid to a salesperson based upon 2 calculated fields that i have on the reports which are

@profit
@commission - (A percentage figure)

i have tried several formulas including:

Sum ({@commission} * {@Profit})

{@commission} * {@Profit}

Every time i try whichever way i do it crystal reports that

"a number, or currency amount is required here"

this is on the @commission field.

I think it's treating the @commission field as text instead of numeric and i can find no way of changing the type.

I've done much more complicated than this and cant understand whats wrong !
Help !
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
try the attached code for starters, the tonumber function takes number stored as text and converts them into an actual number, usable in formulae.

if this fails, i'll need you to return your exact formula.
``````tonumber({@commission})*tonumber({@profit})
``````
0
Author Commented:
That sort of worked but i then cannot summarize by the field for sum.

I'm pretty sure the formula field @commission is being created as text and that is the problem.

Why would it create it as text in the first place ?

This is the formula that creates the @commission value:

if {PASSSalesBonus.STKCODE} in ["calcheck", "cb 200"] then '15' else '0'

0
Commented:
try this (code)
``````local numbervar num;

if {PASSSalesBonus.STKCODE} in ["calcheck", "cb 200"] then num:=15 else num:=0;

num;
``````
0
Commented:
also, if you can't summarize make sure no formulae involved have Whileprintingrecords; or similar, as this will delay the calculation of this until after the report is layed out (and therefore too late for any summary).
0
Author Commented:
No It's still appears to be treating it as text.

I have attached the report.

Hopefully that will be easier !
EQ-Salesperson-Monthly-Bonus-Rep.rpt
0
Commented:
Currently not at a workstation where I can modify it.

Can you provide the plain text of all fomulae involved? :)
0
Author Commented:
(@profit)
{PASSSalesBonus.AMOUNT}-{PASSSalesBonus.COST}

(@bonusamount)
(tonumber(Sum ({@Profit}, {PASSSalesBonus.STKCODE}))/100) * tonumber({@commission})

(@commission)
local numbervar num;

if {PASSSalesBonus.STKCODE} in ["calcheck", "cb 200", "passcal", "passdvd", "passpta20", "PASSSUPP", "warr5", "WARR5", "wm1 + wm7", "wm1 250", "wm1 500", "WM1000", "wm11", "wm12 250", "wm12 500", "wm13 250", "wm13 500", "wm15", "wm2000", "wm3000", "wm45", "wm500", "wm7 250", "wm7 500", "wmcust", "wmmic"] then num:=15 else num:=0;

num;

0
Commented:
The reason you cannot summarize is because you have already summary in {@Bonusammount}

"Sum ({@Profit}, {PASSSalesBonus.STKCODE})"

When you create a group summary, it can be referenced in much the same way, because this is how it does it.

You need to get to your figure without using a sum(x,x)
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
BonusAmount should be:
@commission * @Profit

Put BonusAmount in the detail section.
For the group total, insert a summary: SUM of BonusAmount
0
Author Commented:
Thanks for all of your help.

That worked. I understand now.

0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.