Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

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

Why can't you just create a true formula that includes the relevant fields ?

1024 * 2 + 2400 * 1.5 - 150

With the variables or fields

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

mlmcc

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.

Are they putting in database field names?

mlmcc

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.

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

"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

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.

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

mlmcc

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?

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

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.

All Courses

From novice to tech pro — start learning today.

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