Evaluating formula in Crystal Reports

I have a formula in Crystal that I would like to convert to a numeric value.

The formula is of the form "1024*2 + 2400*1.5 - 150"

It is a string that originally was "H*2 + W*1.5 - 150", I was able to convert to the first string but now need to convert to a numeric value for further calculations.

Cheers
Who is Participating?

x

Commented:
In addition to the basic problem of parsing the string to extract the values and operators, it seems that you're observing operator precedence (multiplication and division before addition and subtraction), which adds a whole new level of complexity.  I'm not going to say that it would be impossible to do that in CR, but "improbable" might be a good description.  I think you'd have to do something like implement something like a stack (an array), where you could store the values until you had all of the operators, so you knew which operations to perform first.

If there is known set of formats that the formulas will always follow ("H*2 + W*3 - 150", "H*W/2 +220", etc.), then that might simplify things.  If the report can determine the "format" for a formula, then it knows how to use the values, without having to parse the string, looking for operators and trying to figure out which ones to use first.  But if the formulas could take any form, then I doubt that trying to evaluate them in CR would be practical.

James
0

Commented:
Can you explain why the 'formula' is a string.
Why can't you just create a true formula that includes the relevant fields ?
0

Commented:
If you want the numeric version

1024 * 2  +  2400 * 1.5  -  150

With the variables or fields

{HeightField} * 2  +  {WidthField} * 1.5  -  150

mlmcc
0

Author Commented:
The formula are entered by the user and the h and w values are variables. There are actually 40 or more variable. The sample I gave was just one of hundreds possible calculations
0

Commented:
"The formula are entered by the user ..."

How do they do that?
How do you ensure that they enter a valid formula?

This sounds more like something that should be done in Excel.
0

Commented:
Crystal is not set up to handle that easily.  You will have to write  parser to read through the string and determine what values to substitute into the formula.  It can be done but the code is not going to be easy to write.

Are they putting in database field names?

mlmcc
0

Author Commented:
Our application is storing the values in the database (as strings), and they are pre parsed at that time. At the time of using the formula's then the replacement values are substituted and we would like to calculate the values.

Replacing the H, W, A .. Z, H1 ... H9, W1...W9, A1 ... Z9 is the easy bit, converting the "Numeric string" to the number is tough bit.

Might have to get the formula translated and the value passed through to the report at the time of reporting. I was hoping to do it all in Crystal.
0

Commented:
So what is it Crystal has to do?

Will you be passing the 4*2 + 4*5  .... form or the one with variable in it?

Do you have standard formulas that are used all the time?

mlmcc
0

Author Commented:
The database fields have formula such as:
"H*2 + W*3 - 150"
"H*W/2 +220"

The H & W can actually be up to about 40 different variables A ... Z, H1 ... H9, W1 ... W9

Before the user runs the report they will then assign values to the vaiables, H = 2200, W= 1250, H1 = 800 etc, but just for those variables that are defined in the formaul set they want to use at the time.

In Crystal I have been able to substitute the values into the formula so i end up with
"2200*2 + 1250*3 - 150"  (or what ever it is)

What I am having dificulty with is how to now convert this string numeric calculation into an actual numeric result. In this case it shoudl result in 8000.

We had the application in an old dBase (Clipper) program that was able to do macro substitution and so was able to get the result easily. But this is not so easy within Crystal.

Thanks
0

Commented:
I think you are going to struggle to find a way to do this.
Newer (like .NET) languages don't have such a feature as the one you found in Clipper.

Access VBA has an Eval() function, which also does what you require,  but again this goes back to a period nearly 30 years ago. Excel has  the same thing as Evaluate().

You can see here the complexities of doing it in a .net language..
http://social.msdn.microsoft.com/Forums/vstudio/en-US/7f62b87d-a35c-4074-a0f0-84a9dd7ff0a5/convert-string-to-formula

I don't know whether you would be able to find (or create, I guess) an add-in to do this sort of thing.  It's outside my scope, I'm afraid and I don't know how you would start.
0

Commented:
What are trying to allow the users to do?

Do you have a fixed number of formulas or are the users entering formulas they that fit their analysis?

mlmcc
0

Author Commented:
Thanks Guys, you have at least confirmed my suspicions. This is not going to be easy, I will try to get the programmer to modify the program that calls my report so they can do the calculations and save them into a temp table that i can link. What shame :-(

One though I had was, could I use an SQL statement to generate the required values, so during the Query process send in the formula as one of the fields and get it to generate the calculation as part of the resulting data set.

Any thoughts?
0

Commented:
> ... could I use an SQL statement to generate the required values ...

What are you trying to "generate" in the SQL?

If you're just trying to use SQL code to evaluate the formulas in the strings, then you've got the same problem, and unless you've got some kind of function that will do it (like the ones that Peter mentioned earlier), my first instinct is that it would be easier in CR.

OTOH, if the components that go into the formula are available (something like a list of the operands and operators), then you might be able to do the calculation based on those (rather than trying to interpret the string).

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