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
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
If you want the numeric version
1024 * 2 + 2400 * 1.5 - 150
With the variables or fields
{HeightField} * 2 + {WidthField} * 1.5 - 150
mlmcc
1024 * 2 + 2400 * 1.5 - 150
With the variables or fields
{HeightField} * 2 + {WidthField} * 1.5 - 150
mlmcc
ASKER
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
"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.
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.
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
Are they putting in database field names?
mlmcc
ASKER
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.
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.
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
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
ASKER
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
"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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Do you have a fixed number of formulas or are the users entering formulas they that fit their analysis?
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
> ... 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
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
Why can't you just create a true formula that includes the relevant fields ?