Solved

Evaluating formula in Crystal Reports

Posted on 2013-06-29
13
654 Views
Last Modified: 2013-07-02
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
0
Comment
Question by:PeterCoole
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39286823
Can you explain why the 'formula' is a string.
Why can't you just create a true formula that includes the relevant fields ?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39286928
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 Comment

by:PeterCoole
ID: 39287634
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
 
LVL 77

Expert Comment

by:peter57r
ID: 39287951
"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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39288108
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 Comment

by:PeterCoole
ID: 39288139
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Expert Comment

by:mlmcc
ID: 39288548
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 Comment

by:PeterCoole
ID: 39288951
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 125 total points
ID: 39289229
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39289890
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
 
LVL 34

Accepted Solution

by:
James0628 earned 125 total points
ID: 39292464
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
 

Author Closing Comment

by:PeterCoole
ID: 39293176
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
 
LVL 34

Expert Comment

by:James0628
ID: 39294001
> ... 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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now