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 !
bapkinsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

oliffCommented:
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})

Open in new window

0
bapkinsAuthor 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
oliffCommented:
try this (code)
local numbervar num;

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

num;

Open in new window

0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

oliffCommented:
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
bapkinsAuthor 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
oliffCommented:
Currently not at a workstation where I can modify it.

Can you provide the plain text of all fomulae involved? :)
0
bapkinsAuthor 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
oliffCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
UnifiedISCommented:
BonusAmount should be:
@commission * @Profit

Put BonusAmount in the detail section.
For the group total, insert a summary: SUM of BonusAmount
0
bapkinsAuthor 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.