• Status: Solved
• Priority: Medium
• Security: Public
• Views: 591

# 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 !
0
bapkins
• 5
• 4
2 Solutions

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

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 5
• 4
Tackle projects and never again get stuck behind a technical roadblock.