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

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
Asked:
bapkins
  • 5
  • 4
2 Solutions
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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